MDX Stored Procedures (Part 1)

UPDATE:  You can find part 2 here, along with a link to download a C# quick start.

A long time ago I realised that inevitably, however well specified the requirements or however tight the deadlines are, clients always throw a curve ball.  To that avail, like many, I adopt an agile approach to project delivery with a keen eye on building frameworks and generic components that improve productivity and flexibility for current and future projects.

My current project is no different to any other, we knew the requirements would change as, even from the outset, they were high level and vague.  With little budget to flesh them out, a controlled, iterative approach was essential.  It’s a typical BI project, various data sources require consolidating and processing to allow analytics and reporting to assist in strategy and revenue generation.

The reporting element was the vaguest of them all, although what did exist immediately eliminated using Reporting Services as the reporting/delivery mechanism.  The required reports were highly interactive and aesthetically challenging enough to stretch Reporting Services functionality to the limit, even without the vagueness busting curve balls out of sight but already primed for deployment.

With all this in mind, we decided to use ASP.NET 2.0 web forms for the reporting as this gave us a little more freedom with regard to layout, a high degree of flexibility surrounding interactivity and the ability to quickly react to the ever changing requirements.  Now, with an eye on productivity we decided to build an MDX Helper class (mentioned previously here and here) that would streamline the development process and enable us to react quickly to those inevitable changes.

Our own requirements for such a component were as follows:

• Allow text files containing parameterised MDX to be executed.
• Must support the execution of multiple queries over a single connection
• Return a conventional ADOMD cell set object containing the query results
• Return a typed/untyped ADO.NET DataTable object for binding to native and third party .NET controls
• Allow the inspection of various server, database and cube properties
• Lightweight, re-usable and simple to use

The public interface of the resultant component looks like this:

In it’s simplest form, usage is straight forward.

Take the following parameterised query stored in file UKSales.txt

select
[Product].[Product Categories].[Category].members on rows,
[Measures].[Internet Sales Amount] on columns
from
[Adventure Works] where
@Country

You return an ADOMD cell set object using the following C# code snippet:

// Create an instance of the helper class, setting cube and query information
MDXHelper helper = new MDXHelper(“ASTestServer”, “Adventure Works DW”, “Adventure Works”, MDXHelper.MDXResourceFormat.RelativeURL, “UKSales.txt”);

// Add in the country parameter
helper.Parameters.Add(“@Country”, “[Geography].[Geography].[Country].&[United Kingdom]”);

// Execute the query
CellSet result = helper.ExecuteCellSet();

// Tidy up
helper = null;

There are several overrides on the constructor and ‘Execute’ methods to allow for flexible usage and it’s been used in anger now for a good couple of weeks supporting every type of MDX query we’ve managed to throw at it so far.  It still needs a little refinement and some full-on testing but it has achieved its goal and has helped us easily digest changes to the original requirements.  It has also given us some great ideas for version 2 where the query definitions are stored in the Analysis Services cube definition itself.

In a future post I’ll delve into the approach and implementation of the helper class to see how it works under the hood.