Row- and role-based security model in Tabular modelling



The goal is to control access level basing on a hierarchy on per-node basis. Another restrain is that each user may need access to multiple nodes (this complication impedes from using of DAX-function LOOKUPVALUE).

Natural hierarchy consists of cost centres:



Security table consisting of only two columns holds links between user logins and node codes:



Fact data:


The solution found consists of the next parts:

1. The HierarchySecurity table should not be linked to any other table:

Tabular model

2. In the dimension table should be created a measure that for particular security and cost centre context returns 1 or nothing depending on user’s permissions set in the “HierarchySecurity” table.

The authorisation measure

3. All calls to the related tables should be wrapped with functions putting them in user- and node-specific context:

Test script


Works like a chum!


About fdtki

Sr. BI Developer | An accomplished, quality-driven IT professional with over 16 years of experience in design, development and implementation of business requirements as a Microsoft SQL Server 6.5-2014 | Tabular/DAX | SSAS/MDX | Certified Tableau designer
This entry was posted in programming and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s