Power BI Embedded: Convention-based dynamic Row-level Security

Power BI Embedded is a great tool for ISVs offering a BI product for their customers. Generally, the reports exposed to the customers are personalized to some extent - one can use the Power BI JavaScript library to interact with the reports and tweak the visuals based on the logged-in user.

Another way reports differ from user to user is the underlying data that user is allowed to see. This is controlled using Row-level Security.

A common method to implement Row-level security is to pass the user's email address to the data model, and have rules filter the data model appropriately based on the data that user is permitted to see (this is called dynamic row-level security).

However, sometimes it can be useful to filter the data model based on more than just a user's email address. This blog indicates how to modify an Embed Request (made by Power BI Embedded) to provide additional context on what data the embedding application would like to be returned for a user to view on a report.

Recap: What is Row-level Security (RLS)?

RLS is a set of "rules" applied to a data model (at the row-level) before queries are evaluated on a report, to restrict the data that a user can see. In Power BI, this is implemented by defining "Roles" in Power BI Desktop.

Types of RLS

Static

Static roles define hard-coded rules on tables in your data model. With this method, there is no notion of what data a specific user has access to defined within the data model.

Example: You have a Countries table in your data model. You know you have users who only need to see data from the United Kingdom. Thus, you define a "UKEmployees" role with a rule defined on the Countries table. Something like Countries[Name] == "UK". This then propagates that filter across the data model through the relationships you've defined between the tables*. The users from the UK are then assigned to that role before viewing the report. How? Well that depends on whether you're exposing your report through the Power BI Service, or through Power BI Embedded. More on that shortly.

Dynamic

Dynamic roles collect details of the consumer of the report dynamically, and then apply RLS by matching those details with details of the user defined within the data model. This means that the data model can be filtered dynamically based on the data that user has access to.

Example: A table in the model called UserInCountry includes an "EmailAddress" column and a "Country" column. "John Doe" logs in, his email address is supplied to the data model, and this is then used to filter the UserInCountry table down to only the row(s) that pertains to him, and, in turn, the rest of the data model is then filtered accordingly. The "role" could be called "DynamicRLS", and the "rule" in this case would look like UserInCountry[Email] == USERPRINCIPALNAME(). What's the USERPRINCIPALNAME() function? That's the 'dynamic' part. The DAX USERPRINCIPALNAME function will return the UPN (i.e. the email address) of the current logged in user who is trying to view the report. Again, we have to somehow tell Power BI that we want to have this role applied to users who are going to view the report. Let's take a look how.

_{* Remember: filters will only propagate as far as the "arrows" on your relationships allow - as soon as you hit a relationship where the "arrows" are opposing the filter direction, the propagation stops.}

Assigning users to roles

For any reports that are exposed in the Power BI Service (or reports embedded in Teams/Sharepoint, embedded via Secure Embed or embedded using the User Owns Data method), users are assigned to rules through the Security settings on the corresponding dataset in the workspace in the Power BI Service.

For reports embedded via the App Owns Data method (otherwise known as "Embedding content for customers"), the role assignment happens as part of the Embed Request that the client sends to the Power BI API when requesting an embed token. The body of the embed request that includes RLS assignments usually looks something like this:

{
    "accessLevel": "View",
    "identities": [
        {
            "username": "<username>",
            "roles": [
                "<roleName>"
            ],
            "datasets": [
                "<datasetId>"
            ]
        }
    ]
}

The roleName is the name of the role as you've defined in Power BI Desktop. So, I could enter roles = [ "UKEmployees" ] if I wanted to assign a user to the static role example I mentioned earlier.

The username attribute is an interesting one. The value assigned to this attribute is what is returned by the DAX USERPRINCIPALNAME or USERNAME function within your report. In that sense, it's only really useful for Dynamic RLS, or when you make use of either of those functions within a measure in your report for some reason.

For pretty much any type of dataset (apart from Analysis Services live connections), the value that you provide for this attribute in the embed request can be any string whatsoever*. Most of the time, it will be the logged-in user's email address. However, sometimes it's necessary to get a little creative with this attribute. Here's why.

_{* This isn't entirely true. There are some restrictions depending on your data source. See the description of the username property here for more details.}

Passing additional filter information

Say we have the following tables:

Creating the following model:

So, a Security table with two users: Ed, and Carmel. Carmel has access to all countries' data, Ed only has access to the UK. This Security table has a bidirectional M:1 relationship (with "Apply security filter in both directions" applied) to a Countries dimension table which houses a list of countries. Finally, the Countries table has a single-directional 1:M relationship with a Spend fact table, which has yearly spend by country.

Let's now go into "Manage Roles" and create a "DynamicRLS" role, with a filter applied to the Security table as such:

Now remember - in the Power BI Service, USERPRINCIPALNAME() will return the email address of the logged-in user. But in Power BI Embedded, this function will return whatever is passed in as the username attribute in the embed request payload. Hence the lack of any email addresses in the Security table - we don't need to store them in our model. We can store whatever we want, as long as we know we have some fields to help filter our model down to the granularity we need. Those fields don't even need to be anything to do with a user. Anyway, I digress.

For demonstration purposes, I've included a [Name] column in the Security table. A simple way to test the role is to select the "View as" button under "Modelling" -> "Security" in the ribbon. Then, we select both the "DynamicRLS" role and the "Other user" checkbox, and in the "Other user" role, we enter our value. Let's enter "Ed".

This then filters the model:

As expected, the only data we see for "Ed" is that pertaining to the "UK". How about if we enter "Carmel" instead?

Again, as expected, we see all the data, since Carmel has been given access to each country's data. But what if we wanted to ensure Carmel only viewed one country's data at a time, for example? It might be that the report we're building doesn't really make sense for viewing more than one country at once, or we have another justification to enforce our users to only view one country at once.

We could add a report/page level filter to the filter pane and enforce single select on that filter, but one reason this isn't (currently) ideal is that the "Require single selection" toggle doesn't handle the case where a user deselects the currently-selected value on the filter, which removes that selection and therefore removes all filters on that field (which is the equivalent of displaying all values for that field).

We could add a slicer to the page and enforce "Single select" on this. The slicer visual's application of the "Single select" does work (i.e. it doesn't ever allow a single value not to be selected), but now we have to add an extra slicer on each page of the report, cluttering up the canvases.

