Unit Testing MDX
I was recently asked if it was possible to do some smoke testing on new deployments of SQL Server Analysis Services cubes. My initial answer was “yes, of course” – then someone asked me to actually do it. It turns out to be as easy as I thought it was but I wasn’t able to find any particular blog posts regarding how to do it so here goes. In order to perform unit tests against a cube, do the following:
- Create a new C# (or VB) Unit Test Project
- From the SQL Server 2014 (or SQL Server 2012 – but for SQL Server 2012 this is all built into Visual Studio) feature pack download page (https://www.microsoft.com/en-us/download/details.aspx?id=42295) select Download and select the 32 or 64 bit version of ADOMD.NET
- Add a reference to the Microsoft.AnalysisServices.AdomdClient.dll (located in C:\Program Files\Microsoft.NET\ADOMD.NET\120)
- Add a reference to System.Data
- Import the namespace Microsoft.AnalysisServices.Adomd
You’re ready to go. The following is a simple unit test again the TFS Analysis cube:
1: using System;
2: using Microsoft.VisualStudio.TestTools.UnitTesting;
3: using Microsoft.AnalysisServices.AdomdClient;
4: using System.Diagnostics;
5:
6: namespace dwUnitTests
7: {
8: [TestClass]
9: public class UnitTest1
10: {
11: [TestMethod]
12: public void TestMethod1()
13: {
14: //Create the connection to the cube using integrated authentication
15: AdomdConnection connection = new AdomdConnection("Data Source=Olympia;Initial Catalog=Tfs_Analysis");
16: //Create the command and initialize it
17: AdomdCommand command = new AdomdCommand();
18:
19: //Provide the MDX to retrieve data from the cube
20: command.CommandText = "SELECT NON EMPTY { [Measures].[Work Item Count] } ON COLUMNS, "
21: + "NON EMPTY { ([Work Item].[System_WorkItemType].[System_WorkItemType].ALLMEMBERS ) } ON ROWS "
22: + "FROM [Team System]";
23:
24: //Set the connection for the command
25: command.Connection = connection;
26:
27: //Open a connection to the cube
28: connection.Open();
29:
30: //Retrieve the data
31: CellSet cs = command.ExecuteCellSet();
32:
33: //Close the connection
34: connection.Close();
35:
36: //Validate the data
37: Assert.IsTrue(cs.Axes[1].Positions[0].Members[0].Name == "[Work Item].[System_WorkItemType].&[Bug]");
38: Assert.IsTrue((int)cs.Cells[0].Value == 3);
39: Assert.IsTrue(cs.Axes[1].Positions[1].Members[0].Name == "[Work Item].[System_WorkItemType].&[Product Backlog Item]");
40: Assert.IsTrue((int)cs.Cells[1].Value == 1);
41: Assert.IsTrue(cs.Axes[1].Positions[2].Members[0].Name == "[Work Item].[System_WorkItemType].&[Task]");
42: Assert.IsTrue((int)cs.Cells[2].Value == 4);
43: Assert.IsTrue(cs.Axes[1].Positions[3].Members[0].Name == "[Work Item].[System_WorkItemType].&[Test Case]");
44: Assert.IsTrue((int)cs.Cells[3].Value == 2);
45: }
46: }
47: }
48:
A couple of things to make life easier. I don’t write MDX that often and I can never remember the syntax when I have to. I had though that the most difficult part of this would be creating the select statement (line 20). It turns out you can do it without any work at all. To generate the MDX statement:
- Open SQL Server Management Studio and connect to your cube
- Browse the cube and create the dataset that you want returned as shown in Figure 1
Figure 1 – Browse view of an Analysis Services cube
- Next, click the Design button on the right side of the toolbar to unselect it which results in the view shown in Figure 2
Figure 2 – MDX View to provide the result set shown in Figure 1
- At this point, just copy the MDX, remove the formatting options (including and everything following the “Cell Properties”) and you’re done!
A word of caution on the Assert statements. I am using Axes and Positions to get the values here because I am using ExecuteCellSet to retrieve the data. There are other methods of retrieving the data so be careful when constructing the assert statements. It took me a bit of trial and error to be able to drill through the object model in debug mode to figure out what I should be testing.
And that’s it – you can now perform quality checks on the results of a cube to ensure that the cube is processing correctly. This probably does not apply to very many people but for those that it does apply to, using proper software development techniques for cube development is every bit as important as using these techniques elsewhere. Maybe more so because end users rely on data from cubes to drive business decisions – if this data is wrong…