Applying Security to Calculated Measures

Tired of not being able to secure your Calculate Measures through SSAS? 

One of the biggest issues facing Analysis Services Developers these days is applying security to calculated measures through roles. The problem is with the default behavior exhibited by Analysis Services. Currently if you follow the following steps, you cannot apply security to a calculated measure through a typical role setup.

Typical Calculated Measure Creation

Step 1 – Create a new calculated Measure for Profit

This measure does not need any special settings, nor would those help our issue.

Step 2 – Deploy the Cube and Verify the Calculation is working

The calculated measure is calculating correctly, (Simple calc so we didn’t expect a problem).

Step 3 – Create a new Role and try to add security to this measure

This won’t work no matter how many times we try it. Calculated Measures cannot be secured by typical security as SSAS is designed today. There is a workaround however that we can implement.

It’s best to implement this before you begin creating your calculated members, since this will change the way they are all created. I am now going to walk you through the process of creating a calculated measure that will get around this limitation. The way we are going to do that is relatively simple, but must be followed step by step.

Step 1 – Create a new Measure either as a named calculation in the DSV or in the relational source and add it to the DSV. We will set it with a default value of 0.

Step 2 – We will now add this to the cube as a new measure.

Once it is added, we need to go configure the actual calculation. Now where will we do this at? We are going to use a SCOPE statement and control the context of the measure to set it equal to the calculation we desire. To do this, go to the calculations tab and create a new Script Statement.

Step 3 – Create a new Script Statement on Calculations Tab

The SCOPE statement is very effective for controlling a large number of context related issues and is used for anything from security to modifying other calculations and their solve order. For this use, it’s main job is to overwrite the value of 0 we placed in our Named Calculation, and replace it with the actual calculated value.

I know this seems like a long way around, but it is the most straightforward way to implement this security. WAIT we haven’t done the security yet. Go ahead and deploy the new cube with the SCOPE statement so we can work with it.

Step 4- Create the Role again and see if you get profit in your list to secure.

Now it’s there! Great Job!

You’re now ready to begin adding more measures in the same fashion. This will become a quick and easy process once you get the hang of it. Also you will be able to do a few other things with this statement.