How to trace MDX queries running on Analysis Services


So you’ve got a BI solution up and running and want to find out what MDX queries are actually running on your server?

Well this guide will help you quickly find out.

 

Step 1: – Launch SQL Profiler – You can find it on the Tools menu of SQL Server Management Studio (SSMS):

How to trace MDX queries running on Analysis Services - 1

 

Step 2: – Login to Analysis Services:

How to trace MDX queries running on Analysis Services - 2

 

Step 3: – Start a new Trace:

How to trace MDX queries running on Analysis Services - 3

 

 

Step 4: – Remove a lot of the noise by removing all the events you don’t care about:

Just leave the ‘Query End’ event ticked.

If your server isn’t very busy then you can probably just click ‘Run’ to start profiling, otherwise continue onto the next step.

How to trace MDX queries running on Analysis Services - 4

 

 

Step 5: – Filter the traces even more

Before clicking ‘Run’ you can do some more filters by clicking on the ‘Column Filters’ button.

How to trace MDX queries running on Analysis Services - 5

 

 

Step 6: – Show queries for only a specific user

Here I’m adding a filter to only show results where the NTUserName = grudman ( grudman is my username)

How to trace MDX queries running on Analysis Services - 6

 

 

Step 7 – Click run, and watch the trace logs come in 🙂

How to trace MDX queries running on Analysis Services - 7

 

 

Step 8 – Optionally create a template for all this

Please see my next article for the steps you’ll need to perform

 

As always all feedback is welcome 🙂

Advertisements

How to insert specific values into a SQL table with an Identity column


As you probably already know; the values in an Identity column are auto-generated by the database, and as such you can’t just insert a specific value into those columns.

Normally if you try to insert a specific values into an Identity column you will get an error similar to this:

Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

Well sometimes you really do want to use specific values for the Id. For example you may want to pre-populate a database or you need to fix some data, or a hundred other reasons. Well this is how you do it:

SET IDENTITY_INSERT [dbo].[Customer] ON -- This turns auto-generated values off

INSERT INTO [dbo].[Customer] (Id, [Name]) VALUES (123, 'Gordon') --Insert your rows

SET IDENTITY_INSERT [dbo].[Customer] OFF -- This turns auto-generated values back on

The Results:
The results

Well I hope this helps someone out there! 🙂

All questions and feedback are welcome.

http://msdn.microsoft.com/en-ie/library/ms186775.aspx

MSDN Documentation