Measure Tool – Making SSAS measures physical to apply security

The security model of SSAS prevents us to apply security limitations to calculated members. We can hide physical members but we have no means to hide calculated members to any role. By preventing access to the physical measures, the user will not be able to see the result of the calculated measure, nevertheless he will always see the metadata definition of the member and, if he tries to add it to a query, he will get an error as a result.

Even if this approach is effective, it leads to a very poor user experience: it would be much better to completely hide the calculated measure to any user who does not have access to it.

A solution to this situation might be found by fooling SSAS: if we create an empty physical measure and then use MDX code to override its value with our MDX calculation, then we will be able to apply security to the physical measure and still have the ability to define its value using a calculated member.

An example is much easier to understand that any theoretical explanation: let us suppose that we want to make the Gross Profit calculated measure of Adventure Works a physical one.

Adapting in some way the technique showed in this post by Marco Russo, we might be tempted to create a measure group containing an empty physical measure (GrossProfit), without linking the measure group to any dimension. Sadly to say, SSAS will refuse to deploy a cube that contains a measure group not related to any dimension. So we will need to create a physical measure group linked to at least one dimension in order to make this technique works.

We can define a new view:

CREATE VIEW MeasureTool AS      SELECT          GrossProfit = CAST (0 AS REAL),         TimeKey      FROM         DimTime

And then add the view to our DSV and create a measure group based on this view, relating it to the time dimension through the TimeKey. Note that we choose the DimTime just because it is a small dimension, any very small dimension will work as we are not using this relationship in any way, it is only a technicism to make the OLAP cube work.

If we deploy the cube at this point, the new pjysical measure will always contain zero, which is not what we want. Nevertheless, a very simple MDX SCOPE will solve the problem and perform the magic:

SCOPE (Measures.GrossProfit);     THIS = [Measures].[Sales Amount] - [Measures].[Total Product Cost]; END SCOPE;

Now the GrossProfit measure will perform the correct computation but it will be a physical measure, so we can apply security on it and hide it to all the users that are not allowed to see it.

The physical space of the cube is grown but, using a small dimension, the growth will be negligible.