Date and Time Dimension Template

by Admin12. February 2013 15:02

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.

 

 

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About company

BIT Impulse - a software development company, a vendor of a proprietary BI system called "Business Analysis Tool".

Web site: www.bitimpulse.com

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar