How does MDX Query run

 

When user fires a MDX query it goes through sequence of steps

1.      Formula Engine: Formula engine receives the MDX first hand and then parses it. Once MDX is correctly parsed it looks for data in its Cache, if find all the data it needs, it will skip Storage engine(step 2) and goes to Step 3

2.      Storage Engine: Formula engine sends request to Storage engine for data. Storage engine looks into its Cache and if doesn’t fine requested data if fetches data from physical storage and returns back to Formula engine

3.      Formual Engine: Once it get all the Data it needs, performs calculations specified in MDX and return result set to MDX editor.

Form above Steps we can see the there could be two bottleneck for a MDX query

a)    Data Fetch from Storage engine

b)    Performing Calculation

So How do we know where it is stuck.

User profile and run MDX in background. Now check for Query Subcube and Query Subcube Verbose Event in profiler, when u sum the time from these events corresponding to the Query you will get the Time taken by Storage Engine to return the Data. Now if you subtract this time from Total time taken by Query you will get time taken in performing Calculation. 

There we are we know the time taken at each step and choose the direction to look for.

Cheers!!