Test Power BI

How to test Power BI data on your machine and in Power BI workspace.

CAT allows you to automate tests against your data in Power BI Desktop files and against data in datasets in a Power BI workspace.

Automate Tests Against Data in .pbix Files

You should test your models before you deploy them. CAT makes this easy. Just open your .pbix file and use PowerBI@1 provider for your data source:

Data Sources:
- Name: My PowerBI file
  Provider: PowerBI@1
  Connection string: AccountingData  # this is name of your open .pbix file (without the extension)

You can then start with your tests:

Tests:
- Name: Account table is not empty
  Suite: Smoke tests
  Data source: My PowerBI file
  Query: |
      EVALUATE('Account')
  Expectation: set is not empty

Power BI data is no different from any other data, you can do whatever tests you need on them (e.g., compare them with ORACLE or SQL Server data using sets match etc.)

Test Datasets in Power BI Workspaces

Before you start testing data in datasets in Power BI workspaces, you need to setup couple of things. It does not take long time (if you have enough permissions) and it is needed only before you start testing.

First, if you are not an administrator of your Power BI tenant, you need to find out who it is and share a link to this page with him/her.

What the administrator needs to do:

  • Create a service principal - the principal will access the data in your Power BI workspace datasets
  • Create an Azure AD security group
  • Enable service principals in Power BI Admin portal
  • Allow access to the Power BI workspace for the pricnipal

Sounds like lengthy process, but with enough permissions you’ll have it done in minutes. Please follow this tutorial by Microsoft to do the above mentioned steps:

https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-service-principal

Once this is done, you can simply use Dax@1 provider to access data in the workspace:

DAX provider example for connection to Power BI workspace dataset

  1. Here use your Power BI tenant name

  2. Use name of your Power BI workspace

  3. Name of the dataset you need to test

  4. Use names of environment variables, where you store service principal ID and secret (see below).

User ID must have form of app:ApplicationID@TenantID. Password must contain a valid secret value. You’ll get those values from your Power BI tenant administrator.

The idea is to not have connection string like Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name; Initial Catalog=Your-Dataset-Name;User ID=app:97c52ac8-6543-499a-b599-6dc87ee8b06d@ddbc54cb-ae6c-44f1-a8bf-a18e475d84dc;password=AX39Jsklfjds943l449skfdpp directly in your file. It does NOT belong there, it does NOT belong to the source control.

You can hide the entire connection string or parts of it to an environment variable. In the example above, there are two environment variables, one for the user name and one for the password. If you create environment variables AERO_POWER_BI_SP_NAME with value app:97c52ac8-6543-499a-b599-6dc87ee8b06d@ddbc54cb-ae6c-44f1-a8bf-a18e475d84dc (app:ApplicationID@TenantID) and AERO_POWER_BI_SP_SECRET with value AX39Jsklfjds943l449skfdpp, you can use the connection string from the example. CAT will automatically use those values, you don’t need to do anything execpt for creating the variables. This approach can be used in automated pipelines, most CI/CD platforms let you use environment variables.

Even better approach is to hide the entire connection string into an environment variable:

Data Sources:
- Name: My PowerBI Workspace
  Provider: Dax@1
  Connection string: '%MY_ENVIRONMENT_VARIABLE_CONTAINING_THE_ENTIRE_CONNECTION_STRING%'

Can I Test Power BI Dataset Without Service Principal?

Yes, but we do NOT recommend that. Most organizations have MFA enabled and you’d be bothered with confirmations. Twice each time before you invoke tests evaluation (CAT needs two connections). This is annoying, and in the end you’ll need service principals approach to automate the testing process anyway.

Can I Compare Power BI Data with My Database?

Sure, Power BI data is no different for CAT. Feel free to compare them against SQL Server, ORACLE, PostgreSQL, SSAS, even Excel, CSV, … whatever - in CAT you can compare data from different providers with no effort. We do our best to allow you to test the data where they are, without the need to copying them or load them somwhere.

← Work with Passwords
JUnit →