When working with MDX queries in analytical structures, a warning can be generated regarding the limit of data crossings, especially when using functions such as CrossJoin or NonEmptyCrossJoin. This warning is generated when the number of combinations generated by crossing dimensions is too high, exceeding the limits set to avoid processing overload.
Why does this happen?
MDX query processing treats each dimension as independent, crossing all its possible members. When multiple dimensions with many different members are combined, the volume of data generated can be excessive, making the query unfeasible.
For example, when crossing two dimensions with thousands of members each, the result generated can be a set with millions of combinations before even considering any additional filters or restrictions. This can exceed the safety limits set to avoid performance impacts on the analysis.
How can this problem be avoided?
To optimize the execution of queries and avoid exceeding the limit of crossings, the following approach can be applied:
Create hierarchical dimensions: Creating a hierarchical dimension makes it possible to organize data in a structured way, defining direct relationships between elements. In this way, query processing takes place on the basis of the existing hierarchy, reducing the need for massive data crossings. Learn more
This approach consists of combining the relevant intersections in advance within a hierarchical structure, ensuring that only valid relationships are considered in the analysis, avoiding the processing of unnecessary combinations.
How to apply it to MDX queries?
With this solution, you can reformulate the MDX query using the new hierarchical dimension:
| SELECT NON EMPTY {[Measures].[QTPROCEDIMENTO]} ON COLUMNS, NON EMPTY [DimensaoHierarquica].[NivelMaisEspecifico].Members ON ROWS FROM [BaseAnalitica] WHERE NonEmptyCrossJoin( {([DimensaoFiltro].[Filtro])}, ([DimensaoTempo].[Data].[Inicio] : [DimensaoTempo].[Data].[Fim]) ) |
In this approach:
- The rows (ON ROWS) use the hierarchical dimension at the most specific level relevant to the analysis, ensuring that only direct relationships are considered.
- The “Show hierarchy” option can be activated in the analysis to display the members of the previous levels within the hierarchical structure.
This solution makes it possible to avoid running unnecessary cross-references, significantly reducing the impact on performance and ensuring that the analysis is processed efficiently.