Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

Archive for the ‘SSAS’ Category

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

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

 

Advertisements

Written by Konstantin Gorakine

February 26, 2010 at 6:00 pm

Posted in SSAS

One Cube, Different Dimensions, Different Data

with 3 comments

Just started my new gig in NYC and already got some rather unusual requirements related to the cube data security. Client has a small cube with one secured dimension (call it Org Structure) and one special dimension (call it Merit) which when sliced by, should behave slightly different from the rest of dimensions.

A user can have access to one or several departments. No matter how many departments user has access to; one department is always a Master department for him/her.  User has access to data for all departments when sliced by any dimension except Merit, and data for all departments except Master when sliced by Merit dimension. Let me put that requirement in Excel for better visualization.

Here, all data is freely accessible when sliced by any dimension (except Merit); in this case by Org Structure. You can see that Master department data contributes to the total and there is no restrictions applied to the data visibility. Results should change when we slice by Merit dimension:

Noticed the difference in the total? When we slice by the Merit dimension, we should not have access to the Master department data, since this data is highly sensitive for a user and must be hidden. Take a look at another picture:

Any time user is using Merit dimension, data is recalculated to reflect new values without master department figures.  

My solution consists of two almost identical cubes (which will source their data from the same relational database) and a .NET component. One cube with security implemented will be facing users answering the majority of the requests, whereas the second will be contacted via .NET stored proc only when request by the Merit dimension is submitted. The second cube will have no security restrictions and will stay hidden for the users.  The reason why we would need a second cube is simple:  infinite recursion. It’ll be clear soon.

First of all, we need to know when we slice by Merit department, so we apply our calculations to this dimension only. This easily can be achieved by using Scope statement in the cube script. My initial scope statement looked like this:

scope ([Measures].[Measure] *

       [Merit].[Merit].Members);

      

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

                   [Merit].[Merit].[All Merits])

           then

       

               this = sum(NonEmpty([Org Structure].[Org Structure Key].Children,

                                      (StrToMember("[Logins].[Login].[" + userName + "]"),

                                       [Measures].[Login OrgStructure]

                                       )

                                   )

                          )              

           end if;           

end scope;

Here, I have my security filter added, so I thought that I would be able to obtain results from the same cube, but when SSAS is trying to resolve [Merit].[Merit].CurrentMember it is running the same scope statement eventually falling into infinite recursion. This is how I came up with an idea of having another cube. I would write .NET stored proc, obtain the tuple of the current cell by using ADOMDServer library, build dynamic MDX, and open ADOMDClient connection to the second cube to execute my query. This path needs to be run for every cell on the report, so when users want to drill down their results to the lover levels, execution time will linearly increase.

Here is my final scope statement with the call to the .NET proc added.

scope ([Measures].[Measure] *

       [Merit].[Merit].Members);

      

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

                   [Merit].[Merit].[All Merits])

           then

       

               this = MeritSecurity.GetMerit(

                      "Provider=MSOLAP.4;Initial Catalog=hidden cube;Data Source=myserver100"

                                              )              

           end if;           

end scope;

Here is the .NET proc code and it’s well-commented

Imports Microsoft.AnalysisServices.AdomdServer

 

Public Class MeritSecurity

 

    Public Function getMerit(ByVal connectionString As String) As String

 

        Const COMMA As String = ","

 

        Dim selectTuple As String = String.Empty

        Dim whereSlicer As String = String.Empty

        Dim result As String = String.Empty

 

        Dim currentCellAddressTB As New TupleBuilder

        Dim currentCellAddressM As MemberCollection

 

        ‘get the current user name

        Dim userName As String = New Expression("Username()").Calculate(Nothing).ToString

        ‘build the sub-select slicer with the security filter

        whereSlicer = "(NonEmpty([Org Structure].[Org Structure Key].Children, " + _

                      "         (StrToMember(""[Logins].[Login].[" + userName + "]""), " + _

                      "                        [Measures].[Login OrgStructure])) ) on 0 from [hidden cube])"     

 

        ‘go over the current context and collect all current members into tuple bulder

        For Each currentDimension In Context.CurrentCube.Dimensions

            For Each currentHierarchy In currentDimension.Hierarchies

                currentCellAddressTB.Add(currentHierarchy.CurrentMember)

            Next

        Next

 

        ‘place all members into a member collection

        currentCellAddressM = currentCellAddressTB.ToTuple.Members

 

        ‘build select tuple for the current cell

        For Each currentMember In currentCellAddressM

            selectTuple += currentMember.UniqueName + COMMA

        Next

 

        ‘get rid of the last comma and close the tuple

        selectTuple = "(" + Left(selectTuple, selectTuple.Length – 1) + ")"

 

        ‘get all ADOMDClient objects to run query against the second cube

        Dim SSASconnection As New Microsoft.AnalysisServices.AdomdClient.AdomdConnection

        Dim SSAScommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand = + _

        SSASconnection.CreateCommand()

        Dim SSASDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader

 

        SSASconnection.ConnectionString = connectionString

        SSAScommand.CommandType = CommandType.Text

        ‘build the final query

        SSAScommand.CommandText = "select " + selectTuple + " on 0 from " + whereSlicer

 

        SSASconnection.Open()

        ‘RUN IT FINALLY…

        SSASDataReader = SSAScommand.ExecuteReader()

 

        Try

            If SSASDataReader.Read() Then

                result = SSASDataReader.GetString(0)

            Else

                result = "no access"

            End If

        Catch ex As Exception

            result = String.Empty

        End Try

 

        SSASconnection.Close()

 

        Return result

 

    End Function

 

