dimension security on a combination of dimensions

sudipta sen 1 Reputation point
2020-08-05T12:47:44.07+00:00

0

I have 2 dimension tables scenario and compensation. scenario already has dimension security applied. Now we need to implement dimension security for compensation. Compensation is dependent on scenario. For example we have a separate table which has many to many relationship between compensation and scenario. Now the compensation security will also be implemented based on the scenario security of the user. Can anyone please guide me on how to implement it.

We have the following tables :'

Compensation dimension table :

Compensation Id, Name, etc....

Scenario dimension table :

Scenario Id, Name, etc

Compensation Fact Table and Scenario Fact Table :

One more table in DB : CompensationReport Table (scenario id, compensation id, etc....) n*m relationship.
This table has scenario,compensation combination.

User Table

Scenario already has a user scenario mapping table (dimension security already exist)

I also have a scenario, compensation, user mapping table.
So user should have access only to the combination .

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,245 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-08-06T07:15:47.493+00:00

    Hi,
    You cube should be a SSAS multidimensional cube right?
    I have read your post, I think for this snowflake schema there is not much direct short cut we could use to relate security table and the two dimensions.
    I think you could use help from this link: protect dimension members

    Regards,
    Lukas

    0 comments
  2. Alexei Stoyanovsky 3,416 Reputation points
    2020-08-18T07:25:54.877+00:00

    The first thing to check is whether separate security on the two dimensions satisfies your requirements. Separate security means that for a given user, there's a list of allowed Compensation members, a list of allowed Scenario members, and the user is able to see all data in the cube that belongs to any combination of the two allowed lists. If this works for you, you just secure Compensation in the same way you have Scenario secured and you're good to go.
    Now if it doesn't, that is you have to secure specific combinations of Scenario and Compensation, you'll have to create a single dimension for the pair. It doesn't have to be visible to the end users, but this will be the one securing your combinations - and you'll still probably have to secure Scenario and Compensation individually so the users don't get distracted by items that are not relevant to them.

    0 comments
  3. Lukas Yu -MSFT 5,816 Reputation points
    2020-08-27T01:43:13.493+00:00

    Hi,
    Did you resolve this issue, we are happy to know your progress or feedback! :)

    0 comments