MDX Stored Procedures (Part 2)
What seems like a lifetime ago I posted about MDX Stored Procedures and how we've built a helper class that provides a wrapper to an MDX query.
The helper class has been stable for quite sometime now and a few people have been asking me when I'm going to release it. Well, today is the day.
You can download a Visual Studio 2005 C# Quick Start project from here.
The idea behind the class is that text files containing parameterised MDX queries can be executed against a cube with just a couple of lines of code.
Here are some notes to complement the quick start to get you going:
Step 1: Create an instance of the helper class
MDXHelper helper = new MDXHelper
("localhost",
"Adventure Works DW Standard Edition",
"Adventure Works",
MDXHelper.MDXResourceFormat.RelativeURL,
"MDX/Product Category List.txt");
There are a couple of overloads on the constructor. The one above specifies all the options. The first three parameters set the Analysis Services Server, the database, the cube. The last two parameters specify what format the MDX query. In this case it's a Relative URL meaning that the next parameter must specify a relative URL pointing to a text file containing the desired query. The alternative to a Relative URL is 'Text' which means the last parameter must be the query itself rather than a path to a file.
Step 2: Add the parameters to the parameter collection.
helper.Parameters.Add(
"@ProductCategory",
CategoryList.SelectedValue);
Parameters are simply name/value pairs and are held internally as a generic dictionary<string,object>. Parameters are optional but you will get an error if you invoke a parameterised query without defining a value for every parameter.
Step 3: Execute the Query
DataTable categoryList = helper.ExecuteDataTable();
A DataTable or a CellSet can be returned, again various overloads exist to provide additional flexibility, for example you can set the resource format and query path on the ExecuteDataTable e.g.
DataTable result = helper.ExecuteDataTable(MDXHelper.MDXResourceFormat.RelativeURL, "MDX/Internet Sales by Country.txt");
Additional Notes
The query definitions are cached in the ASP.NET application cache so if you make any changes to the query definition files you'll need to recycle the application pool.
The helper class will only work for Web Projects. There is no support for WInForms/SmartClient deployments.
If you are returning a data table you can specify some additional options.
- Untyped is the fastest and default, returning a plain data table based on a flattened CellSet object.
- Typed brings back a Data Table with the Schema column data types set to match the CellSet object it was derived from. This can be slow for large datasets.
- UntypedFormatted brings back a plain data table but using the formatting e.g currency formats etc held in the cube.
There are some additional properties containing meta data debugging information that have been useful. ExecutedMdxQuery contains the actual MDX that was executed after the parameter replacements have occurred. DatabaseServerVersion, CubeDescription, CubeLastProcess and CubeLastSchemaUpdate are pulled from the server when a query is executed.
You can use parameters to define any aspect of the query. E.g. You could use parameters to swap rows and columns, define measures, pretty much anything you can achieve with search and replace. On one hand this provides no end of flexibility but on the other provides diddly squat compile time debugging!
The same database connection is maintained between query execution calls.
All feedback gratefully received.