THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

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.

Published Thursday, February 05, 2009 12:18 PM by AlbertoFerrari

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

mosha said:

> The physical space of the cube is grown

The real physical space of the cube as measured by size on the disk won't grow, because when physical measure is a constant it will be compressed to 0 bits.

Mosha.

February 5, 2009 10:05 AM
 

Jan Morten said:

Hi

I have implemented cube security on calculated measures almost as described above, but I have a problem with index calculations.

I have implementet YTD calculations with a utility dimension instead of creating a YTD measure for each measure type.

When SSAS is calculating YTD for a index it accumulates the YTD index value instead of calculated value.

Is this i common problem? Any ideas on how to solve it?

Regards Jan Morten

September 7, 2009 10:02 AM
 

Anne Rocco said:

Based on the solution outlined, I was able to take the calculated measures and have them appear under the dimension data security tab as physical measures I could hide/unhide.  However, when I would select/uncheck one of these measures as unavailable for a particular Role, any users within that Role accessing the cube would receive a script error:

"The member '[<<CalculatedMemberName>>]' was not found in the cube when the string, [<<DimensionName>>].[<<CalculatedMemberName>>], was parsed." (fyi, I am using ProClarity as the analytic tool).  

If a Measure was unselected for a particulare Role, the SCOPE statement would not recognize the Measure (in the example above Measures.GrossProfit).

To get around the issue, I created a dimension (Dim.Roles) and added Members for each of the Roles within the cube.  For each Role, I configured the dimension data security to only read the member applicable to that role (hence created a one-to-one mapping).  Then, I changed the SCOPE statement to only execute if the one Member was one of the Roles that did have access to that specific Measure:

SCOPE (IIF(MEMBERTOSTR([Dim.Roles].[RoleName].MEMBERS.Item(0)) = "[Dim.Roles].[RoleName].&[GeneralUser]", STRTOMEMBER('[Measures].[GrossProfit]'),{}));

   THIS = [Measures].[Sales Amount] - [Measures].[Total Product Cost];

END SCOPE;

As you can imagine, this is not the most elegant of solutions and creates overhead for extensibility of Roles within SSAS.  Has anyone seen this before and/or have an alternative approach?

Thanks.

September 25, 2009 12:37 AM
 

AlbertoFerrari said:

Anne,

Another, not very elegant, solution to this problem is to configure the cube to ignore MDX errors during the MDX script evaluation.

It is not very elegant but, in a production environment, it will work fine. Clearly, during the cube development, it is mandatory to enable error checking to verify the code you are writing.

Alberto

September 25, 2009 3:24 AM
 

Shom said:

In 2008 (not sure about 2005), you can apply security on calculated measure also. In role-> Dimension Data Security-> Measure Dimension -> Advance Tab-> Denied Set -> provide all measures {[Measures].A, [Measure].B} which are to be denied access under denied set.

April 30, 2010 2:31 AM
 

Shubh said:

Hi ,

Pls advice, after making the calculated member as physical, as given in the post, how can i apply dynamic security on the measures based on users.

Like -

User 1 can see Measure 1,2,3

User 2 can see Measure 2,5,7

and so on...

Regards,

Shubh

August 13, 2011 4:40 PM
 

Anthony said:

This would work fine if your cube were based on actual physical database tables...but what if your entire DSV consists of named queries???

October 10, 2012 3:57 PM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement