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:
The solution found consists of the next parts:
1. The HierarchySecurity table should not be linked to any other table:
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.
3. All calls to the related tables should be wrapped with functions putting them in user- and node-specific context:
Works like a chum!