End Class

 

This will work, returning different results when data is sliced by different dimensions. As I’ve mentioned, this can and will impact query time dramatically, but client really wants this functionality to be somehow inplemented.

Any feedbacks will be appreciated

Written by Konstantin Gorakine

January 28, 2010 at 5:31 pm

Posted in SSAS

Create New SSAS Partition dynamically: XML Task

with one comment

There is a good number of posts exist on how to create and manage new SSAS partitions dynamically, but almost all of them based on AMO coding and may look intimidating to some folks especially with little .NET skills in the past but expert DBA skills presently J. Knowing my programming background, I was "kindly" asked to provide a framework for dynamic partition creation with as little code implementation as possible, so it can be maintained by a person with a different skill set. Let’s think a bit: SSAS partition object can be scripted as XMLA script, some XMLA tags can be replaced dynamically to ensure partition uniqueness, SSIS XML Task has Operation Type – Merge… Ok, enough thinking J

 

Assume, that we want to create a new partition for each new year of data. We will need a simple cube with at least one QueryBinding partition already created and having WHERE clause, just to generate our version of partition XMLA (original partition can be deleted after we are done), something like this:

 
 

As you can see, there are just a few dynamic parts that can be replaced on the fly to make partition script fully dynamic, the list is highlighted: Partition ID, Partition Name, WHERE clause and Partition Slice. SSIS XML Task Merge operation can add new tabs to a XML document, so basically, idea here is to add all dynamic parts in the run time and send the final script to the SSAS for execution. Let’s try to do that. First, we create a Master XMLA partition script template by deleting all dynamic tags and store it somewhere in the share folder. So, the Master script template will look like this:

 

 

As you can see, there is no Partition ID, Name or Slice tags in it, as well, WHERE clause got modified by replacing actual Year value with "####" mask. Additionally, <Create> tag had xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" attribute, but looks like XML Task can’t read the entire XML when this attribute is present, so I was forced to remove it while building dynamic XMLA with XML Task and restore it right before sending final XMLA script to the SSAS, since SSAS won’t process <Create> command without it. Next, I built a simple SSIS package with 5 tasks and 5 variables.

 

 

Package Variables are set up and configured in the following way

 

 

For example, Node_PartiitonID variable’s Expression property is set to "<ID>Transaction_" + @[User::CurrentYear] + "</ID>" to build partition ID based on the current Year.

 

[Add Partition ID] XML Task is configured as follows and self-explanatory.

 

 

Here, I connected to my Master XMLA template, went to /Create/ObjectDefinition/Partition XPath and inserted value taken from [User::Node_PartitionID] variable. After all this, I saved results of generated XMLA script to [User::XMLA] variable. In the next two tasks, I am sequentially adding Partition Name and Partition Slice to a in-variable script, each time modifying and saving final version of XMLA variable contents. Configuration for the [Add Partition Slice] looks like this.

 

 

Here, source is [User::XMLA] variable of course, not initial version of the Master.xmla file. The task before the last one is the Script Task. We need to add some final details to the almost-ready-for-the-run XMLA script. Couldn’t avoid using .NET completely, J

 

      Public Sub Main()

 

        ‘year mask

        Const YEAR_MASK As String = "####"

        ‘get current year

        Dim sCurrentYear As String = _

             Dts.Variables("User::CurrentYear").Value.ToString

        ‘replace #### mask with current year value

        Dim sXML As String = _

             Dts.Variables("User::XMLA").Value.ToString.Replace(YEAR_MASK, sCurrentYear)

        ‘add before droped xmlns attribute back to <Create> tag

        sXML = sXML.Replace("<Create>", _

             "<Create xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">&quot;)

        ‘store modified script back to var

        Dts.Variables("User::XMLA").Value = sXML

 

        Dts.TaskResult = Dts.Results.Success

      End Sub

 

