The past few weeks at work, I’ve been building out a process for automation of a Microsoft Tabular Model project; in a previous post I described how we automated the deployment process, and this post will focus on testing. A full, working sample project is available on GitHub.
The Problem: Testing Measures
In SQL terms, a measure is somewhere between a view and an analytic function; it’s a calculation used to dynamically aggregate and filter report tables, and it can contain a fair bit of logic. Measures can also depend on other measures, and they’re often used to encapsulate logic, even if they won’t be exposed directly to the end user.
In our case, we needed to work closely with business analysts to define the measures in business terms, ensure that they were implemented correctly, and protect ourselves from regressions as we build out additional reporting. We knew from the start that we wanted a BDD-style approach, and we settled on SpecFlow as our test framework.
There are six steps that need to be performed for each test scenario:
- Ensure that the database is in a clean (empty) state
- Load a small, known data set into the proper database tables
- Build and deploy the Tabular Model to an SSAS instance
- Process the Tabular Model against the database
- Query the measure under test
- Compare the query results to the expected values
To accomplish these tasks, we divided the work into three logical pieces: A testing framework to do data comparisons, a helper module to interact with the SQL Server database, and a second helper to interact with the Tabular Model.
We want to run all of our tests locally, so we need to have both SQL Server and Analysis Services instances running on
localhost; Both of these are freely available in the developer edition of SQL Server.
If you aren’t familiar with BDD or SpecFlow, there is a nice walkthough here. Essentially we’ll describe the desired behavior in plain text, using Gherkin; an individual test case is known as a Scenario, and multiple Scenarios are grouped into a Feature.
Each Scenario has multiple Steps, and each Step will be implmented using our programming language of choice–in this case, C#.
In the example project, our measure is a simple average. The gherkin for a very basic test case might look like this:
Feature: AverageAge Calculating the average age of people Scenario: A single person Given I have persons: | Name | Age | | Bob | 5 | When I query for average age Then I expect the result to be: | Average | | 5 |
This scenario has three Steps–a
When, and a
Then. We can have Visual Studio generate empty step definitions for us automatically, and fill in the implementation details.
When testing Tabular measures, our Step implementations will be very similar across Scenarios and even Features–
Given will be inserting data into the SQL database and refreshing the Tabular model;
When will correspond to a query against the model, and
Then will be comparing the query result against the expected values.
It’s normal to re-use Step definitions across Scenarios; since each of our Features will be testing a single measure, we’ll typically have many scenarios that use the exact same steps, where the only difference is in the data. To see an example of this, look at the Feature and Steps for calculating average age.
Since we’ll be doing the same interactions over and over again, it makes sense to extract out the common logic into a couple of helper classes–one for inserting data into the SQL database, and the second for querying data back out of the Tabular model.
Interacting with the Database
The local SQL Server instance will act as the data source for our Tabular Model; in order to set up our tests, we need two things: A way to set up our initial schema, and a way to manipulate data in the database.
The first thing we need is do build an empty database with the proper tables. There are various ways to handle database migration, and the method you choose has no real effect on the rest of the testing pipeline; the only requirement is that the schema of the test database should match whatever is being used in production.
We use alembic, but a Database Project or Entity Framework would work just as well. Technically you could do this step by hand, and create the database as a one-off import, but if you aren’t doing your database migrations in a repeatable way, you probably should be!
Once we have our schema in place, we need a way to delete and insert data into our tables. The example uses a basic
SqlCommand, but the details don’t matter; EntityFramework or a micro-ORM would work just as well. The only critical piece here is that we need to be able to expose our implementation to the test framework via context injection.
Interacting with the Tabular Model
To refresh and query the Tabular Model, we use the ADOMDClient library, which exposes a familiar set of interfaces (
Adapter, etc) for interacting with tabular data.
Similar to the SQL Server module, we want to share this package among our test package; in the example project, both the
TabularHelper modules are injected via the ConnectionSupport class.
Refreshing the Model
Querying the Measure under Test
Once we’ve loaded our test data, we want to query whichever measure we’re testing, and check the output against our expected values. Because there’s we’re only interested in the data itself (there’s no real business logic), we opted to use a simple
DataTable to return the data.
In the example project, there’s a single method which executes an arbitrary DAX query and returns a
DataTable object; you can easily flesh out the
TabularHelper with additional query methods, to better encapsulate the query logic.
Running the Test Suite
Now that we have our tests and helper modules in place, we need to build and deploy our model, then execute the test suite. The build and deploy process is the same as outlined in the previous post. We typically run our tests via a PowerShell script, but you can also run them from Visual Studio itself, using the IDE Integration.
Configuring Connection Strings
Assuming that we have local copies of both the database and Tabular model, the last thing we need to do is inject the proper connection strings to our helper modules. We used environment variables to do this, but a config file could also work.
To set up the proper environment variables, execute the following:
$env:TabularAcceptanceTestAnalysisServicesConnectionString = "Data Source=localhost;Catalog=validation;User ID=test_ssas;Password=test_ssas" $env:TabularAcceptanceTestSqlConnectionString = "Server=localhost;Database=validation;User ID=test;Password=test;"
Note that if you want to run the tests from Visual Studio, you’ll need these environment variables set in the process where Visual Studio is running; one way to do this is to launch Visual Studio from a shell with the appropriate variables set:
'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe'
Running the tests
Once the environment is set up, you can execute the scripts to build and deploy the model, then run the test suite:
.\deploy_model.ps1 -workspace c:/develop/tabular-automation -environment validation -analysisServicesUsername test_ssas -analysisServicesPassword test_ssas
.\run_validation.ps1 -databaseUsername test -databasePassword test -analysisServicesUsername test_ssas -analysisServicesPassword test_ssas
You should see output like the following:
NUnit Console Runner 3.7.0 Copyright (c) 2017 Charlie Poole, Rob Prouse Runtime Environment OS Version: Microsoft Windows NT 10.0.16299.0 CLR Version: 4.0.30319.42000 Test Files TestExample\TestExample.csproj => TestExample.AverageAgeFeature.ASinglePerson Given I have persons: --- table step argument --- | Name | Age | | Bob | 5 | -> done: AverageAgeSteps.GivenIHavePersons(<table>) (3.5s) When I query for average age -> done: AverageAgeSteps.WhenIQueryForAverageAge() (0.2s) Then I expect the result to be: --- table step argument --- | Average | | 5 | -> done: AverageAgeSteps.ThenIExpectTheResultToBe(<table>) (0.0s) => TestExample.AverageAgeFeature.BlankAgesAreTreatedAsZero Given I have persons: --- table step argument --- | Name | Age | | Bob | 5 | | Jane | | -> done: AverageAgeSteps.GivenIHavePersons(<table>) (0.3s) When I query for average age -> done: AverageAgeSteps.WhenIQueryForAverageAge() (0.0s) Then I expect the result to be: --- table step argument --- | Average | | 2.5 | -> done: AverageAgeSteps.ThenIExpectTheResultToBe(<table>) (0.0s) => TestExample.AverageAgeFeature.TwoPeopleWithTheSameAge Given I have persons: --- table step argument --- | Name | Age | | Bob | 5 | | Jane | 5 | -> done: AverageAgeSteps.GivenIHavePersons(<table>) (0.2s) When I query for average age -> done: AverageAgeSteps.WhenIQueryForAverageAge() (0.0s) Then I expect the result to be: --- table step argument --- | Average | | 5 | -> done: AverageAgeSteps.ThenIExpectTheResultToBe(<table>) (0.0s) => TestExample.AverageAgeFeature -> Using app.config Run Settings BasePath: C:\develop\tabular-automation\TestExample\bin\Debug\ DisposeRunners: True WorkDirectory: C:\develop\tabular-automation ImageRuntimeVersion: 4.0.30319 ImageTargetFrameworkName: .NETFramework,Version=v4.5.2 ImageRequiresX86: False ImageRequiresDefaultAppDomainAssemblyResolver: False NumberOfTestWorkers: 2 Test Run Summary Overall result: Passed Test Count: 3, Passed: 3, Failed: 0, Warnings: 0, Inconclusive: 0, Skipped: 0 Start time: 2018-01-07 17:11:56Z End time: 2018-01-07 17:12:02Z Duration: 5.872 seconds Results (nunit3) saved as TestResult.xml
The End Result
Once we have the deployment and test scripts in place, we can integrate them with a CI server to set up a deployment pipeline. To do this, we first deploy the new version of the model to a dedicated
validation environment where the automated test suite is run; if all the tests pass, we automatically build and deploy again, this time to a staging environment. In the staging environment, analysts can interact with the Tabular model through front-end tools like PowerBI; once the changes have been approved, we can promote the new version of the model to production.