View MDX query generated while browsing a cube in SSMS

Question

In Sql Server Management Studio once I browse a cube I can drop column fields, row fields and filter fields. This displays the required data.

I want to know if there is a way to view the MDX query being generated behind the scenes to display the data?

Thanks.

Answer 1

SQL Server Profiler works on SSAS servers. Select Analysis Services for the Server type in the Connection dialog when initiating a Profiler connection. Select your Analysis Services server and connect. You can use the Standard Profiler template and start the trace. You should be able to see the MDX query that way.

Answer 2

Open SSMS, and Connect to 'Analysis Server engine'.

Select the 'CUBE' and do right click and place the metrics and dimension as per your requirement.

and Execute the query.

In the top menu, you could fine 'Design Mode', you can see the MDX query for the corresponding query result.

Answer 3

There's an Excel plugin on Codeplex that allows you to view the MDX created while browsing a SSAS cube.

http://olappivottableextend.codeplex.com/

Answer 4

There isn't a great way to do this via excel or SSMS, but if you use BIDS, there is a simple way of achieving it, and I use it a lot myself....

Create a new report project. Create a new Datasource, which connects to the cube. Start the process to create a new report, and on the 'Design the query' screen, you can click on 'query builder'. This will pull up an GUI where measures and dimensions can be clicked and dragged, etc. Then click on the 'Design mode' icon, and it will toggle between the GUI view and showing the MDX.