Meeting the Query Challenge
Given the vast number of query formations possible within MDX, together with an almost endless number of cube designs and calculations, there arises a significant challenge in designing the fastest possible automated query. To assist the user in achieving the fastest possible query results, BI has developed a set of optimization switches that can be used to tweak the manner in which a query is executed.
To turn ON all query optimization switches, click the top half of the Query Options icon in the Query Ribbon.
To control individual optimization switches, click the bottom half of the Query Options icon in the Query Ribbon.
Default Optimization Switch Settings
The default values of the various optimization switches are shown below.
Can remain ON at all times.
Can remain ON at all times.
|Sub Query Mode||OFF||Default value may be changed in User Options.|
|Filtering Data||OFF||See Optimize checkbox in Data Filter dialog.|
|N-of-N Iterations||OFF||See Optimize checkbox in N-of-N dialog.|
Quick Fix Strategy
When a slow query is encountered, it is possible to click the top half of the “Query Options’” button in order to enable all optimization switches. In many cases, the query speed will improve immediately. If performance does not improve, we recommend that you turn OFF the various optimization switches one-by-one until the optimal solution is found for the query.
Multiple Hierarchies from Different Dimensions
When using two or more hierarchies from DIFFERENT dimensions on a single query axis (either columns or rows), it is recommended to use the optimization switches that are based on the NONEMPTY function.
Multiple Hierarchies from Same Dimension
When using two or more hierarchies from the SAME dimension on a specific axis (column or row), it is recommended to perform optimization using the Context Heuristics optimization switch.
Multiple Elements from a Single Hierarchy
When using multiple elements from a single hierarchy, it is recommended to perform optimization using the Sub Query Mode.
Additional Optimization Tips
Turn OFF Query Pre-Check
The Query Pre-Check feature in the Query Ribbon is designed to protect users from accidentally running large queries and thus depleting server resources. The pre-check measures the size of the possible rows and columns in the result set and blocks overly large queries from executing. In some instances the Query Pre-Check can add significantly to calculation time and should be turned OFF to improve query response time.
Choose Flat Grid Type
BI Office allows users to choose from a number of different grid types. For large queries, the Flat Grid will deliver the shortest rendering time (while the Multi-Tier Grid will usually produce the longest rendering time).
Build Better Aggregation for OLAP Cubes
For OLAP cubes only, you can build better aggregations by means of Usage Based Optimization in SSAS. By sampling one or more queries against your cube in the Analysis Services, you will be able to use the sampled data to improve aggregation design for the cube. Applying these aggregations can dramatically improve the performance of your cube, since long running (but high demand) queries are optimized inside the cube’s structure.