Technical: Database – Dimension Modeling – Dimension – Time (Populating DimTime – SQL Server Implementation)

Technical: Database – Dimension Modeling – Dimension – Time (Populating DimTime – SQL Server Implementation)

Introduction

Hopefully, this is the first of many posts that tracks my foray into learning “Online analytical processing” (OLAP).

In this post, we will cover the Time Dimension.

Inspiration

As I read broadly about Data Mart, Data Warehouse, and Dimension Modeling the person that speaks most to me is Ralph Kimball.

The company he founded over shares on the Internet and leaves one with little reason not to read a bit.

Here is a beautifully written piece about the Time Dimension.

Time for Time

http://www.kimballgroup.com/1997/07/10/its-time-for-time/

Schema – Design

We are going to have one Dimension table called as dimTime.  And, two lookup lookup tables that we will use to populate our lone dimension table.

Schema – Design – Calendar – Weekday

dbo.lookupCalendarWeekday

Schema – Design – Calendar – Month

dbo.lookupCalendarMonth

Schema – Design – Calendar – Time

From Microsoft AdventureWorksDW database.

dbo.DimTime

Schema – SQL

Schema – SQL – Calendar – Weekday



set noexec off;
set nocount on;

use [DBLabDW]
go

if object_id('dbo.lookupCalendarWeekday') is not null
begin

	set noexec on;

end
go

create table dbo.lookupCalendarWeekday
(

      [id] tinyint not null

    , [dateAdded] datetime not null
		constraint [defaultlookupCalendarWeekdayDateAdded] default getdate()

    , [addedBy]   nvarchar(100) not null
		constraint [defaultlookupCalendarWeekdayAddedBy] default SYSTEM_USER

    , [dateModified] datetime null
    , [modifiedBy]   nvarchar(100) null

    , [weekday] tinyint not null
    , [sortOrder] tinyint not null 
		constraint [defaultlookupCalendarWeekdaySortOrder] default 0

    , [weekDayInEnglish] nvarchar(100) null
    , [weekDayInFrench] nvarchar(100) null
    , [weekDayInSpanish] nvarchar(100) null

)

ALTER TABLE dbo.lookupCalendarWeekday
    ADD CONSTRAINT [AK_lookupCalendarWeekday_weekday] UNIQUE NONCLUSTERED 
    (
	[weekday] ASC
    )

set noexec off;
go

Quick Points

Here are a couple of quick points:

  • We created a unique  constraint AK_lookupCalendarWeekday_weekday on the dbo.lookupCalendarWeekday table
  • Having this constraint will equip us with the uniqueness requirement that is needed for us to create a foreign key against this table (when we create the Time Dimension table)

Schema – SQL – Calendar – Month



set noexec off;
set nocount on;

use [DBLabDW]
go

if object_id('dbo.lookupCalendarMonth') is not null
begin

	set noexec on;

end
go

create table dbo.lookupCalendarMonth
(

      [id] tinyint not null

    , [dateAdded] datetime not null
		constraint [defaultlookupCalendarMonthDateAdded] default getdate()

    , [addedBy]   nvarchar(100) not null
		constraint [defaultlookupCalendarMonthAddedBy] default SYSTEM_USER

    , [dateModified] datetime null
    , [modifiedBy]   nvarchar(100) null

    , [sortOrder] tinyint not null 
		constraint [defaultlookupCalendarMonthSortOrder] default 0

    , [monthInEnglish] nvarchar(100) null
    , [monthInFrench] nvarchar(100) null
    , [monthInSpanish] nvarchar(100) null

)

ALTER TABLE [dbo].[lookupCalendarMonth]
	add constraint PK_LookupCalendarMonth
	    primary key
	(
		[id]
	)

set noexec off;
go

Schema – SQL – Calendar – dimTime

For the sake of consistency, knowing we are unlikely to do better, and since we do not currently have any need for customization, we will stick with the dimTime structural definition from Microsoft AdventureWorksDW database.



SET NOEXEC OFF
go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

use [DBLabDW]
go

if object_id('dbo.DimTime') is not null
begin

	set noexec on

end
go

CREATE TABLE [dbo].[DimTime]
(
	[TimeKey] [int] IDENTITY(1,1) NOT NULL,
	[FullDateAlternateKey] [datetime] NULL,
	[DayNumberOfWeek] [tinyint] NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NULL,
	[SpanishDayNameOfWeek] [nvarchar](10) NULL,
	[FrenchDayNameOfWeek] [nvarchar](10) NULL,
	[DayNumberOfMonth] [tinyint] NULL,
	[DayNumberOfYear] [smallint] NULL,
	[WeekNumberOfYear] [tinyint] NULL,
	[EnglishMonthName] [nvarchar](10) NULL,
	[SpanishMonthName] [nvarchar](10) NULL,
	[FrenchMonthName] [nvarchar](10) NULL,
	[MonthNumberOfYear] [tinyint] NULL,
	[CalendarQuarter] [tinyint] NULL,
	[CalendarYear] [char](4) NULL,
	[CalendarSemester] [tinyint] NULL,
	[FiscalQuarter] [tinyint] NULL,
	[FiscalYear] [char](4) NULL,
	[FiscalSemester] [tinyint] NULL,
    CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED 
    ( 
	[TimeKey] ASC
    )

WITH (
          PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
     ) 
     ON [PRIMARY],
 CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED 
(
	[FullDateAlternateKey] ASC
)
WITH (
           PAD_INDEX = OFF
         , STATISTICS_NORECOMPUTE = OFF
         , IGNORE_DUP_KEY = OFF
         , ALLOW_ROW_LOCKS = ON
         , ALLOW_PAGE_LOCKS = ON
     ) 
     ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DimTime]
	add constraint FK_DimTime_DayNumberOfWeek
	    foreign key
	(
		[DayNumberOfWeek]
	)
	references dbo.lookupCalendarWeekday
	(
		[weekday]
	)
GO

ALTER TABLE [dbo].[DimTime]
	add constraint FK_DimTime_MonthNumberOfYear
	    foreign key
	(
		[MonthNumberOfYear]
	)
	references dbo.lookupCalendarMonth
	(
		[id]
	)
GO

SET NOEXEC OFF
go

SET ANSI_PADDING ON
GO

Quick Points

Here are a couple of quick points:

  • We are creating a foreign key FK_DimTime_DayNumberOfWeek on the dbo.DimTime table
  • The foreign key joins the resident DayNumberOfWeek column to the weekday table using the unique column (weekday)

 

Populate Date Table

SQL – Populate – Calendar – Weekday

There are a couple of important points:



use [DBLabDW]
go

/*
	dbo.lookupCalendarWeekday Population
*/

/*
	French Calendar Vocabulary
	http://french.about.com/od/vocabulary/a/calendar.htm

*/

/*
	Spanish - My Calendar
	http://www.bbc.co.uk/schools/primarylanguages/spanish/my_calendar/

*/

BEGIN TRAN;

	/*
		http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
	*/
	-- Define the CTE expression name and column list.
	WITH cteWeekday
	(
		  [id]
                , [weekday]
		, [weekDayInEnglish]
		, [weekDayInFrench]
		, [weekDayInSpanish]
		, [sortOrder]
	)
	AS
	-- Define the CTE query.
	(
		SELECT 1, 1, 'Sunday', 'dimanche', 'domingo', 1
		UNION
		SELECT 2, 2, 'Monday', 'lundi', 'lunes', 2
		UNION
		SELECT 3, 3, 'Tuesday', 'mardi', 'martes', 3
		UNION
		SELECT 4, 4, 'Wednesday', 'mercredi', 'miércoles', 4
		UNION
		SELECT 5, 5, 'Thursday', 'jeudi', 'jueves', 5
		UNION
		SELECT 6, 6, 'Fridday', 'vendredi', 'viernes', 6
		UNION
		SELECT 7, 7, 'Saturday', 'samedi', 'sábado', 7
	)

	/*
		Merge Statement Definition
		http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
	*/
	merge dbo.lookupCalendarWeekday as tblTarget
	using cteWeekday as tblSource

	ON (tblTarget.[id] = tblSource.[id]) 

		WHEN NOT MATCHED BY TARGET
			then Insert 
			(
				  [id]
                                , [weekday]
				, [weekDayInEnglish]
				, [weekDayInFrench]
				, [weekDayInSpanish]
			)
			values
			(
				  tblSource.[id]
                                , tblSource.weekday
				, tblSource.[weekDayInEnglish]
				, tblSource.[weekDayInFrench]
				, tblSource.[weekDayInSpanish]
			)

		WHEN MATCHED and 
			(

			    (
				isNull(tblTarget.[weekday], -1) 
				!= isNull(tblSource.[weekday], -1)
			    )

                         or
			    (
				isNull(tblTarget.[sortOrder], -1) 
				!= isNull(tblSource.[sortOrder], -1)
			    )

			  or
			   (
				isNull(tblTarget.[weekDayInEnglish], '') 
				!= isNull(tblSource.[weekDayInEnglish], '')
			  )

			  or
			  (
				isNull(tblTarget.[weekDayInFrench], '') 
				!= isNull(tblSource.[weekDayInFrench], '')
			  )

			  or
			 (
				isNull(tblTarget.[weekDayInSpanish], '') 
					!= isNull(tblSource.[weekDayInSpanish], '')
			 )

		)

		then Update set
                           [weekday] = tblSource.[weekday]
			 , [sortOrder] = tblSource.[sortOrder]
			 , [weekDayInEnglish] = tblSource.[weekDayInEnglish]
			 , [weekDayInFrench] = tblSource.[weekDayInFrench]
			 , [weekDayInSpanish] = tblSource.[weekDayInSpanish]
			 , [dateModified] = getdate()
			 , [modifiedBy] = SYSTEM_USER
	;

COMMIT TRAN;

SQL – Populate – Calendar – Month



use [DBLabDW]
go

/*
	dbo.lookupCalendarMonth Population
*/

/*
	French Calendar Vocabulary
	http://french.about.com/od/vocabulary/a/calendar.htm

*/

/*
	Spanish - My Calendar
	http://www.bbc.co.uk/schools/primarylanguages/spanish/my_calendar/

*/

/*
	delete from dbo.lookupCalendarMonth;
*/
BEGIN TRAN;

	/*
		http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
	*/
	-- Define the CTE expression name and column list.
	WITH ctemonth
	(
		  [id]
		, [monthInEnglish]
		, [monthInFrench]
		, [monthInSpanish]
		, [sortOrder]
	)
	AS
	-- Define the CTE query.
	(
		SELECT 1, 'January', 'janvier', 'enero', 1
		UNION
		SELECT 2, 'February', 'février', 'febrero', 2
		UNION
		SELECT 3, 'March', 'mars', 'marzo', 3
		UNION
		SELECT 4, 'April', 'avril', 'abril', 4
		UNION
		SELECT 5, 'May', 'mai', 'mayo', 5    
		UNION
		SELECT 6, 'June', 'juin', 'junio', 6
		UNION
		SELECT 7, 'July', 'juillet', 'julio', 7
		UNION
		SELECT 8, 'August', 'août', 'agosto', 8
		UNION
		SELECT 9, 'September', 'septembre', 'septiembre', 9
		UNION
		SELECT 10, 'October', 'octobre', 'octubre', 10
		UNION
		SELECT 11, 'November', 'novermbre', 'noviembre', 11
		UNION
		SELECT 12, 'December', 'décembre', 'diciembre', 12
	)

	/*
		Merge Statement Definition
		http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx
	*/
	merge dbo.lookupCalendarMonth as tblTarget
	using ctemonth as tblSource

	ON (tblTarget.[id] = tblSource.[id]) 

		WHEN NOT MATCHED BY TARGET
			then Insert 
			(
				  [id]
				, [monthInEnglish]
				, [monthInFrench]
				, [monthInSpanish]
			)
			values
			(
				  tblSource.[id]
				, tblSource.[monthInEnglish]
				, tblSource.[monthInFrench]
				, tblSource.[monthInSpanish]
			)

		WHEN MATCHED and 
			(

				(
					isNull(tblTarget.[sortOrder], -1) 
				      != isNull(tblSource.[sortOrder], -1)
				)

				or
				(
				      isNull(tblTarget.[monthInEnglish], '') 
				     != isNull(tblSource.[monthInEnglish], '')
				)

				or
				(
					isNull(tblTarget.[monthInFrench], '') 
					!= isNull(tblSource.[monthInFrench], '')
				)

				or
				(
				     isNull(tblTarget.[monthInSpanish], '') 
				      != isNull(tblSource.[monthInSpanish], '')
				)

			)

			then Update set
				   [sortOrder] = tblSource.[sortOrder]
				 , [monthInEnglish] = tblSource.[monthInEnglish]
				 , [monthInFrench] = tblSource.[monthInFrench]
				 , [monthInSpanish] = tblSource.[monthInSpanish]
				 , [dateModified] = getdate()
				 , [modifiedBy] = SYSTEM_USER
	;

	select * 
        from dbo.lookupCalendarMonth

COMMIT TRAN;

SQL – Populate – DimTime

Pasted below is the sample code for populating the DimTime table.

As always, I left out the hardest part; which is how to calculate Fiscal Dates when it does not match the Calendar Dates.

Hopefully, will steal some cost later on and use that.

Here is the code:



set nocount on
go

/*
	How to join 2 tables without an ON clause
	http://stackoverflow.com/questions/15508142/how-to-join-2-tables-without-an-on-clause

*/
use [DBLabDW]
go

declare @dateBegin		datetime
declare @dateEnd		datetime
declare @dateCurrent	datetime

set @dateBegin = '1/1/2014'
set @dateEnd = '12/31/2014'

set @dateCurrent = @dateBegin

begin tran

   while (@dateCurrent <= @dateEnd)
   begin

	insert into dbo.DimTime
	(
             [FullDateAlternateKey]
	   , [DayNumberOfWeek]

	   , [EnglishDayNameOfWeek]
	   , [SpanishDayNameOfWeek]
	   , [FrenchDayNameOfWeek]
	   , [DayNumberOfMonth]
	   , [DayNumberOfYear]
	   , [WeekNumberOfYear]

	   , [EnglishMonthName]
	   , [SpanishMonthName]
	   , [FrenchMonthName]

	   , [MonthNumberOfYear]

	    , [CalendarQuarter]
	    , [CalendarYear]
	    , [CalendarSemester]

	    , [FiscalQuarter]
	    , [FiscalYear]
	    , [FiscalSemester]

	)
	select
		  @dateCurrent as [FullDateAlternateKey]
		, datepart(weekday, @dateCurrent) --[DayNumberOfWeek]
		, tblCalendarWeekday.[weekDayInEnglish]
		, tblCalendarWeekday.[weekDayInSpanish]
		, tblCalendarWeekday.[weekDayInFrench]
		, datepart(day, @dateCurrent) --[DayNumberOfMonth]
		, datepart(dayofYear, @dateCurrent) --[DayNumberOfMonth]
		, datepart(week, @dateCurrent) --[DayNumberOfMonth]
		, tblCalendarMonth.[monthInEnglish]
		, tblCalendarMonth.[monthInSpanish]
		, tblCalendarMonth.[monthInFrench]
		, datepart(month, @dateCurrent) -- [MonthNumberOfYear]

		, case 

		     when datepart(month, @dateCurrent) between 1 and 3 then 1
		     when datepart(month, @dateCurrent) between 4 and 6 then 2
		     when datepart(month, @dateCurrent) between 7 and 9 then 3
		     when datepart(month, @dateCurrent) between 10 and 12 then 4
		     else -1

		  end as [CalendarQuarter]

		, datepart(year, @dateCurrent)
		     as [CalendarYear]

		, case 
			when datepart(month, @dateCurrent) between 1 and 6 then 1
			when datepart(month, @dateCurrent) between 7 and 12 then 2
			else -1

		  end as [CalendarSemester]

		, case 

			when datepart(month, @dateCurrent) between 1 and 3 then 1
			when datepart(month, @dateCurrent) between 4 and 6 then 2
			when datepart(month, @dateCurrent) between 7 and 9 then 3
		        when datepart(month, @dateCurrent) between 10 and 12 then 4
			else -1

		  end	as [FiscalQuarter]

		, datepart(year, @dateCurrent)
			 as [FiscalYear]

		, case 

			when datepart(month, @dateCurrent) between 1 and 6 then 1
			when datepart(month, @dateCurrent) between 7 and 12 then 2
		        else -1
		  end as [FiscalSemester]

		from  [dbo].[lookupCalendarWeekday] tblCalendarWeekday

			cross join [dbo].[lookupCalendarMonth] tblCalendarMonth

		where tblCalendarWeekday.[weekday] = datepart(weekday, @dateCurrent)

		and   tblCalendarMonth.[id] = datepart(month, @dateCurrent)

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

	end

	select * 
	from   dbo.DimTime

commit tran

go

Quick Points

Here are a couple of quick points:

  • As always, there is a very spirited debate that ran out into the open (Internet) surrounding the differences between datepart\week and datepart\iso_week and the role of locale\languages and the inferred DATE_FIRST setting

Conclusion

The Time Dimension is foundational to any discussion about Data Warehousing modeling.

In SQL Server Analysis Services (SSAS), one can either explicitly create it or have SSAS create it.

Best Practices suggests that one should explicitly create and populate it.

If you follow Best Practice (BP), please keep in mind the countries and locales that your system will be used.

And, whether you will have systems for each locale or whether you will use a centralized system.

Listening

Listening to my favorite story teller:

Kenny Chesney – The Boys of Fall

http://www.youtube.com/watch?v=AlXDo5WhQXI

References

References – Dimension Modeling

References – Calendar – Internationalization

References – Transact SQL

References – Transact SQL / Example

References – Transact SQL / DateFirst and DatePart 

References – Transact SQL / Compare ISO against ISO_WEEK

One thought on “Technical: Database – Dimension Modeling – Dimension – Time (Populating DimTime – SQL Server Implementation)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s