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:

SecurityProj_Data02

SecurityProj_Data02

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

SecurityProj_Data04

 

Fact data:

SecurityProj_Data03

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!

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s