Develop and take a look at RLS Guidelines in Energy BI | by Salvatore Cagliari | Jun, 2023

Fairly often, not all Customers ought to have permission to entry all knowledge in a Report. Right here I’ll clarify how one can develop RLS Guidelines in Energy BI to configure entry and how one can take a look at them.

11 min learn

11 hours in the past

Photograph by FLY:D on Unsplash

A lot of my purchasers wish to limit entry to the info of their stories based mostly on particular guidelines.

Entry to knowledge known as Row Stage Safety (RLS briefly).

You’ll find many articles about RLS in Energy BI on Medium.

I added two of them within the References part under.

Whereas all of the articles do job of explaining the fundamentals, I all the time miss a proof on how one can develop extra complicated guidelines and how one can take a look at them simply.

On this article, I’ll clarify the fundamentals of RLS and add complexity step-by-step.

As well as, I’ll present you how one can use DAX Studio to construct queries to check RLS guidelines earlier than including them to the info mannequin.

So, right here we’re.

I exploit the State of affairs the place Customers get entry to Retail Gross sales knowledge based mostly on Shops or the geographic areas of Shops throughout the firm, together with a mix of each.

Within the Contoso knowledge mannequin, I exploit the next tables:

Tables involved in my scenario (Figure by the Author)
Determine 1 — Tables concerned in my state of affairs (Determine by the Creator)

I create the next report to check my outcomes:

Starting report (Figure by the Author)
Determine 2 — Beginning report (Determine by the Creator)

To create an RLS Rule, you could open the Safety function editor:

Open the Security role editor (Figure by the Author)
Determine 3 — Open the Safety function editor (Determine by the Creator)

Subsequent, you possibly can create a brand new Function and set the title for this Function:

Create a Role and rename it (Figure by the Author)
Determine 4 — Create a Function and rename it (Determine by the Creator)

In my case, I put the title to “StorePermissions”.

Now, I can begin including an expression to regulate entry to the Retailer desk:

Add a DAX expression to the new Role (Figure by the Author)
Determine 5 — Add a DAX expression to the brand new Function (Determine by the Creator)

We now have had a brand new, easier editor for RLS guidelines for a number of months.

In my case, I wish to add a DAX expression. So, I click on on the “Change to DAX editor” button.

At first, I add the best doable expression: TRUE()

Simplest possible RLS rule (Figure by the Author)
Determine 6 — Easiest doable RLS rule (Determine by the Creator)

To grasp RLS Guidelines, you need to know that entry is managed by the output of the expression within the RLS rule editor.

The person will get entry if the output of the expression is just not empty or FALSE().

In precept, any expression within the RLS rule editor is added as a filter to any question.
Let’s have a look at the impact of this primary expression earlier than I clarify this in additional element.

To check the Rule, I save the expression and shut the Editor.

Now I can View the report with the brand new Rule:

Test the RLS rule (Figure by the Author)
Determine 7 — Take a look at the RLS rule (Determine by the Creator)

On high of the report web page, you will note a yellow banner displaying that you’re wanting on the report utilizing the StorePermission Rule.

Because the StorePermission rule doesn’t limit entry, you’ll not see any distinction.

Let’s strive one thing totally different.

Now I modify the Expression within the RLS rule to FALSE().

Once I take a look at the Rule, I can’t see any knowledge:

Test the Rule with FALSE() (Figure by the Author)
Determine 8 — Take a look at the Rule with FALSE() (Determine by the Creator)

This proves that knowledge is accessible if the expression doesn’t return FALSE().

To grasp this impact intimately, let me present a DAX question to get the consequence with none restrictions:

,"Retail_Sales", 'All Measures'[Retail Sales]
ORDER BY Retailer[Store]

Once I add an RLS rule with TRUE(), as proven above, the question adjustments to a question just like this:

,"Retail_Sales", 'All Measures'[Retail Sales]
ORDER BY Retailer[Store]

I enclosed the question inside a FILTER() perform and added TRUE() because the filter expression.

Within the following examples, I’ll use CALCULATETABLE(), as writing the code is extra environment friendly and versatile.

Extra on this in a bit.

Subsequent, I wish to limit entry to all Shops containing the “Contoso T” String.

For this, I modify the Expression within the Rule editor to the next:

CONTAINSSTRING('Retailer'[Store], "Contoso T")

When testing the rule, I get the next consequence:

Result for restricting access to “Contoso T” Stores (Figure by the Author)
Determine 9 — Consequence for limiting entry to “Contoso T” Shops (Determine by the Creator)

It will be good to check the end result of such a rule with a DAX Question.

On this case, I exploit the next Question in DAX Studio to examine the Consequence:

,"Retail_Sales", 'All Measures'[Retail Sales]
CONTAINSSTRING('Retailer'[Store], "Contoso T") = TRUE()
ORDER BY Retailer[Store]

The interior half, with SUMMARIZECOLUMNS(), generates the output desk.

