Troubleshooting MDX Related Issues Using the SQL Profiler
The SQL Profiler is an excellent tool to troubleshoot query related issues.
Using the profiler, you can analyze the queries run by BI Office, find query duration times in SSAS and compare it with the actual duration in BI Office, view errors that were thrown by SSAS and more.
To run a Profiler trace, please do the following:
1. Start the SQL Profiler by going to the Start menu > All Programs > Microsoft SQL Server 20XX > Performance Tools > SQL Server 20XX Profiler.
The SQL Profiler can also be accessed from the Tools menu in SQL Management Studio.
2. Click on File > New Trace > Log into the SSAS instance against which you would to run the Profiler.
3. In the Trace Properties window, under the Events Selection tab, check all Events.
4. To filter the trace to only show queries related to BI Office, go to Column Filters and click on ApplicationName > expand Like > type "Pyramid".
5. You can also filter the trace to only show queries run by a specific user. To do this, click on NTUserName > expand Like > type the username that is logged into BI Office.
6. Click on OK and Run.
7. Now you can run queries in BI Office and view the query details in the trace. if needed, the trace can be saved and sent to the support team for analysis.