SSAS OLAP Cube Dynamic Security. Many dimensions in one role

Question

After setting a cube I was asked to add dynamic security with use of table of users and data they can see.

The problem is that i have to take into account 3 different dimensions. I've decided to use the fact table with noneEmpty function on count.

NonEmpty([Dimension].[Hierarchy].members,
    ([Measures].[Allowed Count],
     [Users].[User].&[UserName]
     )
)

After setting role I've got result like:

Dim1 | Dim2 | Dim3

1 | A | 300

1 | A | 320

1 | A | 340

1 | B | 300

1 | B | 320

1 | B | 340

Where it should be:

Dim1 | Dim2 | Dim3

1 | A | 300

1 | A | 320

1 | B | 340

Data for allowed user access are stored in table like

UserName | Dim1Key | Dim2Key | Dim3Key

Hierarchy is like Each Dim1 contains each type of Dim2 that contains each type of Dim3.

And user can only access given member of Dim3 in Dim2 in Dim1.

Is there a way to connect this dimensions in MDX so each Dim in the end has only its respective values

UPDATE:

After some research I've got this query:

SELECT [Measures].[CC Count] ON 0,

NonEmpty(
(
NonEmpty((Dim1.children),
    ([Measures].[CC Count],
    [Users].[User].&[userName]
    ))
    ,
NonEmpty((Dim2.children),
    ([Measures].[CC Count],
    [Users].[User].&[userName]
    )), 
NonEmpty((Dim3.children),
    ([Measures].[CC Count],
    [Users].[User].&[userName]
    ))
    )
    ,([Measures].[CC Count],
    [Users].[User].&[userName]
    ))
ON 1
FROM [Cost Center]

That gives me wanted results, but I can't place it into Dimensiom Data in Role. Is there a way to change it?

GregGalloway GregGalloway · Accepted Answer · 2016-09-27T07:22:54

Please try creating a new hidden dimension where the key attribute has a composite key of key1, key2 and key3. You will have to pick some NameColumn but it doesn't matter. So pick key1 as the name. You don't need anything on the dimension except the dimension key.

In the Dimension Usage of your cube designer make sure this new dimension is joined to all fact tables and to the security measure group which provided the CC Count measure.

Then create role based security just on that dimension. The users will be able to see all members of all dimensions but this new composite key dimension will ensure they can't see fact rows they are not supposed to. And this should perform much better than the alternative which is cell security.

SSAS OLAP Cube Dynamic Security. Many dimensions in one role

1 Answers