We could have the application interact with the Filters API in the Power BI Embedded JavaScript library to apply the single-select restriction at the application level, but this filter logic could still be directly modified by the user depending on how the embedding is implemented.

Another option, and the purpose of this blog post, is that we can define a convention for the value that we'd like our application to assign to the username attribute it sends as part of the embed request. For example, we could define a convention where we ask the application to send us a value of the form <Username>:<Country>, e.g. Carmel:UK. All we need to do is as add some logic to the RLS rules to parse that string into its logically separate components: Username and Country. Here's the logic that does that:

After applying that RLS logic to the Security table, and viewing as Carmel:UK, we see this:

If we try viewing as Ed:US, we see no data - this is because Ed doesn't have access to view US data, therefore the RLS filter returns no rows in the Security table, and this context propagates across the model.

We can even create a couple of measures to retrieve the information we're passing into the custom string for RLS. The measures look like this:

User = 
VAR customString = USERPRINCIPALNAME ()
VAR indexOfSeparator = FIND ( ":", customString, 1, BLANK () )
RETURN
    LEFT ( customString, indexOfSeparator - 1 )

Country = 
VAR customString = USERPRINCIPALNAME ()
VAR indexOfSeparator = FIND ( ":", customString, 1, BLANK () )
RETURN 
    RIGHT ( customString, LEN ( customString ) - indexOfSeparator )

And, as expected return this (in the case of Carmel:UK):

Everything's now in place to implement this convention-based dynamic RLS.

Testing it out

After publishing the report to a workspace, we need to perform a couple of API requests. For ease, I'm going to use the "Try it" functionality in the Power BI REST API to make my API requests. I'm then going to test the Embedded functionality using the Power BI Embedded Playground, using the "Code" tab in the "Sample tool".

The "bits" we need for embedding a report with RLS are the Workspace ID, Report ID, Dataset ID, the Report Embed URL and an Embed Token.

The Workspace ID can be retrieved from the URL when you're viewing content within the workspace within the Power BI Service. It's the ID that appears after /groups/.

The Report ID can be retrieved from the URL when you're viewing the report in the Power BI Service. You can also retrieve this using the Get Reports in Group endpoint, if you need to retrieve it by name, for example.

The Dataset ID can be retrieved from the URL when you've entered the dataset settings in the Power BI Service. Alternatively, you can get it using the Get Report In Group endpoint if you have the Report ID, or the Get Datasets in Group if you need to retrieve it by name, for example.

The Report Embed URL can be retrieved using the Get Report In Group endpoint. This takes the workspace ID and the report ID.

Finally, the Embed Token is retrieved using the Generate Token In Group endpoint, passing in the workspace ID and report ID. This is a POST method, accepting a body of the form mentioned earlier in this blog post. During report development, you can hit this endpoint a limited number of times without the workspace being assigned to a capacity, However, when moving to production, the workspace will need to be assigned to a capacity. See this link for more info.

In our case, the body of our embed request looks like this:

{
    "accessLevel": "View",
    "identities": [
        {
            "username": "Carmel:UK",
            "roles": [
                "DynamicRLS"
            ],
            "datasets": [
                "68b...-...-...-...d69"
            ]
        }
    ]
}

After making the request, we receive a response which includes the embed token. (Note: If this call isn't successful, make sure a) you have access to the report (& dataset) you're trying to embed, and b) you've been granted the permission to "Embed Content in Apps" through the tenant admin settings.) Now, using the Embed Token, Embed URL and Report ID we've just received and using them in the Power BI Embedded playground, we see this:

Which, as you can see, is our report which has been filtered down to just the data we specified as part of our RLS convention - "Carmel" and "UK".

Conclusion

We have learned that we can pass additional filter information in the username property of the embed request when using Power BI Embedded. This isn't necessarily best practice, but some of you may find it useful in certain scenarios where you need to pass more contextual information about the data to be shown for a particular user.

More generally, I think there ought to be a generic way to pass custom information to Power BI datasets from Power BI Embedded. Some of you might be aware that there is a CUSTOMDATA() DAX function which appears to do exactly that - however, this only works for models that lie in Azure Analysis Services. To that end, I've posted an idea on the Power BI ideas site to have an equivalent function for non-AS datasets, so please vote if you agree.

Finally, as ever with RLS, you need to be aware of any performance implications of your RLS rules. For complex rules and large datasets in particular, you may run into performance issues. Ensure you test appropriately before deploying anything to production.

Otherwise, I hope you found this blog useful!