In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.
Scenario:
We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:
What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom
Solution:
1) Open Tabular Model in SSDT (SQL Server Data Tools)
2) Toolbar > Model > Roles
3) Role Manager > NEW > change Name to Europe and Permissions to Read
4) Under the Row Filters, for the Geography Table, enter the following code:
=[Country Region Name]=”France” || [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”
How to edit code for your scenario? change the [country region name] to your column(s) and also change the values
5) Click OK
6) Now let’s test it!
7) Toolbar > Model > Analyze in Excel
8) Select the role Europe
10) From Pivot Table, Select Margin & Countries:
11) As you can see, since the role Europe was selected for testing purpose in step 8 – ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!
I hope this tutorial helps you get started on implementing dynamic security in Tabular models.
Resource:
WhitePaper: Securing the Tabular BI Semantic Model
Thanks for sharing this great information,
can we give the access control only on dimension level not on aggregate level?
Is it possible?
for example:
I would like to view the grand total for all countries and view the data for specific country.