0845 643 64 63

Monthly Archives: September 2008

Dynamic MDX in Reporting Services

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 MDX Table

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,

Dynamic MDX SQL

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

Dynamic MDX Code

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.

Dynamic MDX Fields

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.

Dynamic MDX Results

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

Mosha's MDX Studio

I almost feel embarrassed…, I’ve been writing this blog for over 9 months now, and I have yet to mention Mosha, although in my defence, there is a link to his blog in the links section to the right.

As many/most of you may know, Mosha Pasumansky is one the key brains behind designing the MDX language and Analysis Services – nuff said?

Over the last year he has been working on a pet project, MDX Studio. It’s an MDX query tool which any self respecting OLAP developer should now be using on a regular basis. He has just released v0.4.6, which adds some really nifty features such as the dependency view.

If you’re just starting out with MDX, then the intellisense will be of massive benefit to you; even if you’re a seasoned pro, the performance monitoring is an essential tool on its own.

If you haven’t already tried it, have a look at Mosha’s blog, and get a copy – you won’t regret it.

And thanks for all your hard work Mosha – It’s much appreciated.

Alex

SQLBits III and more…

Next week we’re lucky enough to have a double-whammy of SQL events in the UK, has Christmas come early?…

On Wednesday 10th September, Microsoft are running a live Technet event, SQL Server 2008 Unleashed, at their Reading campus. It looks like it should be a day well spent, with Prem Mehra delivering a keynote speech on large scale SQL Server systems, and other sessions including data warehouse improvements, upgrade paths, and a session focusing on BI. The event is now full, so I hope you’ve already booked your place…

Then, on Saturday 13th September, we’ve got the long awaited SQLBits III event in Hatfield. There are far too many sessions to list here, but with dedicated tracks for BI, Admin, Development and Katmai, there is certainly something for everyone. You can safely assume that I’ll be on the BI track all day!

I’ll be going to both events; I know a few of you regular frog-blogees are also going, so I look forward to meeting you at one or both.

Alex

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon