question

sudiptasen-8681 avatar image
0 Votes"
sudiptasen-8681 asked LukasYu-msft commented

mdx issue for multilevel dimension hierarchy

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.

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No, please keep it separate as this is a workaround for the other proble,m.

0 Votes 0 ·

Perhaps we could add some part of Scope function into this to make it working for higher levels.


0 Votes 0 ·