Search code examples
securityssasparent-childmdxolap

Aggregation - SSAS Dynamic Security over parent-child Hierarchy

I would like build the following SSAS Security situation: enter image description here

I created Role to filter the data accordingly and user1 can only see London 100 and from here the aggregation to UK also 100. I have also a FactlessDimension that relates the DimUsers and DimBranch into a many-to-many relationship

The request is that user1, can see only London 100 (just like before) and instead of UK-100 he needs to see the all UK (150). Would be that possible to implement in SSAS?

Solution

  • Right, apologies for not noticing that your Branch dimension is parent-child - even though that was in the question title!

    I've set up a test cube mirroring your setup (as far as I can understand it). I'm ignoring that stupid error "Dimension Users could not be found" I get when I set the custom MDX role security; and ignoring the fact that in the SSDT (aka Visual Studio) Cube Browser, this custom security doesn't work. Let's just put that down to the SSDT UI being a POS - because in an SSMS MDX query, I get what I expect.

    Here's my Branches table:

    BranchID    ParentID    BranchName
1           NULL        Scotland
2           NULL        England
3           1           Glasgow
4           1           Edinburgh
5           2           Bristol
6           2           London

    There are two hierarchies on the Branch dimension: one called BranchID (simply on the attribute BranchID), and one called ParentChild, which is the parent-child hierarchy using ParentID.

    I restrict access to the Branch.ParentChild hierarchy for a "restricted" user by using this expression in the Allowed Member Set box:

    NONEMPTY(Branches.[ParentChild].members,  
(Measures.UserBranchAccess,StrToMember("[Users].[" + UserName + "]")))

    So now I give access to all branches to a "restricted access" user by populating a table I call UserBranchAccess. (The resulting COUNT measure I call UserBranchAccess). I then remove access to Bristol by deleting the corresponding row.

    Running this query in SSMS with EffectiveUserName set to the "restricted user", I get exactly the result you were looking for:

    SELECT
NONEMPTY(Branches.[ParentChild].members,   
(Measures.UserBranchAccess,StrToMember("[Users].[" + UserName + "]"))) 
ON 0
FROM [Test Dimensions]

    Result:

    All England London  Scotland    Edinburgh   Glasgow
700 550     50      150         50          100

    (my default measure called Branch Total is displayed here - note that Bristol's 500 is not shown, but is included in the England total).

    I then tried removing access to both Bristol and England for the "restricted" user. I get exactly the same result.

    Let's see what's happening to the "security" measure UserBranchAccess:

    SELECT
Branches.[ParentChild].members ON 0
FROM [Test Dimensions]
WHERE
(UserBranchAccess,Users.[User ID].[RestrictedUser])

    result:

    All England Bristol London  
4   1       (null)  1

    (Scotland not shown for clarity).

    England is being allowed, because at least one of its children is allowed.

    Just to check that I didn't miss out any processing, I did the same query but on the (non parent-child) hierarchy of the Branch dimension:

    SELECT Branches.[BranchID].Members ON 0
FROM [Test Dimensions]
WHERE
(UserBranchAccess,Users.[User ID].[EMEA\martinhe])

    Result is as expected: England is not explicitly allowed in the normal BranchID hierarchy, but is allowed (see previous result) in the parent-child hierarchy:

    All Bristol England London
4   (null)  (null)  1

    (again, Scotland not shown for clarity).

    So the bottom line is that I can't reproduce the problem you're having, whereby (using my figures) London would show 50 and England would also only show 50.

    But perhaps you can identify how your setup differs from mine?