Search code examples
sqlvisual-studiotestingssasmdx

How to automate MDX testing of SSAS

I've developed some MDX queries which are used to test our SSAS cube. I would like to automate these queries so that I could execute them all with a click of a button and ideally green bar/red bar based on their output.

Is there a way I could hook these queries up with Visual Studio to get this behavior?

Solution

  • You can try any unit testing framework here. Although unit tests aren't intended for such use, they can be useful there - test runners have red/green indicator out of the box.

    Write test which executes mdx using ADOMD.NET ( http://j.mp/NtKFih ) and exception during execution will fail test. You can also investigate result using CellSet object and decide if test has passed.

    Simple example using Microsoft's Unit Testing Framework (references To System.Data.dll and Microsoft.AnalysisServices.AdomdClient.dll are required):

    using Microsoft.AnalysisServices.AdomdClient;
...
[TestMethod]
public void CubeHealthCheck1()
{
    using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;
           Initial Catalog=SejmCube")) //your connection string here
    {
        conn.Open();
        AdomdCommand cmd = conn.CreateCommand();
        //your mdx here
        cmd.CommandText = "SELECT NON EMPTY { [Measures].[Glosow Przeciwko] } 
                           ON COLUMNS FROM [Sejm]";
        CellSet cs = cmd.ExecuteCellSet();
    }
}

    Any exception will fail test (no connection for example) - you can add try/catch and in message inform what went wrong.

    You can run this test from for example Test List Editor window Test List Editor window and then in Test Results window you have result with indicator Test Results window

    If you don't want using unit testing framework, you can develop custom visual studio extension (http://j.mp/QfMNQt) with similar logic inside.