On this case, I’m solely within the checklist of the shops.

Then, I enclose the SUMMARIZECOLUMNS() name with CALCULATETABLE() so as to add a filter to the question.

On this case, I add the expression from the RLS rule, together with an “= TRUE()” examine.

The result’s the next:

Result of check query (Figure by the Author)
Determine 10 — Results of examine question (Determine by the Creator)

However what occurs below the hood?

Let’s have a look at the Storage Engine Question:

Result of the check Query (Figure by the Author)
Determine 11 — Results of the examine Question (Determine by the Creator)

And what occurs once I apply the RLS rule to this question?

I can apply an RLS rule from DAX Studio with a number of clicks:

Activate an RLS rule (Figure by the Author)
Determine 12 — Activate an RLS rule (Determine by the Creator)

The Storage Engine question is the next:

Query Analysis with the RLS Rule
Determine 13 — Question Evaluation with the RLS Rule

The primary question (Line 2) retrieves the checklist of all Shops.

The second question contains the RLS rule within the WHERE clause.

As a substitute of getting the checklist matching Shops (In accordance with the Filter), we see a cryptic line, which incorporates the RLS rule.

You’ll be able to see that the results of the Storage Engine (SE) question nonetheless incorporates 309 Rows, like above, which is the variety of all Shops + 3 rows.
A touch why now we have the discrepancy of three rows is within the textual content under the SE question: Estimated dimension: rows = 309

The precise variety of rows returned could also be certainly 306.

However this evaluation exhibits that RLS guidelines are utilized after the Storage Engine, because the question consequence incorporates solely 21 rows: All Shops which begin with “Contoso T”.

That is essential, because the Components Engine (FE), which can compute the ultimate consequence after the Storage Engine, is single-threaded and may use just one CPU Core.

Whereas the SE is multi-threaded and may use a number of CPU cores.

Consequently, we should chorus from writing inefficient code into the RLS rule.

Subsequent, I wish to mix two expressions:

  1. Solely Shops beginning with “Contoso T”
  2. Solely Shops in Europe

To attain this, I add a second expression to the Geography desk utilizing the easy editor:

Add expression to the Geography table (Figure by the Author)
Determine 14 — Add expression to the Geography desk (Determine by the Creator)

Once I swap to the DAX Editor, I get the next expression:

DAX Expression from the Simple editor (Figure by the Author)
Determine 15 — DAX Expression from the Easy editor (Determine by the Creator)

Discover the usage of the strict equal operator.

Altering to the simple equal operator on your expression could be needed.

That is the consequence when testing the rule:

Result of the combined rule (Figure by the Author)
Determine 16 — Results of the mixed rule (Determine by the Creator)

The DAX question for this rule will appear like this:

Translation to a DAX query and results (Figure by the Author)
Determine 17 — Translation to a DAX question and outcomes (Determine by the Creator)

Now, let’s add one other stage of complexity to the RLS rule:

I wish to limit entry to the Shops which both:

  • The title of the Shops begins with “Contoso T” and are in Europe
  • The title of the Shops begins with “Contoso S” and are in North America

This time, I start with the DAX question. That is the easier option to develop and take a look at the expression.

I take the primary question and enclose it with the filter expression.

As I have to filter two tables (Retailer and Geography), I need to use FILTER() and RELATED():

SUMMARIZECOLUMNS(Retailer[Store], 'Geography'[Continent])
,"Retail_Sales", 'All Measures'[Retail Sales]
,OR(CONTAINSSTRING('Retailer'[Store], "Contoso T") && RELATED(Geography[Continent]) = "Europe"
,CONTAINSSTRING('Retailer'[Store], "Contoso S") && RELATED(Geography[Continent]) = "North America")
ORDER BY [Retail Sales] DESC, 'Geography'[Continent], Retailer[Store]

I would like the RELATED() perform as I exploit FILTER() to iterate by the Retailer desk, and I would like the Continent column from the Geography desk.

Because the Geography desk is on the one aspect of the Relationship, I can use RELATED() to get the Continent column.

That is the consequence:

Query for the combined Rule (Figure by the Author)
Determine 18 — Question for the mixed Rule (Determine by the Creator)

Subsequent, we should translate this filter to an RLS rule.

For the RLS rule, we will take away the FILTER() perform, because the RLS rule inherently works as a filter.

Translation to one RLS Rule (Figure by the Author)
Determine 19 — Translation to at least one RLS Rule (Determine by the Creator)

Observe that I eliminated the expression from the “Geography” desk.

Once I take a look at this rule in Energy BI, I get the next consequence, which corresponds to the consequence from the DAX question:

Testing the combined RLS rule (Figure by the Author)
Determine 20 — Testing the mixed RLS rule (Determine by the Creator)

For testing the RLS rule, for instance, if you need solely to get the checklist of filtered shops, you possibly can write a easy question with simply the FILTER() perform:

