Unit Testing SQL Server OLAP Cubes Using C#

2
votes

I have a SSAS cube which needs to be tested. I want to insert some values into my test database and then trigger cube to be processed on my test database and then write tests again dimensions/measures and cube itself based on the values inserted into dim tables.

The cube has a data source which should be configurable. I created a SSIS package which has a Analysis Services Processing Task to process the cube and I can execute the package in my test project but when running the ssis package directly seems to process the cube whereas executing it through the test project doesn't although it doesn't throw any exception.

Basically I need to know if there is any way to unit test the OLAP cubes either using an SSIS package or any other ways.

Thanks for your help.

3
ssasolapolap-cube
One way of solving this problem is to write own code on top of Visual Studio Unit tests. You can have helper function to run you processing pipeline: SSIS, Stored Procedures, any other code and then write MDX queries and run them (using helper functions) in unit tests to compare with expected results. Here you can find something you are after: stackoverflow.com/questions/12077949/… – user170442
Thanks for your comments. I have already written MDX queries to read the dimension tables and cubes however I need to automate the testing so that it doesnt rely on a cube existence. I want to process the cube every time I run the tests. – Sandra

3 Answers

1
votes

Here is a good example of how to do this:

http://msdn.microsoft.com/en-us/magazine/dn519927.aspx

1
votes

You should take a look to the framework named NBi. This framework is specialized in the tests for BI solutions and provide useful syntax to check dimension's members, OLAP structure and to check correctness of queries.

0
votes

There are many possible solutions for this, and they are all just 5 seconds away via your favourite search engine. Here's a starting point:

http://msdn.microsoft.com/en-us/library/hh230848.aspx

Perhaps what you want is to use the Database.Process method from Microsoft.AnalysisServices.