The warning “The query exceeded the limit of supported crossings” indicates that the query generated an excessive number of data combinations, exceeding the limit of 500,000 crossings. This limit has been set to avoid processing overload and ensure that analysis remains fast and efficient. This alert arises mainly when multiple data dimensions are combined, resulting in a very large volume of possibilities. To avoid performance impacts, the platform restricts queries that exceed this limit, ensuring a balanced use of resources and a fluid experience for all users.
Why does this happen?
MDX query processing treats each dimension as independent, generating combinations between all its possible members. When multiple dimensions with many different members are combined, the volume of data generated can become excessive, making the query unfeasible.
For example, when obtaining a list of customers and the products they have purchased, BIMachine cross-references all the customers with all the products, forming a matrix with all the possible purchase combinations. If the register contains 1,000 different customers and 250 different products, the resulting matrix will have 250,000 combinations before even considering filters or restrictions. This volume can quickly exceed safety limits, impacting the performance of the analysis.
How can this problem be avoided?
To optimize the execution of queries and avoid exceeding the crossing limit, 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.