Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Mastering Dimensions of Time

By PhyData DBA,

Recently I had a project to create a Data Warehouse for our small business to use for reporting and analysis.

We needed date and time Dimension tables to store values for any date or time in our datasources.  We had no requirements to support multiple regions or languages, but needed a schema that could be updated easily and expanded on if needed.  There was also a requirement that the date table row id value be based on the actual date stored in each row.

We decided on the following two tables:

CREATE TABLE [dbo].[DimDate](
       [DateKey] [int] NOT NULL,
       [FullDateAlternateKey] [date] NOT NULL,
       [DateString] [varchar](10) NULL,
       [DayOfWeek] [tinyint] NOT NULL,
       [DayOfWeekName] [nvarchar](10) NOT NULL,
       [DayOfMonth] [tinyint] NOT NULL,
       [DayOfYear] [smallint] NOT NULL,
       [WeekOfYear] [tinyint] NOT NULL,
       [MonthName] [nvarchar](10) NOT NULL,
       [MonthOfYear] [tinyint] NOT NULL,
       [CalendarQuarter] [tinyint] NOT NULL,
       [CalendarYear] [smallint] NOT NULL,
       [IsWeekend] [bit] NOT NULL,
       [IsLeapYear] [bit] NOT 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]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DimDate] ADD  CONSTRAINT [DF_DimDate_IsWeekend]  DEFAULT ((0)) FOR [IsWeekend]
GO
ALTER TABLE [dbo].[DimDate] ADD  CONSTRAINT [DF_DimDate_IsLeapYear]  DEFAULT ((0)) FOR [IsLeapYear]
GO
 CREATE TABLE [dbo].[DimTime](
       [TimeKey] [int] NOT NULL,
       [TimeAltKey] [time](0) NULL,
       [HourOfDay] [tinyint] NULL,
       [MinuteOfHour] [tinyint] NULL,
       [SecondOfMinute] [tinyint] NULL,
       [TimeString] [varchar](8) NULL,
 CONSTRAINT [PK_DimTime] 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]
) ON [PRIMARY]

Now we need to populate the time and date dimension tables with some data.  We do not need every possible date in the table, but at least all the possible dates that could be in our data set.  Let’s make the scripts re-useable.  When executed with optional parameters they can either fill in gaps, populate entire ranges, or just a specific date. This should allow us to fill and then update the Date dimension table with only the data that is needed.  Since the time table has values for just one 24 hour period, its procedure should fill the entire table at once. 

Here is what we came up with. To populate DimTime:

-- Declare and set variables for loop
Declare
@StartTime time(0),
@EndTime time(0),
@Time time(0),
@Interval int

Set @StartTime = '00:00:00'
Set @EndTime = '23:59:59'
Set @Time = @StartTime
Set @Interval = 1

-- Loop through Times
WHILE @Time <= @EndTime
BEGIN

    -- Insert record in dimension table if Time does not exist
    IF NOT EXISTS ( SELECT 'X' from dbo.DimTime (NOLOCK) where TimeAltKey = @Time )
    BEGIN
  
    INSERT Into DimTime with(rowlock)
    (
    TimeKey,
    TimeAltKey,
    [HourOfDay],
    [MinuteOfHour],
    SecondOfMinute,
    TimeString
    )
    Values
    (
    @Interval,
    @Time,
    datename(hh,@Time),
    datename(minute,@time),
    datename(second,@time),
    CONVERT(varchar(8), @time)
    )

    END

    -- Goto next second
    Select @Time = Dateadd(ss,1, @Time)
    Select @Interval = @Interval + 1

END

Notice that there is a section that checks to see if the value being added already exists.  We also made the script so that if we wanted to add more precision to the time table later, that would be supported.  It is recommended to create a new time dimension table if you are going to increase the precision of your time values.  This will allow you to keep the existing surrogate key values from the initial time dimension table in your Data warehouse, or at the least convert them to the correct values in the new time dimension table.

Now it is time to populate the Date table with all possible dates in our data source.  A quick check through all the date values in the data source shows that we need to support dates between January 2005 and December 2016. Here is our script for populating these values:

-- Declare and set variables for loop
Declare
@StartDate date,
@EndDate date,
@Date date

Set @StartDate = '2005-01-01'
Set @EndDate = '2016-01-01'
Set @Date = @StartDate

-- Loop through dates
WHILE @Date <= @EndDate
BEGIN
    -- Check for leap year
    DECLARE @IsLeapYear BIT
    IF ((Year(@Date) % 4 = 0) AND (Year(@Date) % 100 != 0 OR Year(@Date) % 400 = 0))
    BEGIN
        SELECT @IsLeapYear = 1
    END
    ELSE
    BEGIN
        SELECT @IsLeapYear = 0
    END
    -- Check for weekend
    DECLARE @IsWeekend BIT
    IF (DATEPART(dw, @Date) = 1 OR DATEPART(dw, @Date) = 7)
    BEGIN
        SELECT @IsWeekend = 1
    END
    ELSE
    BEGIN
        SELECT @IsWeekend = 0
    END

    -- Insert record in dimension table if date does not exist
    IF NOT EXISTS ( SELECT 'X' from dbo.DimDate (NOLOCK) where FullDateAlternateKey = @Date )
    BEGIN
   
    INSERT Into DimDate with(rowlock)
    (
    DateKey,
    FullDateAlternateKey,
    DateString,
    [DayofWeek],
    [Dayofweekname],
    [DayOfMonth],
    [DayOfYear],
    [WeekOfYear],
    [MonthName],
    MonthOfYear,
    [CalendarQuarter],
    [CalendarYear],
    [IsWeekend],
    [IsLeapYear]
    )
    Values
    (
    Convert(INT, CONVERT(varchar(10), @Date, 112)),
    @Date,
    CONVERT(varchar(10), @Date, 105),
    DATEPART(dw, @Date),
    DATENAME(dw, @Date),
    Day(@Date),
    DATEPART(dy, @Date),
    DATEPART(wk, @Date),
    DATENAME(mm, @Date),
    DATEPART(mm, @Date),
    DATENAME(qq, @Date),
    Year(@Date),
    @IsWeekend,
    @IsLeapYear
    )
   
    END
    -- Goto next day
    Set @Date = dateadd(day,1,@Date )

END

This script is not only fast and updateable, notice how flexible it is.  We also made the insert loop check for and skip existing values that are in the supplied date range.

There might be several other ways of doing this without using a Row By Row script. We could not find one that was as easy to read and understand, or that would be as modifiable and portable as this one. Also, since we are not talking about millions of rows it was faster in a big picture sort of way to do it like this.

I hope that others find this article and the information in it as useful as we did.  Enjoy!

NOTE: This script creates the Date Dimension table sets the Primary Key and clustered Index of the table to the column called [DateKey].  This is just what worked well for us.  You may find better results by changing this, but that is up to you.

Total article views: 7184 | Views in the last 30 days: 28
 
Related Articles
FORUM

Cube Browser not displaying data for selected dimensions

Cube Browser not displaying data for selected dimensions

BLOG

Degenerate Dimensions

Degenerate dimensions, also called fact dimensions, are standard dimensions that are constructed fro...

FORUM

How to Convert nx1 dimension table to 1x1 dimension table

How to Convert nx1 dimension table to 1x1 dimension table

FORUM

Dimension key errors despite having selected Ignore Errors

SSAS Cube Processing job gives dimension key errors even when Ignore Errors is selected

FORUM

Begin Transaction question

begin tran without commit/rollback

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones