Microsoft Business Intelligence: Conformed Dimension in DWH

A conformed dimension is a dimension that has the same meaning to every fact with which it relates. Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent reporting across the enterprise.

In other word we can see that A Conformed dimensions are dimensions which can be used across any business area – that is, the business key of the table is applicable to any subject area where the dimension has relevance, and attribute labels, definitions and values are consistent across the business.

A conformed dimension can exist as a single dimension table that relates to multiple fact tables within the same data warehouse, or as identical dimension tables in separate data marts. Date is a common conformed dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning when joined to any fact table.

Here we are creating DimDate Dimension table

Below is the script to create the table.

CREATE TABLE dimdate
  (
     datekey                          INT NOT NULL,
     datefullname                     VARCHAR(50) NULL,
     datefull                         DATE NULL,
     year                             INT NULL,
     quarter                          INT NULL,
     quartername                      VARCHAR(50) NULL,
     quarterfullname                  VARCHAR(50) NULL,
     quarterkey                       INT NULL,
     month                            INT NULL,
     monthkey                         INT NULL,
     monthname                        VARCHAR(50) NULL,
     dayofmonth                       INT NULL,
     numberofdaysinthemonth           INT NULL,
     dayofyear                        INT NULL,
     weekofyear                       INT NULL,
     weekofyearkey                    INT NULL,
     isoweek                          INT NULL,
     isoweekkey                       INT NULL,
     weekday                          INT NULL,
     weekdayname                      VARCHAR(50) NULL,
     fiscalyear                       INT NULL,
     fiscalquarter                    INT NULL,
     fiscalquarterkey                 INT NULL,
     fiscalmonth                      INT NULL,
     fiscalmonthkey                   INT NULL,
     isworkdaykey                     INT NULL,
     isworkdaydescription             VARCHAR(50) NULL,
     calendarquarter                  VARCHAR(50) NULL,
     calendaryear                     VARCHAR(50) NULL,
     calendarsemester                 VARCHAR(50) NULL,
     isleapyear                       INT NULL,
     has53weeks                       VARCHAR(50) NULL,
     has53isoweeks                    VARCHAR(50) NULL,
     mmyyyy                           VARCHAR(50) NULL,
     style101                         VARCHAR(50) NULL,
     style103                         VARCHAR(50) NULL,
     style112                         VARCHAR(50) NULL,
     style120                         VARCHAR(50) NULL,
     firstdayofweekstartfromsunday    VARCHAR(50) NULL,
     lastdayofweekstartfromsunday     VARCHAR(50) NULL,
     firstdayofweekstartfrommonday    VARCHAR(50) NULL,
     lastdayofweekstartfrommonday     VARCHAR(50) NULL,
     firstdayofweekstartfromtuesday   VARCHAR(50) NULL,
     lastdayofweekstartfromtuesday    VARCHAR(50) NULL,
     firstdayofweekstartfromwednesday VARCHAR(50) NULL,
     lastdayofweekstartfromwednesday  VARCHAR(50) NULL,
     firstdayofweekstartfromthursday  VARCHAR(50) NULL,
     lastdayofweekstartfromthursday   VARCHAR(50) NULL,
     firstdayofweekstartfromfriday    VARCHAR(50) NULL,
     lastdayofweekstartfromfriday     VARCHAR(50) NULL,
     firstdayofweekstartfromsaturday  VARCHAR(50) NULL,
     lastdayofweekstartfromsaturday   VARCHAR(50) NULL
     CONSTRAINT pk_dimdate PRIMARY KEY CLUSTERED ( datekey ASC )WITH (pad_index
     = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks
     = on, allow_page_locks = on) ON [PRIMARY]
  )
ON [PRIMARY]

Below is the script to load the dimension table from 1990-01-01 to 2030-12-31 (40 years date).

DECLARE @CurrentDate DATE
DECLARE @FiscalYearStartMonth INT
DECLARE @WeeklyHolidays TABLE
  (
     weekday INT
  ) -- weekday, sunday is 1 and saturday is 7
DECLARE @FirstDate DATE
DECLARE @NumberOfYearsToGenerate INT
DECLARE @LastDate DATE

SET @FirstDate='1990-01-01'
SET @NumberOfYearsToGenerate=40
-- do not change line below
SET @LastDate=Dateadd(year, @NumberOfYearsToGenerate, @FirstDate)
SET @CurrentDate=@FirstDate
SET @FiscalYearStartMonth=7

-- insert weekly holidays
INSERT INTO @WeeklyHolidays
            (weekday)
VALUES     (1) -- sunday
INSERT INTO @WeeklyHolidays
            (weekday)
VALUES     (7) -- saturday
WHILE( @CurrentDate < @LastDate )
  BEGIN
      INSERT INTO dimdate
                  (datekey,
                   datefullname,
                   datefull,
                   year,
                   quarter,
                   quartername,
                   quarterfullname,
                   quarterkey,
                   month,
                   monthkey,
                   monthname,
                   dayofmonth,
                   numberofdaysinthemonth,
                   dayofyear,
                   weekofyear,
                   weekofyearkey,
                   isoweek,
                   isoweekkey,
                   weekday,
                   weekdayname,
                   fiscalyear,
                   fiscalquarter,
                   fiscalquarterkey,
                   fiscalmonth,
                   fiscalmonthkey,
                   isworkdaykey,
                   isworkdaydescription,
                   calendarquarter,
                   calendaryear,
                   calendarsemester,
                   isleapyear,
                   has53weeks,
                   has53isoweeks,
                   mmyyyy,
                   style101,
                   style103,
                   style112,
                   style120,
                   firstdayofweekstartfromsunday,
                   lastdayofweekstartfromsunday,
                   firstdayofweekstartfrommonday,
                   lastdayofweekstartfrommonday,
                   firstdayofweekstartfromtuesday,
                   lastdayofweekstartfromtuesday,
                   firstdayofweekstartfromwednesday,
                   lastdayofweekstartfromwednesday,
                   firstdayofweekstartfromthursday,
                   lastdayofweekstartfromthursday,
                   firstdayofweekstartfromfriday,
                   lastdayofweekstartfromfriday,
                   firstdayofweekstartfromsaturday,
                   lastdayofweekstartfromsaturday)
      SELECT CONVERT(INT, CONVERT(VARCHAR(8), @CurrentDate, 112))
             AS
             DateKey
             ,
             CONVERT(VARCHAR(max), @CurrentDate, 106)
             AS DateFullName,
             @CurrentDate
             AS
             DateFull,
             Datepart(year, @CurrentDate)
             AS
             Year,
             Datepart(quarter, @CurrentDate)
             AS
             Quarter,
             'QTR ' + Datename(quarter, @CurrentDate)
             AS
             QuarterName,
             CASE Datepart(qq, @CurrentDate) WHEN 1 THEN 'First' WHEN 2 THEN
             'Second'
             WHEN 3
             THEN 'Third' WHEN 4 THEN 'Fourth' END + ' Quarter'
             AS
             QuarterFullName,
             CONVERT(INT, Datename(year, @CurrentDate)
                          + Datename(quarter, @CurrentDate))
             AS
             QuarterKey,
             Datepart(month, @CurrentDate)
             AS
             Month,
             CONVERT(INT, Datename(year, @CurrentDate)
                          + RIGHT('0'+CONVERT(VARCHAR(2), Datepart(month,
                          @CurrentDate
                          )), 2))
             AS
             MonthKey,
             Datename(month, @CurrentDate)
             AS
             MonthName,
             Datepart(day, @CurrentDate)
             AS
             DayOfMonth,
             Datepart(day, Eomonth(@CurrentDate))
             AS
             NumberOfDaysInTheMonth,
             Datepart(dayofyear, @CurrentDate)
             AS
             DayOfYear,
             Datepart(week, @CurrentDate)
             AS
             WeekOfYear,
             Datename(year, @CurrentDate)
             + RIGHT('0'+Datename(week, @CurrentDate), 2)
             AS
             WeekOfYearKey,
             Datepart(iso_week, @CurrentDate)
             AS
             ISOWeek,
             Datename(year, @CurrentDate)
             + RIGHT('0'+CONVERT(VARCHAR(2), Datepart(iso_week, @CurrentDate)),
             2)
             AS
             ISOWeekKey,
             Datepart(weekday, @CurrentDate)
             AS
             WeekDay,
             Datename(weekday, @CurrentDate)
             AS
             WeekDayName,
             CASE
               WHEN Month(@CurrentDate) < @FiscalYearStartMonth THEN
               Year(@CurrentDate)
               ELSE Year(@CurrentDate) + 1
             END
             AS
             FiscalYear,
             Ceiling(CONVERT(FLOAT, ( CASE
                                        WHEN Month(@CurrentDate) = 13
                                             - @FiscalYearStartMonth
                                      THEN 12
                                        ELSE ( ( @FiscalYearStartMonth - 1 ) +
                                               Month(
                                               @CurrentDate) )%
                                             12
                                      END )) / 3)
             AS
             FiscalQuarter,
             CONVERT(VARCHAR(4), CASE WHEN Month(@CurrentDate)<
             @FiscalYearStartMonth
             THEN
             Year(@CurrentDate) ELSE Year(@CurrentDate)+1 END) + CONVERT(VARCHAR
             (1
             ),
             Ceiling(
             CONVERT(FLOAT, (CASE WHEN Month(@CurrentDate)=13
             -@FiscalYearStartMonth
             THEN 12
             ELSE ((@FiscalYearStartMonth-1) +Month(@CurrentDate))%12 END))/3))
             AS
             FiscalQuarterKey,
             CASE
               WHEN Month(@CurrentDate) = 13 - @FiscalYearStartMonth THEN 12
               ELSE ( ( @FiscalYearStartMonth - 1 ) + Month(@CurrentDate) )%12
             END
             AS
             FiscalMonth,
             CONVERT(VARCHAR(4), CASE WHEN Month(@CurrentDate)<
             @FiscalYearStartMonth
             THEN
             Year(@CurrentDate) ELSE Year(@CurrentDate)+1 END) + RIGHT('0'+
             CONVERT
             (
             VARCHAR(2)
             , CASE WHEN Month(@CurrentDate)=13-@FiscalYearStartMonth THEN 12
             ELSE
             ((
             @FiscalYearStartMonth-1) +Month(@CurrentDate))%12 END), 2)
             AS
             FiscalMonthKey,
             CASE
               WHEN Datepart(weekday, @CurrentDate) IN (SELECT weekday
                                                        FROM   @WeeklyHolidays)
             THEN 1
               ELSE 0
             END
             AS
             IsWorkDayKey,
             CASE
               WHEN Datepart(weekday, @CurrentDate) IN (SELECT weekday
                                                        FROM   @WeeklyHolidays)
             THEN
               'Weekend'
               ELSE 'Workday'
             END
             AS
             IsWorkDayDescription,
             Datepart(qq, @CurrentDate)
             AS
             CalendarQuarter,
             Year(@CurrentDate)
             AS
             CalendarYear,
             CASE Datepart(qq, @CurrentDate)
               WHEN 1 THEN 1
               WHEN 2 THEN 1
               WHEN 3 THEN 2
               WHEN 4 THEN 2
             END
             AS
             CalendarSemester,
             CONVERT(BIT, CASE
                            WHEN ( Datepart(year, @CurrentDate) % 400 = 0 )
                                  OR ( Datepart(year, @CurrentDate) % 4 = 0
                                       AND Datepart(year, @CurrentDate) % 100 <>
                                           0
                                     )
                          THEN 1
                            ELSE 0
                          END)
             AS
             IsLeapYear,
             CASE
               WHEN Datepart(iso_week, Datefromparts(Year(@CurrentDate), 12, 31)
                    )
                    = 53
             THEN 1
               ELSE 0
             END
             AS
             Has53Weeks,
             CASE
               WHEN Datepart(week, Datefromparts(Year(@CurrentDate), 12, 31)) =
                    53
             THEN 1
               ELSE 0
             END
             AS
             Has53ISOWeeks,
             CONVERT(CHAR(2), CONVERT(CHAR(8), @CurrentDate, 101))
             + CONVERT(CHAR(4), Datepart(year, @CurrentDate))
             AS
             MMYYYY,
             CONVERT(CHAR(10), @CurrentDate, 101)
             AS
             Style101,
             CONVERT(CHAR(10), @CurrentDate, 103)
             AS
             Style103,
             CONVERT(CHAR(8), @CurrentDate, 112)
             AS
             Style112,
             CONVERT(CHAR(10), @CurrentDate, 120)
             AS
             Style120,
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 1 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromSunday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 0 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromSunday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 2 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromMonday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 1 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromMonday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 3 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromTuesday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 2 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromTuesday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 4 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromWednesday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 3 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromWednesday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 5 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromThursday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 4 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromThursday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 6 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromFriday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 5 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromFriday',
             CONVERT(CHAR(10), Dateadd(wk, 0, Dateadd(day, 7 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'FirstDayOfWeekStartFromSaturday',
             CONVERT(CHAR(10), Dateadd(wk, 1, Dateadd(day, 6 - Datepart(weekday,
                                                               @CurrentDate
                                                               ),
                                              Datediff(dd, 0, @CurrentDate)
                                              )), 120)
             AS
             'LastDayOfWeekStartFromSaturday'

      SET @CurrentDate=Dateadd(day, 1, @CurrentDate)
  END