The best monitoring tool for MDX queries is SQL Server Profiler, which you could launch by navigating to Start | All Programs | SQL Server 2012 | Performance Tools | SQL Server Profiler. Profiler records various phases of the query execution, including the retrieval of data from partitions or dimensions, querying aggregations or memory cache as well as displaying the results to the application. Based on the Profiler output, we can surmise whether SSAS spends most of its execution time retrieving data from the storage engine or in the formula engine, meaning deriving calculated values after the data retrieval is complete.
Follow these steps for monitoring and tuning MDX queries.
Once the Profiler is open, go to File | New Trace and connect to your Analysis Services instance. Profiler allows choosing numerous events and columns that you could include in your trace. Ensure that the Show all Events checkbox is checked, then choose the following...