One Cube, Different Dimensions, Different Data – with MDX

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

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

         (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])       

        and

        –check if current  user has only partial access

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

then

       –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])

 

        and

        –check if current user has no access

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

then

      this = "no access"

end if;

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