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.
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.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr