Understanding MDX Query Execution process

In this post we are going to see basic MDX Query Execution process in SSAS. Typically, the performance issues SSAS users encounter occur in one of two realms: cube processing and query execution, this post will focus on the latter as we start by establishing a higher level of understanding of what happens when an MDX query is issued against our cube. Let us consider below basic mdx query which returns reseller sales for all members of calendar year hierarchy for United States. Query returns simple cell set as seen below

SELECT { [Measures].[Reseller Sales Amount] } ON 0
	,{ [Date].[Calendar Year].[Calendar Year].members } ON 1
FROM [Adventure Works]
WHERE ( [Geography].[Country][United States] )

The Analysis Services querying architecture provides several components that work together to efficiently retrieve and evaluate data. The below figure identifies the two major operations that occur during querying: Preparing MDX query execution plan and data retrieval so let us understand what happens when this query hit SSAS Server in detail.

Query Parser

When MDX query is issued on SSAS Server the Query Parser accepts MDX requests, it then parses the request, validates the syntax and passes it along to the Formula Engine for query execution.

Formula Engine/Query Processor

Upon receiving the validated and parsed query from the Query Parser, the Query Processor prepares an execution plan which dictates how the requested results will be provided from the cube data and the calculations used. This summarizes the Formula Engine operations:
1) Populate axes by getting members from attribute store specified in SELECT and WHERE clause and create set of tuples which are then sent to Calculation Engine. In our example tuples are formed by selecting members of [Calendar Year] hierarchy (data from attribute store) and measure [Reseller Sales Amount] and [Geography].[Country][United States]. Tuple Set formed is
 ( [Date].[Calendar Year].[2005], [Reseller Sales Amount], [Geography].[Country][United States] )
,[Date].[Calendar Year].[2006], [Reseller Sales Amount], [Geography].[Country][United States] )
,[Date].[Calendar Year].[2007], [Reseller Sales Amount], [Geography].[Country][United States] )
,[Date].[Calendar Year].[2008], [Reseller Sales Amount], [Geography].[Country][United States] )
2) In next step Calculation Engine is responsible for translation of tuple set request into subcube data requests and makes request for subcube data from storage engine, So cell values for these tuples is requested from Storage engine.
3) Storage engine takes subcube requests and responds with subcube data (see storage engine section below).
4) After data is received form Storage Engine, Formula Engine produces result set by doing cell-by-cell (naive) computation or block mode (subspace) computation on subcube data depending upon MDX request, Which one is chosen by the engine can have a significant impact on performance and in the end cell set is formed containing measure values as shown above and returned to client.
5) Finally it stores calculation results in formula engine cache with varying scope such as Query scope (cache will not be shared across queries in a session, for calculations defined in WITH clause) or Session scope (cache will be shared across queries in a session, for calculations defined using CREATE MEMBER) or Global scope (cache can be shared across sessions, for calculations defined in cube).

Data retrieval from Storage Engine

The Storage Engine responds to the sub cube data (a subset or logical unit of data for querying, caching and data retrieval) request generated by the Formula Engine/Query Processor.
1) Get from Storage Engine Cache: It first checks if the requested sub cube data is already available in the Storage Engine cache, if yes then it serves it from there.
2) Get from Aggregations: If not then it checks if the aggregation is already available for the requested query, if yes then it takes the aggregations from the aggregation store and caches it to the Storage Engine cache and also sends it to Query Processor for serving the request. Also if aggregated data at lower level is available(not available at higher level requested) then storage engine summarizes lower level aggregated values on the fly as needed and sends it to Query Processor. For e.g data is requested at year level and year level aggregation is not available but month level aggregation is available then Storage Engine summarizes it and send it to Query Processor.
3) Get from Partitions: If data is not in aggregations either it then scans MOLAP partitions in parallel (uses bitmap indexes if available to locate partition segments for requested data), calculates the required aggregations and caches it to the Storage Engine and then sends it to Query Processor for serving the request.

While troubleshooting you need to understand which component is taking more time and needs to be optimized; such as the Formula Engine or Storage Engine. To understand this you can use SQL Server profiler and capture certain events which will tell you the time taken by these components for a cold cache:

Storage Engine Time = Total elapsed time for all Query Subcube Events
Formula Engine = Total Execution Time (Query End event) – Storage Engine Time 

If most of the time is spent in the Storage Engine with long running Query Subcube events, the problem is more likely with the Storage Engine. In this case you need to optimize the dimension design, design of the aggregations and create partitions to improve query performance. If the Storage Engine is not taking much time then it is the Formula Engine which is making things slow, in that case you need to focus on optimizing the MDX queries.