After a couple of days off work last week with SQL Bits III and SQL 2008 Unleashed, it’s back to the grindstone this week; however before I get onto the serious stuff I’d like to say thank you to the organisers of both events. Watching you on stage headbanging to Rockstar – legendary! (You can see for yourself here and here…).
Anyway, back to MDX…
This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.
Lets say for example that you want to have a sales report grouped dynamically by either product, sales person, department or customer. Normally you would use a single static MDX query, and then add a dynamic grouping to the table in the report. This is fine, until you try it on a large dataset. If you only have 50 products, 2 salesmen, 5 departments and 100 customers, your MDX needs to return 50,000 records, the report then has to summarise all of this into the level of grouping you want. This renders the pre-calculated aggregations in OLAP pretty much worthless.
To get around this, you can generate your MDX dynamically, so that the query returns the data already grouped into the correct level. You can also use this to add extra filters to the query, but only when they are required.
To start with, lets see how we would do this normally with SQL. Assuming we’re working from a denormalised table such as this
Dynamic SQL is pretty simple, instead of having your dataset query as
SELECT SalesPerson,
Sum(Sales) AS Sales
FROM tblData
GROUP BY SalesPerson
you can add a report parameter called GroupBy,
and then use an expression as your dataset
="SELECT "
+ Parameters!GroupBy.Value + " AS GroupName,
Sum(Sales) AS Sales
FROM tblData
GROUP BY " + Parameters!GroupBy.Value
However MDX queries don’t let you use an expression in the dataset, so we have to work around that quite major limitation. To do this we make use of the OpenRowset command. You need to enable it in the surface area config tool, but once it’s enabled you can fire off an OpenRowset command to SQL Server, which will then pass it on to the cube. As the datasource connnection is to SQL Server not Analysis Services, it allows you to use an expression in the dataset.
="SELECT * FROM OpenRowset('MSOLAP',
'DATA SOURCE=localhost; Initial Catalog=SalesTest;',
'SELECT
{[Measures].[Sales]} ON 0,
NON EMPTY {[Product].[Product].[Product].Members} ON 1
FROM Sales') "
You can then expand this to make it dynamic depending on the value of a parameter. Before we do this though, there are a couple of items I should point out.
1) As the expression can get quite large, I find it much easier to create the query from a custom code function
2) As SSRS can’t interpret the expression at runtime, you need to define the fields in your dataset manually (more on this later)
To use a custom code function, just change the dataset expression to
=Code.CreateMDX(Parameters)
We pass in the parameters collection so that we can use the parameters to determine the query. Create a function called CreateMDX() in the code block
You can then construct your MDX query within the code block.
Public Function CreateMDX(ByVal params as Parameters) as string
Dim mdx as string
mdx = "SELECT * FROM OpenRowset("
mdx += " 'MSOLAP', "
mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
mdx += " ' SELECT {[Measures].[Sales]} ON 0, "
mdx += " NON EMPTY {[Product].[Product].[Product].Members} ON 1 "
mdx += " FROM Sales ' "
mdx += ")"
return mdx
End Function
We’re almost there…
The next problem is that the field names returned by the query are less than helpful. To fix this we just need to alias the fields in the query. I usually take the opportunity of casting the numerical fields so that the report treats them as such, rather than as a string.
Public Function CreateMDX(ByVal params as Parameters)
as string
Dim mdx as string
mdx = "SELECT "
mdx += " ""[Product].[Product].[Product].[MEMBER_CAPTION]"" AS GroupName, "
mdx += " Cast(""[Measures].[Sales]"" AS int) AS Sales "
mdx += " FROM OpenRowset("
mdx += " 'MSOLAP', "
mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
mdx += " ' SELECT {[Measures].[Sales]} ON 0, "
mdx += " NON EMPTY {[Product].[Product].[Product].Members} ON 1 "
mdx += " FROM Sales ' "
mdx += ")"
return mdx
End Function
(please do watch out for the quotes, double quotes and double double quotes, it can get a little confusing!)
We then need to tell the dataset which fields to expect from the query.
You can now use the dataset in your report.
However, the original point of this was to make the query dynamic… All we need to do to achieve this is expand the VB.Net code accordingly.
Public Function CreateMDX(ByVal params as Parameters) as string
Dim mdx as string
mdx = "SELECT "
IF params("GroupBy").Value.ToString()="Product" THEN
mdx += " ""[Product].[Product].[Product]"
ELSE IF params("GroupBy").Value.ToString()="SalesPerson" THEN
mdx += " ""[Sales Person].[Sales Person].[Sales Person]"
ELSE IF params("GroupBy").Value.ToString()="Customer" THEN
mdx += " ""[Customer].[Customer].[Customer]"
END IF
mdx += ".[MEMBER_CAPTION]"" AS GroupName, "
mdx += " Cast(""[Measures].[Sales]"" AS int) AS Sales "
mdx += " FROM OpenRowset("
mdx += " 'MSOLAP', "
mdx += " 'DATA SOURCE=localhost; Initial Catalog=SalesTest;', "
mdx += " ' SELECT {[Measures].[Sales]} ON 0, "
IF params("GroupBy").Value.ToString()="Product" THEN
mdx += " NON EMPTY {[Product].[Product].[Product]"
ELSE IF params("GroupBy").Value.ToString()="SalesPerson" THEN
mdx += " NON EMPTY {[Sales Person].[Sales Person].[Sales Person]"
ELSE IF params("GroupBy").Value.ToString()="Customer" THEN
mdx += " NON EMPTY {[Customer].[Customer].[Customer]"
END IF
mdx += ".Members} ON 1 "
mdx += " FROM Sales ' "
mdx += ")"
return mdx
End Function
It’s certainly not that simple, and debugging can cause a few headaches, but you can benefit from a massive performance in complex reports if you’re prepared to put the work in.
You can download the project files here
As always, please let me know how you get on with it, and shout if you have any queries…
Alex
