Konstantin Gorakine's BI Lingual Blog

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

This is the final part of our stored procedure

           –plain cursor works faster than CTE

           declare @CurrentYearKey smallint

           declare flaotHolidays cursor fast_forward

           for select distinct CalendarYearKey from @DimDate

           open flaotHolidays

           fetch next from flaotHolidays into @CurrentYearKey

           while @@fetch_status = 0

             begin

               –Memorial Day

               update @DimDate set Holiday = 1, HolidayName = ‘Memorial Day’

                where DateKey = (select max(DateKey)

                 from @DimDate where CalendarMonthOfYearNum = 5

                  and DayNameOfWeekLong = ‘Monday’

                  and CalendarYearKey = @CurrentYearKey)

                –Labor Day

                update @DimDate set Holiday = 1, HolidayName=‘Labor Day’

                 where DateKey = (select min(DateKey)

                  from @DimDate where CalendarMonthOfYearNum = 9

                   and DayNameOfWeekLong = ‘Monday’

                   and CalendarYearKey = @CurrentYearKey)

                –Thanksgiving

                update @DimDate set Holiday = 1, HolidayName = ‘Thanksgiving’

                 where DateKey = (select (min(DateKey) + 21)

                  from @DimDate where CalendarMonthOfYearNum = 11

                   and DayNameOfWeekLong = ‘Thursday’

                   and CalendarYearKey = @CurrentYearKey)

           fetch next from flaotHolidays into @CurrentYearKey

           end

           close flaotHolidays deallocate flaotHolidays

           update @DimDate set RecordCreateDate = @CurrentDate,

                               RecordUpdateDate = @CurrentDate   

           –return the completed table as a result set.

           select * from @DimDate

end

Once procedure is compiled on the server, a SSIS package can be build with one Data Flow task having OLE DB Source with SQL Command text as following:

exec [sp_PopulateDateDimension]  ’01/01/2009′, ’12/31/2013′

Once package completes, table DimDate is populated with data with each level having it’s own key column that will be used while adding those levels as attribute hierarchies.

Therefore, instead of using Composite Keys as shown on the picture

, a Simple Key will be used providing more optimized key solution. Just don’t forget to order Month attribute hierarchy by the Key, not Name (default)

            calendar: starts on July 1st of the year calendar                      

           FiscalYearKey = case

                             when CalendarMonthOfYearNum <= 7

                               then CalendarYearKey

                              else CalendarYearKey + 1

                             end,

           FiscalSemesterOfYearNum = case

                                       when CalendarSemesterOfYearNum = 1

                                         then CalendarSemesterOfYearNum + 1

                                       else CalendarSemesterOfYearNum – 1  

                                     end,

           FiscalQuarterOfYearNum = case

                                       when CalendarQuarterOfYearNum < 3

                                         then CalendarQuarterOfYearNum + 2

                                        else CalendarQuarterOfYearNum – 2

                                    end,

           FiscalMonthOfYearNum =  case

                                     when CalendarMonthOfYearNum < 7

                                       then CalendarMonthOfYearNum + 6

                                     else CalendarMonthOfYearNum – 6

                                   end,

           FiscalWeekOfYearNum = case

                                   when CalendarWeekOfYearNum > 27

                                    or CalendarMonthOfYearNum >= 7

                                     then CalendarWeekOfYearNum – 26

                                    else CalendarWeekOfYearNum + 27

                                 end

    –load rest of data for fiscal calendar

    update @DimDate

           set

           FiscalWeekKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                           cast(FiscalWeekOfYearNum as varchar(2)), 2),

           FiscalMonthKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                            cast(FiscalMonthOfYearNum as varchar(2)), 2),

           FiscalQuarterKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                              cast(FiscalQuarterOfYearNum as varchar(2)), 2),

           FiscalSemesterKey = cast(FiscalYearKey as char(4)) + right(‘0’ +

                             cast(FiscalSemesterOfYearNum as varchar(2)), 2),

           FiscalYearLong = ‘FY ‘+ convert(char (4), FiscalYearKey),

           FiscalSemesterOfYear1 = ‘FY H’ +

             cast(FiscalSemesterOfYearNum as char(1)),

           FiscalSemesterLong = ‘FY H’ +

             cast(FiscalSemesterOfYearNum as char(1)) + ‘, ‘ +

             cast(FiscalYearKey as char(4)),

           FiscalQuarterOfYear1 = ‘FY Q’ +

             cast(FiscalQuarterOfYearNum as char(1)),

           FiscalQuarterLong = ‘FY Q’ +

             cast(FiscalQuarterOfYearNum as char(1)) + ‘, ‘ +

             cast(FiscalYearKey as char(4)),

           FiscalMonthOfYear1 = ‘FY M’ +

             cast(FiscalMonthOfYearNum as varchar(2)),

           FiscalMonthLong = ‘FY M’ +

             cast(FiscalMonthOfYearNum as varchar(2)) + ‘, ‘ +

             cast(FiscalYearKey as char(4)),

           FiscalWeekOfYear1 = ‘FY W’ +

             cast(FiscalWeekOfYearNum as varchar(2)),

           FiscalWeekLong = ‘FY W’ +

             cast(FiscalWeekOfYearNum as varchar(2)) + ‘, ‘ +

             cast(FiscalYearKey as char(4))

           –holidays (fixed)

           –New Year                     

           update @DimDate set Holiday = 1, HolidayName=‘New Year’

               where CalendarMonthOfYearNum = 1

                 and DayNumberOfMonth = 1 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate)

           –4th of July

           update @DimDate set Holiday = 1, HolidayName=‘4th Of July’

               where CalendarMonthOfYearNum = 7

                 and DayNumberOfMonth = 4 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate)

            –Christmas

            update @DimDate set Holiday = 1, HolidayName=‘Christmas’

               where CalendarMonthOfYearNum = 12

                 and DayNumberOfMonth = 25 and CalendarYearKey

                  in (select distinct CalendarYearKey from @DimDate);                            

           –holidays (float) – CTE works slower than cursor and hence

           commented

             —-Memorial Day

           –with MemorialDays as

           –(select CalendarYearKey, max(DateKey) as DayKey from @DimDate

           — where CalendarMonthOfYearNum = 5

           —   and DayNameOfWeekLong = ‘Monday’

           —   and CalendarYearKey

                 in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Memorial Day’

           —  where DateKey in (select DayKey from MemorialDays);

           —-Labor Day

           –with LaborDays as

           –(select CalendarYearKey, min(DateKey) as DayKey from @DimDate

           — where CalendarMonthOfYearNum = 9

              –and DayNameOfWeekLong = ‘Monday’ –and CalendarYearKey

               –in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Labor Day’

             –where DateKey in (select DayKey from LaborDays);

             —-Thanksgiving

             –with Thanksgivings as

           –(select CalendarYearKey, min(DateKey) + 21 as DayKey

            –from @DimDate

           — where CalendarMonthOfYearNum = 11

              –and DayNameOfWeekLong = ‘Thursday’

              –and CalendarYearKey

               –in (select distinct CalendarYearKey from @DimDate)                

           — group by CalendarYearKey )                 

           — update @DimDate set Holiday = 1, HolidayName = ‘Thanksgiving’   

            –where DateKey in (select DayKey from Thanksgivings);