Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

One Cube, Different Dimensions, Different Data – with MDX

with 3 comments

The previous business scenario (different sums by different dimensions based on UserName function) can be implemented with MDX only. Just to remind the business requirements: users login to cube, they all have access to different departments (can be one or many) and their data, users must be assigned only to one Master department, they can see overall VisualTotal when data sliced by any dimension but Merit, they can see only subtotal of VisualTotal (excluding Master department) when data sliced by Merit dimension in any variation. To support MDX-only solution, relational schema needs to be modified. DimLogins table now consists of 3 columns: LoginKey, LoginID, AccessToMerit. LoginKey is a primary key; LoginID holds user Login ID and AccessToMeritData holds one of the 3 values:"None", "Full" or "Partial". "None" – no Access to Merit data completely means when data is sliced by Merit general message "no access" shown. "Full" means full access to the Merit data including Master department, Partial – partial access to data (excluding Master department sub-total from overall VisualTotal) when sliced by Merit and full data when sliced by any dimension but Merit. In the cube, a hidden dimension named Logins is built which consists of two attributes: [Login] – key attribute and [Access To Merit Data] attribute. Intersection of the current User login and [Login].[Access To Merit].[Partial] member will define the scope for the Merit related calculation only, since neither "Full" nor "None" requires any special calculations. FactLogin table will hold combination of the LoginID keys taken from Login table, DepartmentID keys taken from DimDepartment table and a special flag column called IsMeritAccessible. This flag will indicate whether department is Merit accessible or should be excluded from the total when sliced by all dimensions but Merit and show "no access" when sliced by Merit. From this fact table, I built additional hidden dimension called [Merit Flag] with only one attribute – [Accessible Merit] with values False and True. Intersection of this flag with user login will generate sets of the accessible and not accessible department ID’s.

The first statement after CALCULATE command in Cube Calculations pane would be

–find current user LoginID

create hidden set login as StrToSet("[Login].[Login].[" + UserName + "]");

Since this statement will execute each time a new user connects to a cube, set "login" will hold current user’s LoginID. Next statement finds all departments that the current user has access to.

–find the current user’s Departments ID’s he has an access to

create hidden set Access as


         (login, [Measures].[Fact Login], [Merit Flag].[Accessible Merit].[True])


Next statement finds all departments that the current user has NO access to.

–find the current user’s Departments ID’s he has an access to

create hidden set noAccess as

NonEmpty([Department].[ Department].[ Department].Members,

         (login, [Measures].[ Fact Login], [Merit Flag].[Accessible Merit].[False])



I will need these two sets to create a hidden measure that will called for [Partial] scope only. Next comes the measure


–member that aggregates over the current measure within the Access set context

create member currentcube.[Department].[Department].AggregateCurrentMeasure as

aggregate(Access, Measures.CurrentMember),

visible = 0;

Next is the set that will create a tree consisting of no-accessible departments regardless of the Current Member level, starting from the highest level down to the leaves

–build the [Department] branch to which a current user has no access

create hidden set noAccessMembers as

union (generate(noAccess, ascendants(noAccess.Current)),

       descendants (noAccess));

Finally comes the scope calculation

–check if current Merit member is not All Member (this indicates that user is slicing by Merit)

if not ([Merit].[Merit].CurrentMember is [Merit].[Merit].[All Merit])       


        –check if current  user has only partial access

        (count(exists(login, [Logins].[Access To Merit].[Partial])) > 0)


       –if current member is ALL (this indicates that user is not slicing by Department) then aggregate over Access set

       this = iif([Department].[ Department].CurrentMember is

                  [Department].[ Department].[All Departments]


           –aggregate over Access set here

           [Department].[ Department].AggregateCurrentMeasure,

           –otherwise if user is slicing by Department

           –and current department is restricted – show "no access",

           –if current department is not restricted – return current measure

            iif(count(intersect([Department].[ Department].CurrentMember, noAccessMembers)) > 0

               , "no access"

               , Measures.CurrentMember)


end if;


–if no access – return “no access”

if not ([Merit].[Merit].CurrentMember  is [Merit].[Merit].[All Merit])



        –check if current user has no access

        (count(exists(login, [Logins].[Access To Merit].[None])) > 0)


      this = "no access"

end if;

This script produces exactly the same results as the previous solution, only faster



Written by Konstantin Gorakine

February 26, 2010 at 6:00 pm

Posted in SSAS

3 Responses

Subscribe to comments with RSS.

  1. This is just exactly what we need to be able to do. I really appreciate the effort you put into this. Thanks!


    March 1, 2011 at 6:26 pm

  2. Addressing a similar issue in another context, see Dynamic Security (p. 244) in Chapter Securing the Cube in Expert Cube Development with Microsoft SQL Server 2008 Analysis Services by C. Webb, A. Ferrari, and M. Russo, PACKT Publishing, Birmingham-Mumbai, July, 2009.


    March 1, 2011 at 6:38 pm

  3. Here are this and some other articles on Analysis Services Custom Security:


    Sam Kane

    March 24, 2011 at 4:24 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: