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.
Meet the Team – Catherine Sachdev, Marketing Assistant
Next up we’re introducing you to Catherine Sachdev. Catherine joined us just over a year
Jan
Data Lineage with Azure Purview
I wrote an introductory Purview blog post previously, where I explored what the tool is
Jan
The Next Era of Retail: How Technology is driving change in a COVID-19 World
The retail sector is of great importance and accounts for almost 5% of GDP and
Jan
Meet the Team – Alex Kordbacheh, Junior Consultant
It’s time for another Meet the Team blog! This time we’re introducing you to Alex
Dec
Use cases for Recursive CTEs
Introductions Recursive CTEs are a way to reference a query over and over again, until
Dec
Azure Sentinel is named a ‘Leader’ in the Forrester Research Wave Report
Microsoft have recently announced that they have been named a Leader by Forrester Research in
Dec
Getting Started with Azure Purview
Azure Purview (a.k.a Data Catalog gen 2) has been released into preview and is currently
2 Comments
Dec
An Introduction to ApexSQL Complete – Integration with SSMS
We all know that the idea of add-ins is to make our lives easier. In
Dec