Dimension Template

In this post we will show how we do date and time dimensions. Our template is located here:

http://download.bitimpulse.com/public/bat/DateTimeDemo_v2.zip

In this file you will find backups of the SQL and Analysis databases.

We have a table

CREATE TABLE [dbo].[dimDate](

    [TheDate] [datetime] NOT NULL,

    [Year] [smallint] NOT NULL,

    [QuarterOfYear] [tinyint] NOT NULL,

    [MonthOfYear] [tinyint] NOT NULL,

    [DayOfMonth] [tinyint] NOT NULL,

    [Quarter] [int] NOT NULL,

    [Month] [int] NOT NULL,

    [Day] [int] NOT NULL,

    [DayOfWeek] [tinyint] NOT NULL,

    [IsWorkingDay] [int] NOT NULL,

    [QuarterNameEN] [nvarchar](20) NOT NULL,

    [QuarterNameRU] [nvarchar](20) NOT NULL,

    [QuarterNameUA] [nvarchar](20) NOT NULL,

    [MonthNameEN] [nvarchar](20) NOT NULL,

    [MonthNameRU] [nvarchar](20) NOT NULL,

    [MonthNameUA] [nvarchar](20) NOT NULL,

    [DayNameEN] [nvarchar](15) NOT NULL,

    [DayNameRU] [nvarchar](15) NOT NULL,

    [DayNameUA] [nvarchar](15) NOT NULL,

    [DayOfWeekNameEN] [nvarchar](15) NOT NULL,

    [DayOfWeekNameRU] [nvarchar](15) NOT NULL,

    [DayOfWeekNameUA] [nvarchar](15) NOT NULL,

    [QuarterOfYearNameEN] [nvarchar](50) NOT NULL,

    [QuarterOfYearNameRU] [nvarchar](10) NOT NULL,

    [QuarterOfYearNameUA] [nvarchar](10) NOT NULL,

    [MonthOfYearNameEN] [nvarchar](20) NOT NULL,

    [MonthOfYearNameRU] [nvarchar](20) NOT NULL,

    [MonthOfYearNameUA] [nvarchar](20) NOT NULL,

    [IsWorkingDayNameEN] [nvarchar](20) NOT NULL,

    [IsWorkingDayNameRU] [nvarchar](20) NOT NULL,

    [IsWorkingDayNameUA] [nvarchar](20) NOT NULL,

    [WYear] [int] NOT NULL,

    [WMonthID] [int] NOT NULL,

    [WMonth] [int] NOT NULL,

    [WeekID] [int] NOT NULL,

    [Week] [int] NOT NULL,

    [WYearNameEN] [nvarchar](20) NOT NULL,

    [WYearNameRU] [nvarchar](20) NOT NULL,

    [WYearNameUA] [nvarchar](20) NOT NULL,

    [WMonthNameEN] [nvarchar](20) NOT NULL,

    [WMonthNameRU] [nvarchar](20) NOT NULL,

    [WMonthNameUA] [nvarchar](20) NOT NULL,

    [WeekNameEN] [nvarchar](20) NOT NULL,

    [WeekNameRU] [nvarchar](20) NOT NULL,

    [WeekNameUA] [nvarchar](20) NOT NULL,

    CONSTRAINT [PK_dim_time] PRIMARY KEY CLUSTERED

    (

        [Day] ASC

    )

)

GO

This table can be filled with stored procedure sprCalendarFromDateToDateFill which gets 2 parameters - @FromDate and @ToDate.

The dimension looks like this:

There are 2 hierarchies – natural calendar, and weekly calendar. The calendar hierarchy looks like this:

The weekly hierarchy looks like this:

In weekly hierarchy every month is not an actual month – it is a "virtual" month consisting of whole weeks.

The most important thing is next: the key for day attribute has format YYYYMMDD, quarter - YYYYQ, month - YYYYMM, year – YYYY, week– YYYYWW.

Dynamic Date Selection in BAT

If you want to have a dynamic date selection in the Business Analysis Tool

You have to meet 2 requirements:

1. Your attribute keys must be in format, specified above (YYYYMMDD, YYYYMM, YYYYQ, YYYY, YYYYWW). It doesn't mean you must have ALL the attributes in this format, but if you want to have "last N days" selection – your date key must be in YYYYMMDD format, if you want to have "last N month" selection – your month key must be in YYYYMM format, etc.
2. You must give a "hint" to the BAT, so that BAT knows which level of dimension has a necessary format.

The hint should look like this:

The hint is constructed by this pattern:

[YMD=name_of_day_level, YQ=name_of_quarter_level, YM=name_of_month_level, Y=name_of_year_level , YW=name_of_week_level]

So, there are brackets [], there are TAG=level_name parts separated with comma, where TAG can be one of the following: Y, YQ, YM, YMD, YW.

Rules are next:

1. You are not forced to use ALL the tags Y, YQ, YM, YMD, YW in one hierarchy, you may use a subset of them – depending what keys you have in a necessary format and what levels the hierarchy contains (if a hierarchy doesn't have quarter level at all – of course you shouldn't specify YQ=name_of_quarter_level, because application will be "fooled" and you will get a crash).
2. You cannot use the tags if keys are of wrong format. For instance, if key for month are in format 1,2,3… but not 201301, 201302, 201203 and you set up YM=Month, you will get wrong result of MDX execution, because BAT assumes that key format is YYYYMM and builds MDX queries using this assumption. So, you should specify TAG=… for only those levels which exists and which has the proper format.