dimension security based on hierarchy node

Antoine Krebs 101 Reputation points
2020-10-02T13:45:09.343+00:00

i´m trying to switch from standard permission handling based on leave level (dedicated cost center) like this

NonEmpty (
[cost center].[cost center].Members,
(
StrToMember ("[user].[user].[" + UserName () + "]"),
[Measures].[CostCenterGranted]
)
)

to a usage of cost center hierarchy.
Example
Level 1 Expenses World
Level 2 Expenses Europe
Level 3 Expenses Germany
Level 3 Expenses France
Level 4 Expenses CostCenter 1 - Germany
Level 4 Expenses CostCenter 2 - Germany
Level 4 Expenses CostCenter 3 - France
...
Instead of define per user per costcenter an entry in the bridge table, i want to have only one entry for example cost center node Germany.
This should grant access to all CostCenter below Node Germany
I tried something like this

NonEmpty (
descendants(
[cost center].[cost center group].Members ,,leaves ),
(
StrToMember ("[user].[user].[" + UserName () + "]"),
[Measures].[CostCenterGranted]
)
)

But this didn´t worked.
I do appreciate any ideas.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,260 questions
0 comments
Accepted answer
  1. Antoine Krebs 101 Reputation points
    2020-10-07T10:47:11.997+00:00

    Solution: I found it in my book "Expert Cube Development with SSAS Multidimensional Models"

    Generate (
    NonEmpty (
    [costcenter].[costcenter].[costcenter].Members ,
    (
    [Measures].[CostCenterGranted],
    StrToMember ("[user].[user].[" + UserName () + "]")
    )
    ),
    , {
    LinkMember (
    costcenter.costcenter.CurrentMember,
    costcenter.costcenters
    )
    }
    )

    Thanks

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Darren Gosbell 2,376 Reputation points
    2020-10-05T00:41:51.697+00:00

    You'd need to wrap the descendants() call around the outside of the existing NonEmpty if you want to only grant permissions as the top level and have the permissions flow down from there.

    eg

    descendants(
    NonEmpty (

    [cost center].[cost center group].Members,
    (
    StrToMember ("[user].[user].[" + UserName () + "]"),
    [Measures].[CostCenterGranted]
    )
    )
    ,,leaves )

    1 person found this answer helpful.
    0 comments
  2. Antoine Krebs 101 Reputation points
    2020-10-07T08:29:19.937+00:00

    thank you for the input, but unfortunately it´s not working
    this should be the outcome

    30594-image.png

    when i´m using
    descendants(
    NonEmpty (

    [cost center].[cost center].Members,
    (
    StrToMember ("[user].[user].[" + UserName () + "]"),
    [Measures].[CostCenterGranted]
    )
    )
    ,,leaves )

    i get too much values
    30605-image.png

    The costcenter hierarchy is a parent child hierarchy and its connected via costcenter_id to the bridge table bridgeUserCostCenter.
    My Testuser only has 1 row in the bridge tabel with his username and the costcenter node "PD".

    Maybe i have to use linkedmember or something like that.

    0 comments