The last task sends our final XMLA script to the SSAS server.

 

All this framework (5 tasks) can be placed inside a For Each Loop container, which will loop over all years that require new partition creation. The logic on how to find these years may be implemented differently, depending on overall system requirements and SSAS version. This can be done by reading a list of existing partitions from DMV, or SSIS can be configured not to fail if partition already exists, etc.

 

Of course, this solution has it’s own drawbacks: partitions can be created sequentially only and not in parallel, but if requirements for partition creation are moderate, like in our case, then it’s good enough. Another one is a maintenance of the Master XMLA template, but my answer to this: if DBA’s are already managing thousands of scripts, then one or few more is not a big deal at all J. Nonetheless, the main demand is achieved: no or little of .NET "nightmare". 

 

Written by Konstantin Gorakine

October 30, 2009 at 1:41 pm

Posted in SSAS

Date Dimension script optimized for Analysis Services KeyColumn property (Part 5)

with one comment

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)

Written by Konstantin Gorakine

July 15, 2009 at 12:51 pm

Posted in SSAS

Date Dimension script optimized for Analysis Services KeyColumn property (Part 4)

leave a comment »

      —-load initial keys for fiscal calendar (example of fiscal

            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);

     

Written by Konstantin Gorakine

July 15, 2009 at 12:50 pm

Posted in SSAS

Date Dimension script optimized for Analysis Services KeyColumn property (Part 3)

