mdx issue for multilevel dimension hierarchy

Question

I have a requirement where i had to apply dimension security to one of the dimensions "CompensationItem". But the problem was it has dependency on another dimension "Scenario". We have a mapping table/measure (COMP SECURITY) with compensationItem,Scenario and User columns which should determine whether "CompensationItem" is accessible or not for the scenario user combination. It seems product does not support this so we decided to use MDX for the same.

we have written the below MDX query for this:

Select {Exists([CompensationItem].[CompensationItemLevel2].[CompensationItemLevel2].Members, "[User].&[1]", "COMP SECURITY")} On Rows, {{[Time].[All Time]}} Dimension Properties [Parent_Unique_Name] On Columns From Workforce Where ([Scenario].&[1])

This returns proper result based on user and scenario filter when I use the Exists condition.

But the problem is CompensationItem has 3 levels. And COMP SECURITY table will have mapping for only the least nodes/last level. So if I use level1/level2 in the MDX query, the filter does not work properly and the aggregate total gives results for all the leaf nodes even if it is not secure/mapped to that scenario.

The below query I have used for level2 which does not give desired resullts:

Select {Exists([CompensationItem].[CompensationItemGroupNameLevel].&[GroupName], "[User].&[1]", "COMP SECURITY")} On Rows, {{[Time].[All Time]}} Dimension Properties [Parent_Unique_Name] On Columns From Workforce Where ([Scenario].&[1])

Can someone please guide on how to fix/rewrite this query to get the desired output.

Answer 1

Hi ,
Is this the case that follows dimension security on a dependent dimension? Could we sum the casess together?