Get ready for data testing in Power BI workspace

Share it with the world!

Use CAT to test Power BI data

When you are using CAT for testing your Power BI models, you are usually in need to connect and test the data also in production – means data hosted in Power BI workspace. When using Power BI Premium license, you are actually capable to connect to the data itself, thus you can also test it! This article overviews how to make the data available so you could seamlessly access it, either from CAT or any other tool.

Why would I test data in a Power BI workspace?

Yes, I hear you saying “I tested them in preview stages…”. And that’s good.

But – there are so many things that can go wrong at the end of the data’s journey:

  • the relationships in the model are not modelled correctly
  • some measures stopped working after update of relationships or related metrics
  • calculated columns have wrong values
  • the data in the model is not refreshed and nobody noticed
  • there are problems in row-level security and the user does not see any data

OK, but is it even possible? How do I do that?

Yes. You need to setup couple of things before you start testing.
  1. Create a service principal – the principal will access the data in your Power BI workspace datasets
  2. Create an Azure AD security group
  3. Enable service principals in Power BI Admin portal
  4. Allow access to the Power BI workspace for the principal

Sounds like a lengthy process, but with enough permissions you’ll have it done in few minutes. 

Crete a service principal

Go to Microsoft Entra ID (formerly Azure Active Directory). Go to “App registrations”.
New registration in Microsoft Entra ID (Azure Active Directory)
You only need to provide a name for the service principal, leave the rest untouched. Choose a name that follows your naming conventions. I chose sp-cat-power-bi-tester. Click Register. You will need Application ID and Directory ID:
Service principal details
Now we need to create a secret for the principal:
Once you have a secret, make sure you get it’s value! It will not be possible to get it later. Use the copy button. Make sure you do NOT confuse it with Secret ID – we need secret’s value:
Copy the secret's value
Welcome to the world, service principal.

Don’t close the Microsoft Entra ID interface, we also need to create a security group and make the principal a member of it. Creating a group is a simple task and we’ll not cover it here. 

Enable Service Principals in Power BI Admin Portal

Now we need to change one setting in Power BI Admin portal. Go to Open settings (upper right corner) and go to Admin portal.
Find this setting, enable it and put the name of the group you just created:
You need to enable this setting in Power BI Admin portal.

Allow access in Power BI Workspace

Go to the workspace the service principal should have access to and give him permissions. There is one unexpected thing you need to be aware of. Even though you will use the service principal for testing, giving him Viewer permissions is not enough. Actually, you need to assign Admin permissions, otherwise the access does not work.

OK, I have it, what next?

Congrats, you just set up everything you need to test data in your Power BI workspace. You can now automate your tests, even put them into CI/CD pipelines etc.
So the next step is: create your first Power BI automated test 🙂

If you have no experience with CAT yet, start here:

Installing CAT

Install CAT

Click to copy the command and paste it into your PowerShell 7 terminal!

Direct Download

Download CAT directly from PowerShell Gallery and give it a try!