Fairly often, not all Customers ought to have permission to entry all information in a Report. Right here I’ll clarify learn how to develop RLS Guidelines in Energy BI to configure entry and learn how to take a look at them.
Lots of my shoppers wish to limit entry to the information of their stories primarily based on particular guidelines.
Entry to information known as Row Degree Safety (RLS briefly).
You will discover 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 a great job of explaining the fundamentals, I at all times miss a proof on learn how to develop extra complicated guidelines and learn how to 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 learn how to use DAX Studio to construct queries to check RLS guidelines earlier than including them to the information mannequin.
So, right here we’re.
I take advantage of the Situation the place Customers get entry to Retail Gross sales information primarily based on Shops or the geographic places of Shops inside the firm, together with a mix of each.
Within the Contoso information mannequin, I take advantage of the next tables:
I create the next report to check my outcomes:
To create an RLS Rule, you want to open the Safety position editor:
Subsequent, you may create a brand new Function and set the identify for this Function:
In my case, I put the identify to “StorePermissions”.
Now, I can begin including an expression to regulate entry to the Retailer desk:
We’ve had a brand new, easier editor for RLS guidelines for a couple of months.
In my case, I wish to add a DAX expression. So, I click on on the “Swap to DAX editor” button.
At first, I add the only potential expression: TRUE()
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 take 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:
On prime of the report web page, you will note a yellow banner exhibiting that you’re trying on the report utilizing the StorePermission Rule.
Because the StorePermission rule doesn’t limit entry, you’ll not see any distinction.
Let’s attempt one thing completely 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 information:
This proves that information is accessible if the expression doesn’t return FALSE().
To grasp this impact intimately, let me present a DAX question to get the end result with none restrictions:
EVALUATE
SUMMARIZECOLUMNS(
Retailer[Store]
,"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 much like this:
EVALUATE
FILTER(
SUMMARIZECOLUMNS(
Retailer[Store]
,"Retail_Sales", 'All Measures'[Retail Sales]
)
,TRUE()
)
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 end result:
It might be good to check the result of such a rule with a DAX Question.
On this case, I take advantage of the next Question in DAX Studio to test the Consequence:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
Retailer[Store]
,"Retail_Sales", 'All Measures'[Retail Sales]
)
CONTAINSSTRING('Retailer'[Store], "Contoso T") = TRUE()
)
ORDER BY Retailer[Store]
The inside half, with SUMMARIZECOLUMNS(), generates the output desk.
On this case, I’m solely within the listing 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()” test.
The result’s the next:
However what occurs below the hood?
Let’s take a look at the Storage Engine Question:
And what occurs after I apply the RLS rule to this question?
I can apply an RLS rule from DAX Studio with a couple of clicks:
The Storage Engine question is the next:
The primary question (Line 2) retrieves the listing of all Shops.
The second question consists of the RLS rule within the WHERE clause.
As a substitute of getting the listing matching Shops (In line with the Filter), we see a cryptic line, which incorporates the RLS rule.
You possibly can see that the results of the Storage Engine (SE) question nonetheless accommodates 309 Rows, like above, which is the variety of all Shops + 3 rows.
A touch why we now 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 reveals that RLS guidelines are utilized after the Storage Engine, because the question end result accommodates solely 21 rows: All Shops which begin with “Contoso T”.
That is vital, because the Formulation Engine (FE), which is able to compute the ultimate end result after the Storage Engine, is single-threaded and might use just one CPU Core.
Whereas the SE is multi-threaded and might 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:
- Solely Shops beginning with “Contoso T”
- Solely Shops in Europe
To realize this, I add a second expression to the Geography desk utilizing the straightforward editor:
Once I change to the DAX Editor, I get the next expression:
Discover using the strict equal operator.
Altering to the straightforward equal operator in your expression will be vital.
That is the end result when testing the rule:
The DAX question for this rule will seem like this:
Now, let’s add one other stage of complexity to the RLS rule:
I wish to limit entry to the Shops which both:
- The identify of the Shops begins with “Contoso T” and are in Europe
or - The identify of the Shops begins with “Contoso S” and are in North America
This time, I start with the DAX question. That is the easier strategy to develop and take a look at the expression.
I take the primary question and enclose it with the filter expression.
As I must filter two tables (Retailer and Geography), I have to use FILTER() and RELATED():
EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZECOLUMNS(Retailer[Store], 'Geography'[Continent])
,"Retail_Sales", 'All Measures'[Retail Sales]
)
,FILTER(Retailer
,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 take advantage of FILTER() to iterate by way of 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 end result:
Subsequent, we should translate this filter to an RLS rule.
For the RLS rule, we are able to take away the FILTER() perform, because the RLS rule inherently works as a filter.
Notice that I eliminated the expression from the “Geography” desk.
Once I take a look at this rule in Energy BI, I get the next end result, which corresponds to the end result from the DAX question:
For testing the RLS rule, for instance, whenever you need solely to get the listing of filtered shops, you may write a easy question with simply the FILTER() perform:
Till now, we checked out static RLS guidelines.
However more often than not, we want guidelines primarily based on the Person-Login.
To realize this, we want a desk that maps the person to the rows the person wants entry to.
For instance, a desk like this:
After including the desk to the information mannequin, we have to add a Relationship between the brand new desk and the “Geography” desk:
The connection between the brand new “Geography Entry” desk and the “Geography” desk have to be configured appropriately.
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 primarily based on an RLS rule (a filter) on “Geography Entry”.
Because of this, we should change the cross-filter course to each:
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 are able to add the RLS rule:
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:
To seek out out what occurs, let’s return to the RLS rule editor and alter the view for the Rule to DAX:
The straightforward RLS rule editor doesn’t acknowledge DAX features and provides them as Textual content to filter.
We should change the expression to this:
Now the result’s as anticipated:
The Card on the top-left nook of the report web page accommodates a Measure with the USERPRINCIPALNAME() perform to make sure that the right person is lively in the course of the take a look at.
I may even take a look at an RLS rule utilizing one other person:
It’s humorous that this person doesn’t must exist. It solely must be contained within the “Geography Entry” listing.
Right here is the results of the take a look at:
Within the yellow line on prime, you may see the lively person in the course of the take a look at.
I confirmed you learn how to create elementary RLS guidelines and learn how to take a look at them.
Then I added extra complexity and analyzed the consequences of RLS guidelines on the underlying Storage engine.
We’ve seen that the Formulation Engine processes a part of the RLS rule. Subsequently, we should write environment friendly code within the RLS guidelines.
Figuring out learn how to take a look at RLS guidelines earlier than implementing them within the information mannequin is essential.
It’s a lot simpler to know incorrect outcomes by understanding how the rule is utilized to the information mannequin.
Lastly, I added dynamic user-based RLS guidelines to the mannequin.
These guidelines are tougher to check in a DAX question, as you need to know which information every person can entry to write down the right take a look at question to validate the end result.
I hope I’ve given you some hints on simplifying your life with the RLS function in Energy BI.
You will discover a listing of Safety features in Energy BI on this Article:
You will discover a easy rationalization about Row Degree Safety in Energy BI on the Energy BI (Now Material) Group web page: Row-level safety (RLS) with Energy BI — Energy BI | Microsoft Be taught.
I can advocate this text by Nikola Ilic, the place you will get a place to begin about RLS:
One other good introductory article on Row-Degree-Safety in Energy BI by Elias Nordlinder:
Go to my Tales Listing for extra details about the FILTER() perform and learn how to analyze DAX Question with DAX Studio.
I take advantage of the Contoso pattern dataset, like in my earlier articles. You possibly can obtain the ContosoRetailDW Dataset free of charge from Microsoft right here.
The Contoso Information will be freely used below the MIT License, as described right here.