Executing the FILTER() only (Figure by the Author)
Determine 21 — Executing the FILTER() solely (Determine by the Creator)

Till now, we checked out static RLS guidelines.

However more often than not, we want guidelines based mostly on the Consumer-Login.

To attain this, we want a desk that maps the person to the rows the person wants entry to.

For instance, a desk like this:

User List with assigned Geographies (Figure by the Author)
Determine 22 — Consumer Checklist with assigned Geographies (Determine by the Creator)

After including the desk to the info mannequin, we have to add a Relationship between the brand new desk and the “Geography” desk:

Expanded data model (Figure by the Author)
Determine 23 — Expanded knowledge mannequin (Determine by the Creator)

The connection between the brand new “Geography Entry” desk and the “Geography” desk have to be configured accurately.

After including the Relationship, Energy BI configures it as a 1:n Relationship, with the “Geography” desk on the one aspect and the Filter flowing from the “Geography” desk to “Geography Entry”.

However we wish to filter the “Geography” desk based mostly on an RLS rule (a filter) on “Geography Entry”.
Because of this, we should change the cross-filter course to each:

Settings of the Relationship (Figure by the Author)
Determine 24 — Settings of the Relationship (Determine by the Creator)

As well as, we should set the flag on “Apply safety filter in each instructions,” as Energy BI ignores the cross-filter course setting when making use of RLS guidelines.

Now we will add the RLS rule:

Configure the RLS Rule (Figure by the Author)
Determine 25 — Configure the RLS Rule (Determine by the Creator)

Keep in mind to take away any filter expression on the Retailer desk earlier than including this rule.

When testing the RLS rule, I get this:

Empty result (Figure by the Author)
Determine 26 — Empty consequence (Determine by the Creator)

To seek out out what occurs, let’s return to the RLS rule editor and alter the view for the Rule to DAX:

Wrong RLS rule (Figure by the Author)
Determine 27 — Flawed RLS rule (Determine by the Creator)

The easy RLS rule editor doesn’t acknowledge DAX features and provides them as Textual content to filter.

We should change the expression to this:

Correct DAX rule (Figure by the Author)
Determine 28 — Appropriate DAX rule (Determine by the Creator)

Now the result’s as anticipated:

Testing the RLS rule with my user and the correct RLS expression (Figure by the Author)
Determine 29 — Testing the RLS rule with my person and the right RLS expression (Determine by the Creator)

The Card on the top-left nook of the report web page incorporates a Measure with the USERPRINCIPALNAME() perform to make sure that the right person is lively throughout the take a look at.

I may even take a look at an RLS rule utilizing one other person:

Test the RLS rule with another user (Figure by the Author)
Determine 30 — Take a look at the RLS rule with one other person (Determine by the Creator)

It’s humorous that this person doesn’t have to exist. It solely must be contained within the “Geography Entry” checklist.

Right here is the results of the take a look at:

Test-Result with test user (Figure by the Author)
Determine 31 — Take a look at-Consequence with take a look at person (Determine by the Creator)

Within the yellow line on high, you possibly can see the lively person throughout the take a look at.

I confirmed you how one can create elementary RLS guidelines and how one can take a look at them.

Then I added extra complexity and analyzed the consequences of RLS guidelines on the underlying Storage engine.

We now have seen that the Components Engine processes a part of the RLS rule. Subsequently, we should write environment friendly code within the RLS guidelines.

Understanding how one can take a look at RLS guidelines earlier than implementing them within the knowledge mannequin is essential.

It’s a lot simpler to know unsuitable outcomes by understanding how the rule is utilized to the info mannequin.

Lastly, I added dynamic user-based RLS guidelines to the mannequin.

These guidelines are harder to check in a DAX question, as you need to know which knowledge every person can entry to jot down the right take a look at question to validate the consequence.

I hope I’ve given you some hints on simplifying your life with the RLS characteristic in Energy BI.

Photograph by Andrew George on Unsplash

You’ll find a listing of Safety features in Energy BI on this Article:

You’ll find a easy clarification about Row Stage Safety in Energy BI on the Energy BI (Now Material) Group web page: Row-level security (RLS) with Power BI — Power BI | Microsoft Learn.

I can suggest this text by Nikola Ilic, the place you may get a place to begin about RLS:

One other good introductory article on Row-Stage-Safety in Energy BI by Elias Nordlinder:

Go to my Tales Checklist for extra data about the FILTER() function and how one can analyze DAX Query with DAX Studio.

I exploit the Contoso pattern dataset, like in my earlier articles. You’ll be able to obtain the ContosoRetailDW Dataset free of charge from Microsoft here.

The Contoso Knowledge could be freely used below the MIT License, as described here.

Matplotlib Tricks to Immediately Enhance Your Information Visualizations — In line with “Storytelling with Information” | by Leonie Monigatti | Jun, 2023

Dynamically Rewired Delayed Message Passing GNNs | by Michael Bronstein | Jun, 2023