Sacha Tomey

Sacha Tomey's Blog

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:

Class

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.

Comments (8) -

  • Anonymous

    10/18/2007 1:53:39 PM | Reply

    Hi Sacha,
    Any word on when you're going to release the MDX Helper?

  • sachatomey

    10/18/2007 4:10:41 PM | Reply

    Hi Yosi,

    Well, a version has gone into production and appears to be working quite well.  I will get round to packaging up a 'quick start' solution for public release but if you can't wait for that (hopefully around November time) I can let you have the raw class with a couple of usage examples.

    Just drop me an email and I will email you something back to get you going.

  • Anonymous

    11/19/2007 12:28:45 AM | Reply

    Hi Sacha,

    I'd be very interested in getting hold of the helper class if possible?

    Regards,
    Phillip Higgins

  • sachatomey

    11/19/2007 9:14:53 AM | Reply

    Hi Phillip,

    Drop me an email - through this site - and I'll send you something back.

    Regards
    Sacha

  • Anonymous

    4/4/2008 11:21:02 AM | Reply

    Please provide me a URL for Helper Class.

    thanks
    joji
    jskk4@hotmail.com

  • joji

    4/4/2008 11:59:10 AM | Reply

    Please provide me link or some inside code for helper class.

    Thanks
    joji

  • joji

    5/6/2008 1:06:34 PM | Reply

    Thanks

    I have already implemented it. But nice to have this class as well Smile, Thank you so much...

    cheerz
    joji

Loading