leave a comment »

 

    –specify Sunday as the 1st day of the week.

    set datefirst 7

 

    –generate dates only

    while @CurrentDate <= @EndDate

      begin

         insert @DimDate (Date) values (@CurrentDate)

 

         — move to the next day

         set @CurrentDate = dateadd(day, 1, @CurrentDate)

    end

 

    –current timestamp

    set @CurrentDate = getdate()   

 

    –load initial keys for year calendar

    update @DimDate

       set Date1 = convert(char(10), Date, 101),

           Date2 = replace(convert(varchar(11), Date, 106), ‘ ‘, ‘-‘),

           Date3 = datename(mm, Date) + ‘ ‘ +

                   datename(dd, Date) + ‘, ‘ +

                   datename(yy, Date),

           DateLong = datename(dw , Date) + ‘, ‘ +

                      datename(month , Date) + ‘ ‘ +

                      cast(datepart(day, Date) as varchar(2)) + ‘, ‘ +

                      cast(datepart(year, Date) as char(4)),

 

           CalendarYearKey  = cast(datepart(year, Date) as smallint),

           CalendarSemesterOfYearNum =

              case

                when cast(datepart(month, Date) as tinyint)

                     in (1, 2, 3, 4, 5, 6) then 1

                else 2

              end,

           CalendarQuarterOfYearNum   =

             cast(datepart(quarter, Date) as tinyint),

           CalendarMonthOfYearNum = cast(datepart(month, Date) as tinyint),

           CalendarWeekOfYearNum  = cast(datepart(week, Date) as tinyint),

 

           DayNumberOfYear = cast(datepart(dayofyear, Date) as smallint),

           DayNumberOfMonth = cast(datepart(day, Date) as tinyint),

           DayNumberOfWeek = cast(datepart(weekday, Date) as tinyint)

    –load rest of data for year calendar

    update @DimDate

       set DateKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarMonthOfYearNum as varchar(2)), 2) +

             right(‘0’ + cast(DayNumberOfMonth as varchar(2)), 2),

           CalendarWeekKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarWeekOfYearNum as varchar(2)), 2),

           CalendarMonthKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarMonthOfYearNum as varchar(2)), 2),     

           CalendarQuarterKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarQuarterOfYearNum as varchar(2)), 2),

           CalendarSemesterKey = cast(CalendarYearKey as char(4)) +

             right(‘0’ + cast(CalendarSemesterOfYearNum as varchar(2)), 2),

 

           CalendarYearLong = ‘CY ‘+ convert(char (4), CalendarYearKey),

           CalendarSemesterOfYear1 = ‘Semester ‘ +

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

             cast(CalendarYearKey as char(4)),

           CalendarSemesterOfYear2 = ‘CY H’ +

             cast(CalendarSemesterOfYearNum as char(1)),

           CalendarSemesterLong = ‘CY H’ +

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

             cast(CalendarYearKey as char(4)),

 

           CalendarQuarterOfYear1 = ‘Quarter ‘ +

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

             cast(CalendarYearKey as char(4)),

           CalendarQuarterOfYear2 = ‘CY Q’ +

             cast(CalendarQuarterOfYearNum as char(1)),

           CalendarQuarterLong = ‘CY Q’ +

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

             cast(CalendarYearKey as char(4)),

                    

           MonthNameLong = datename(month , Date),

           MonthNameShort = left(datename(month , Date), 3),

           CalendarMonthOfYear2 = datename(month , Date) + ‘-‘ +

             cast(CalendarYearKey as char(4)),

           CalendarMonthOfYear3 =

             replace(right(convert(varchar(11), Date, 106), 8), ‘ ‘, ‘-‘),

          

           CalendarWeekOfYear1 = ‘Week ‘ +

             cast(datepart(week, Date) as varchar(2)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           CalendarWeekOfYear2 = ‘CY Week ‘ +

             cast(datepart(week, Date) as varchar(2)),

           CalendarWeekLong = ‘CY Week ‘ +

             cast(datepart(week, Date) as varchar(2)) + ‘, ‘ +

             cast(CalendarYearKey as char(4)),

           DayNameOfWeekLong = datename(dw , Date),

           DayNameOfWeekShort = left(datename(dw , Date), 3),

           WorkDay = case

                       when DayNumberOfWeek in (2, 3, 4, 5, 6) then 1

                       else 0

                     end,

          

     

Written by Konstantin Gorakine

July 15, 2009 at 12:49 pm

Posted in SSAS

Date Dimension script optimized for Analysis Services KeyColumn property (Part 2)

leave a comment »

 

Here is the beginning of the stored procedure that generates DimDate data:

create procedure [sp_PopulateDateDimension]

    (

                 @StartDate smalldatetime,

                 @EndDate smalldatetime

      )

as

  begin

 

    set nocount on

 

    declare @CurrentDate smalldatetime

    set @CurrentDate = @StartDate

 

    — Working table for intermediate results

    declare @DimDate table (

    DateKey int,

    Date smalldatetime,

    Date1 char(10),

    Date2 char(11),

    Date3 varchar(20),

    DateLong varchar(30),                                                                        

                                

    –year data                          

    CalendarYearKey smallint,

    CalendarYearLong char(7), 

    DayNumberOfYear smallint,

       

    –semester data

    CalendarSemesterKey int,

    CalendarSemesterOfYearNum tinyint,

    CalendarSemesterOfYear1 char(16),

    CalendarSemesterOfYear2 char(5),

    CalendarSemesterLong char(11),

       

    –quarter data

    CalendarQuarterKey int,

    CalendarQuarterOfYearNum  tinyint,

    CalendarQuarterOfYear1 char(15),

    CalendarQuarterOfYear2 char(5),

    CalendarQuarterLong char(11),

    

    –month data

    CalendarMonthKey int,

    CalendarMonthOfYearNum tinyint,                                    

    MonthNameLong varchar(10),

    MonthNameShort varchar(3),

    CalendarMonthOfYear2 varchar(30),

    CalendarMonthOfYear3 varchar(10),

    DayNumberOfMonth tinyint, 

       

    –week data

    CalendarWeekKey integer,

    CalendarWeekOfYearNum tinyint,               

    CalendarWeekOfYear1 varchar(13),

    CalendarWeekOfYear2 varchar(10),

    CalendarWeekLong varchar(16),

    DayNameOfWeekLong varchar(10), 

    DayNameOfWeekShort varchar(3),

    DayNumberOfWeek tinyint,

    WorkDay tinyint,

 

    –fiscal year data

    FiscalYearKey smallint,

    FiscalYearLong char(7),

       

    –fiscal semester data

    FiscalSemesterKey int,

    FiscalSemesterOfYearNum tinyint,

    FiscalSemesterOfYear1 char(5),

    FiscalSemesterLong char(11),

                    

    –fiscal quarter data

    FiscalQuarterKey int,

    FiscalQuarterOfYearNum tinyint,

    FiscalQuarterOfYear1 char(5),

    FiscalQuarterLong char(11),

    

    –fiscal month data

    FiscalMonthKey int,

    FiscalMonthOfYearNum tinyint,                            

    FiscalMonthOfYear1 varchar(7),

    FiscalMonthLong varchar(12),

 

    –fiscal week data

    FiscalWeekKey int,

    FiscalWeekOfYearNum tinyint,                     

    FiscalWeekOfYear1 varchar(7),   

    FiscalWeekLong  varchar(12),                      

    

    –holiday data

    Holiday tinyint,

    HolidayName varchar(15),

    

    RecordCreateDate smalldatetime,

    RecordUpdateDate smalldatetime

   )

Written by Konstantin Gorakine

July 15, 2009 at 12:47 pm

Posted in SSAS