Setting the role for a calculated dimension (SSAS Cube)

Question

The cube (sql Server 2016) contains dimensions [Unit].[Unit].&[1] and [Unit].[Unit].&[2]. I’ve created a calculated dimension:

CREATE MEMBER CURRENTCUBE.[Unit].[Unit].[All].[1/2] AS [Unit].[Unit].&[1]/[Unit].[Unit].&[2]

To restrict access to the dimension of [Unit].[Unit].[All].[1/2] I’ve created the Role. But I can not select [Unit].[Unit].[All].[1/2] in DimensionData-Basic tab, because this attribute does not appear in the list of attributes. If I specify the attribute [Unit].[Unit].[All].[1/2] in DimensionData-Advanced tab in the MDX-script Denied member set: {[Unit].[Unit].[All].[1/2]}, the user sees this attribute. How do I restrict access to the calculated dimension?

GregGalloway GregGalloway · Accepted Answer · 2016-12-17T13:36:21

If you change your DimUnit SQL table to include a "1/2" row then replace your CREATE MEMBER statement with the following it should work the same and you should be able to see this member in dimension data security after you deploy and process the cube:

([Unit].[Unit].[1/2]) 
     = DIVIDE([Unit].[Unit].&[1], [Unit].[Unit].&[2]);

Then after you have created a role that makes the 1/2 member disappear you will get an error when browsing the cube with that user. Change the above line to the following:

(IIf(IsError(StrToMember("[Unit].[Unit].[1/2]",CONSTRAINED)),Head([Unit].[Unit].Members,0),StrToMember("[Unit].[Unit].[1/2]",CONSTRAINED))
    = DIVIDE([Unit].[Unit].&[1], [Unit].[Unit].&[2]);

BTW, DIVIDE does safe divide so divide by zero won't cause problems.

Off the top of my head I can't recall if StrToMember is necessary vs. just a member reference so I played it safe. Feel free to try it without StrToMember.

To be clear the whole script should say:

CALCULATE;

(IIf(IsError(StrToMember("[Unit].[Unit].[1/2]",CONSTRAINED)),Head([Unit].[Unit].Members,0),StrToMember("[Unit].[Unit].[1/2]",CONSTRAINED))
    = DIVIDE([Unit].[Unit].&[1], [Unit].[Unit].&[2]);

Setting the role for a calculated dimension (SSAS Cube)

1 Answers