I have been doing a fair amount of work on Row Level Security (RLS) where the requirements were quite complex to implement.

Once I had completed the implementation of RLS there were two outstanding items I needed to test.

  1. Make sure that the users are only viewing the data they should see.
  2. Ensure that the performance of a selected user is still as fast as possible.

I would like to mention that this certainly can be done using DAX studio to get the query performance stats, I wanted to test this using Excel.

The reason I wanted to use Excel is because a lot of the users will be using Excel to view and interact with the data and see the performance.

Please NOTE for my testing the dataset was in a Premium Per user app workspace.

For this to work I had to use the Excel desktop application.

Before I completed the steps below, I made sure I got the following information I needed to change later.

  • Effective username
    • This is the username or UPN of the user I want to test.
    • In my example it is gilbert@mydomain.com
  • App Workspace Name
    • This is the app workspace where the dataset resides.
    • In my example the app workspace is called “Power BI – DRLS”
  • Dataset Name
    • This is the dataset within the app workspace.
    • In my example it is called “1. Static Security”
  • This is what it looks like below where I got my information.

The easiest way to create the connection to the Power BI App Workspace is to open the Excel desktop application.

Then click on Insert Ribbon, then Pivot Table and finally click on “From Power BI”

Then select any dataset to connect to. The reason I clicked on any dataset is in the steps below I am going to change the settings.

In the steps below is where I am going to explain how to change the data source connection to test as a different user.

To get to the connection properties I did the following to open the connection.

I clicked on Data in the ribbon then clicked on “Queries & Connections”

Now on the right-hand side I can see my connection, I then right clicked on the connection and selected properties to view the connection properties.

In the Connection Properties Window, click on Definition.

I could then see the Connection String in the window as shown below.

Next I needed to change the connection string properties to get it working for another user.

Before copying the connection string, I updated the details below based on my previous information.

Provider=MSOLAP.8;Persist Security Info=True;Initial Catalog=1. Static Security;Data Source=powerbi://api.powerbi.com/v1.0/myorg/Power BI – DRLS;MDX Compatibility=1;Safety Options=2;EffectiveUserName=gilbert@mydomain.com;MDX Missing Member Mode=Error;Update Isolation Level=2

What I need to point out is the following details that I updated in the connection string above from the details I obtained, highlighted in the colour below.

  • Effective username
    • In my example it is gilbert@mydomain.com
  • App Workspace Name
    • In my example the app workspace is called “Power BI – DRLS”
  • Dataset Name
    • In my example it is called “1. Static Security”

I then copied and pasted this into the connection string as shown below.

I then clicked OK, this then connects to the dataset, it runs as the effective username and displays the results.

In my example this is what the Excel Pivot table looks like without any restrictions.

And this is what it looks like when I have changed the connection string to use the effective username.

If I wanted to test this as another user, I would then go back to the connection string properties and change the “EffectiveUserName”

Summary

The use of the Excel file in this way was a great way for me to test and experience what it would be like for another user who was also going to use Excel. It allowed me to view the performance of the pivot tables when the RLS was also being applied.

Thanks for reading!