THE SQL Server Blog Spot on the Web

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

Alberto Ferrari

DrillThrough Actions and (semi) Security in SSAS OLAP cubes

Using SSAS 2005/2008, there is no way to apply security to DRILLTHROUGH actions, so we cannot decide whether a specific user is authorized or not to perform a specific action. This is a “by design” behavior, since DRILLTHROUGH actions are initiated at the client side, we can decide whether a user can perform or not DRILLTHROUGH but, once we let him do it, he can query anything he wants.

Nevertheless, if we are not concerned with security but only with user experience, there is a simple trick to define which users can see which actions. Leveraging the Condition box, we can write an MDX condition that makes use of the UserName function (which returns the currently connected user) and decide to activate the action for only specific users, like in this action, that will be active only for “SQLBI\Alberto”:


Clearly, since the condition can be any valid MDX expression, we can implement some more data driven logic creating a measure group that stores action names and users, in order not to hard-code the user name inside the action.

What should be very clear is that this workaround is intended to make the user experience with the OLAP cube a better one (actions are activated for only the users that want to use them) but has nothing at all to do with security. If we enable a user to initiate DRILLTHROUGH actions he will always be able to query for anything. That said, if our customer uses just Excel or a similar client and there are no serious security issues, then this solution might help him navigate the cube and activate only the required actions.

Published Wednesday, June 24, 2009 6:52 PM by AlbertoFerrari
Filed under: ,



Anonymous said:

September 15, 2011 11:50 AM

tom rawley said:

To say that there is no way of applying security to drill through actions simply isnt true. If you create a drill through dimension you can apply all the security you want on it through the dimension data tab in the role properties. A drill through dimension is simply a dimension created from the fact table containing all of the attributes that you want returned in your drill through. Whether you are returning the record level data using an action or a drill through action the security model is applicable. You can filter what a user sees by scripts like this to the denied members box:

EXISTS([Dim_SLAMFACT_Drill].[NHSNumber].[NHSNumber],{Except([Dim_SLAMFACT_Drill].[PracticeCode].Children, {[Dim_SLAMFACT_Drill].[PracticeCode].&[F83002]})})

Obviously this requires a dimension that demarks the users, in this case PracticeCode. If you dont have a dimension that demarks the users, simply create one. You may need to make changes to your schemas to add fields in that flag records for the users they are intended for.

May 4, 2012 11:00 AM

tom rawley said:

You could then have an action that looked like this:


[$Dim_SLAMFACT_Drill].[NHSNumber] as [NHSnumber],

[$Dim_SLAMFACT_Drill].[ActivityType] as [Activity Type],

[$Dim_SLAMFACT_Drill].[PracticeCode] as [GP Practice],

[$Dim_SLAMFACT_Drill].[Diagnosis] as [Diagnosis],

[$Dim_SLAMFACT_Drill].[PODCode] as [Point of Delivery],

[$Dim_SLAMFACT_Drill].[TreatmentorProcedure] as [Treatment or Procedure],

[$Measures].[Actual Cost] as [Cost]"

May 4, 2012 11:03 AM

AlbertoFerrari said:


I have not said that you cannot secure the data, I said that you cannot secure a specific drillthrough action. Once you give the user the permission to drillthrough, he will be able to execute drillthrough and, obviously, he will see all the data which is available to him, following standard security. Thus, data is secured, actions are not. Actions can be hidden, but this is just UI, not security.

My tip was just intended to avoid showing among the available actions for a specific user the ones you don't want him to look at

May 4, 2012 11:14 AM

tom rawley said:

Apologies. I didnt realise that you were saying that you 'cannot secure a specific drillthrough action'. It was more the 'once we let him do it, he can query anything he wants.' that I was disagreeing with though. You can restrict indiviual users to seeing exactly what you want them to see user by user on a single action or drill through action.

Anyway the 'MDX condition that makes use of the UserName function ' element of the blog is useful. Not something I have used but I am going to try it out. I am assuming that 'SQLBI\Alberto' refers to an active directory account?

May 4, 2012 11:42 AM

AlbertoFerrari said:

Yep, USERNAME() returns the name of the user currently connected with the cube, in the form domain\username

May 4, 2012 11:55 AM

Drive Blind said:

If I have a number of users who I do not wish to view the drillthrough action how do I go about it.  Ideally I want to do something based on roles i.e.

Role() = 'myRole'


GetRole(Username()) = 'myROle'

I cant find an MDX expression that will return the role of the current user.  Having a CASe statement containing all of my users seems the only way to do this although it is cumbersome and not future proofed when new users join.

May 2, 2013 12:00 PM

Mohit Agarwal said:

It doesn't appear that only the columns listed in the Drillthrough columns are available for drillthrough. I was able to return dimension attributes that I didn't explicitly specify in the drillthrough columns list.

If you can drillthrough to any column that you have access to, then what's the point of having a drillthrough column list?

October 2, 2013 5:13 PM
New Comments to this post are disabled

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.
Privacy Statement