The goal of this post is to help you get started with implementing dynamic security with row filters in Tabular Models (BI Semantic Model).
Scenario:
Business users connect to a Tabular Model via Excel for Data Analysis purposes. One of their common task is to analyze Countries vs. Total Margin:
But the business requirements dictate that the data should be restricted by the Sales region to which they belong. For example, a Sales Manager from Europe can see data about France, Germany and United Kingdom. To meet this requirement, you’ll have to implement Dynamic Security in Tabular Models.
Here’s how you get started:
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
Now let’s test this role:
6) Toolbar > Model > Analyze in Excel
7) Select the role Europe
8) Click OK
9) From Pivot Table, select Margin & Countries:
10) As you can see, since the role Europe was selected for testing purpose in step 7 – ONLY France, Germany and UK data is shown in our test! This means that our row filters are working.
11) You’ll have to setup more roles (based on your security requirements) as shown in steps 1-4 of this post. And after you’ve setup roles, you can add windows users to these roles.
Note: Do not use perspectives as security mechanisms because that’s not what they are meant for.
I hope this tutorial helps you get started on implementing dynamic security in Tabular models.
