Getting Started: Implementing Dynamic Security with row filters in Tabular Models
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
9) Click ok.
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.