Your very first Power BI test

Petr Pařík

January 26, 2024

Data Testing, Power BI

Power BI testing has never been simpler

Do you know you can automate tests of your data in Power BI semantic models (formerly called datasets)? Let’s do it, now!

Why should you care

Bugs can be everywhere. It is great you tested the data in the previous stages, but if you omit testing of the “final destination”, you can still ship a solution with a big potential for problems. Do you hear the questions back in your head? Hesitations like “Did the data load correctly?”, “Is the new / changed relationship a problem for already existing measures?”, “When I fixed that one measure, didn’t I destroy a few others depending on that one?”, …

There are two options. Either you hope all is OK, or you have an evidence. The latter creates trust into your solution, to you and to your data audience.

Preparations

There are some preparations, but nothing big. Our previous blog describes what you need to do. TLDR: you need to change simple setting in a Power BI admin portal, create a service principal and give him access to your workspace.

Start simple

Start with automating one test only. Something simple, but something you care about. The magic of running the test with one command and immediately see a potential problem is addictive.

Prepare your CAT project

If you don’t have CAT installed, download PowerShell 7 (the latest and greatest) and run this command:

PowerShell

Install-Module CAT -AcceptLicense -Force -AllowClobber -Scope CurrentUser

Navigate into a folder of your choice and create a project:

PowerShell

Import-Module CAT
cd ~/Documents # or any other folder of your choice
mkdir AutomatedTestsForPowerBI # it is best practice to have each project in a separate folder
cd AutomatedTestsForPowerBI
New-CatProject AutomatedTestsForPowerBI

Open the AutomatedTestsForPowerBI.cat.yaml file in your favorite text editor. You need to setup your data source (you typically do this only once). It will look like this:

YAML

Data Sources:
– Name: My Power BI Dataset
Provider: Dax@1
ConnectionString: >
Provider=MSOLAP;
Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
Initial Catalog=Your-Dataset-Name;
User ID=%YOUR_SERVICE_PRINCIPAL_NAME_ENVIRONMENT_VARIABLE_NAME%;
Password=%YOUR_SERVICE_PRINCIPAL_SECRET_ENVIRONMENT_VARIABLE_NAME%;

Don’t forget to change “your-organization.com”, “your-workspace-name” and name of two environment variables. Those variables you need to create. When you were preparing access for service principal, you got Application ID and a secret. Put those into two new environment variables in MS Windows. Change the names in the connection string. Should you encounter some troubles, please refer to the tutorial in our documentation.

Up to now, it was boring, right? But once you have all this set (and it really does not take much time), you are all set and can start writing tests.

Add your first test and
run it

As mentioned, it is advised to start simple 🙂 The examples in this blog post will only work with one small dimension table. For illustratory purposes let’s use table ‘dim_version’ from our analytical solution. It looks like this:

First question is, does the table even contain any data? Such simple test is ideal for getting started.

YAML

Tests:
– Name: Table dim_version contains data
Description: If the table is empty, the load process needs to be checked.
Suite: smoke tests
Data Source: My Power BI Dataset
Query: |
EVALUATE ‘dim_version’
Expectation: set is not empty

For bigger tables TOP N should be used, but our table is small, it is enough to check it like this.

OK, from now on, you don’t need to manually verify whether there are data loaded. It is about running one command:

PowerShell

Invoke-CatProject ‘AutomatedTestsForPowerBI.cat.yaml’

Adding more tests

Once you are comfortable with repeating execution of one test, you’ll naturally be thinking about adding another tests. In our simple example, the dimension table with versions, what would increase our trust into the data in the table? Here are a few examples:

YAML

– Name: dim_version contains “Unknown” member
Description: |
Some metrics depend on unknown member. If it was removed or renamed,
it can create problems
Data source: My Power BI Dataset
Query: |
EVALUATE
FILTER ( ‘dim_version’, [VersionOfCat] = “Unknown” && [VersionOfCatID] = -1 )
Expectation: set rowcount
Expected RowCount: 1

As you see, adding new tests is simple. Just provide a name, description and a DAX query, as in the examples above.

What is also important to mention: often you need to be improvising and inventing. DAX does not excel at parsing text, but we need to know the version column really contains versions, not some rubbish. Lack of features in DAX (or any obstacle in general) does not mean you immediately give up. There are always ways to increase trust into the data.

Here are two examples of queries for the ‘dim_version’ table. The first removes dots and checks the rest is numeric. Second parses the major version and checks it is zero.

DAX

— when dots are removed, the value is numeric (except for “Unknown”)
EVALUATE
VAR versions =
(
SELECTCOLUMNS (
FILTER ( ‘dim_version’, [VersionOfCat] <> “Unknown” ),
“VersionOfCat”, [VersionOfCat],
“IsError”, ISERROR ( VALUE ( SUBSTITUTE ( [VersionOfCat], “.”, “” ) ) )
)
)
RETURN
FILTER ( versions, [IsError] = TRUE () )

— this checks major versions, only zero is expected so far
EVALUATE
VAR majorVersions =
SELECTCOLUMNS (
FILTER ( ‘dim_version’, [VersionOfCat] <> “Unknown” ),
“VersionOfCat”, [VersionOfCat],
“MajorVersion”, LEFT ( [VersionOfCat], SEARCH ( “.”, [VersionOfCat] ) – 1 )
)
RETURN
FILTER ( majorVersions, [MajorVersion] <> “0” )

What next?

These were just first steps. In fact, CAT can do lots of other things, like:

easily generate the tests from metadata (so that you don’t write the above simple test for each table separately)
store results of tests in MS Excel, JSON, relational databases and other
compare data between different systems (MS SQL server with Power BI, MS Excel with Postgres, …)
…

Check CAT’s documentation on related topis: