Time dimension in SSAS

In SSAS 2005 and 2008, it exists 4 types of built-in time dimensions. All are based on the server time.

The problem with these server time dimension is the flexibility. Indeed no way to customize the members name or change the name of these. You will always have something like 'Friday 1 January 2010' as day.



To have a more usable time dimension, you will have to create a physical time dimension and import it into your datasource view.



This time dimension will contain then the naming you want.



Here the code I built to fill in my Calendar table which is the source table to my time dimension.



--Creation of the table

CREATE TABLE [dbo].[Dim_Calendar](
[DateKey] [int] NOT NULL, --Surrogate key
[FullDate] [datetime] NULL, --DD/MM/YYYY 00:00:00.000
[WeekNumInYear] [tinyint] NULL, -- The order number of the week in the year (gregorian calendar)
[Month] [tinyint] NULL, -- Month number (1-->12)
[MonthAbbrev] [char](3) NULL, -- Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Spe,Oct,Nov,Dec
[MonthBeginDateKey] [int] NULL, -- The surrogate Key of the first day in the month
[Quarter] [tinyint] NULL, -- Quarter number (1,2,3,4)
[QuarterName] [char](2) NULL, -- Q1,Q2,Q3,Q4
[QuarterYear] [char](7) NULL, -- Q1 2009,Q2 2009,etc.
[Year] [smallint] NULL, -- Year
[WeekNumOverall] [smallint] NULL, -- key identifier of the week
[WeekName_W] [char](4) NULL,
[ISOWeek] [smallint] NULL,
CONSTRAINT [PK_Dim_Calendar] PRIMARY KEY NONCLUSTERED
(
[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]

-- Create function to get the ISO Week
CREATE FUNCTION [dbo].[fn_Get_ISO_Week_Number](

@date DATETIME )

RETURNS INT

AS

BEGIN

/***



The code here is from the article http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/ by Christoffer Hedgate 20 Jan 2005.

Note, change 01-04 in Christoffer's article because dates need to be passed as YYYYMMDD in SQL Server to avoid any inconsistency.



****/



RETURN
( CASE

-- Exception where @date is part of week 52 (or 53) of the previous year

WHEN @date < CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

END

THEN

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

END,

@date

) / 7) + 1



-- Exception where @date is part of week 1 of the following year

WHEN @date >= CASE (DATEPART(dw, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

END

THEN 1



ELSE

-- Calculate the ISO week number for all dates that are not part of the exceptions above

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

END,

@date

) / 7) + 1

END

)

END


-- Create the stored procedure to fill the calendar table
CREATE PROCEDURE [dbo].[Dim_Calendar_Generation]

@StartDate Datetime,
@EndDate Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


SET DATEFIRST 1

DECLARE
@DateKey int,
@WeekNumInYear tinyint,
@Month tinyint,
@MonthAbbrev char(3),
@MonthBeginDateKey int,
@Quarter tinyint,
@QuarterName char(2),
@QuarterYear char(7),
@Year smallint,
@WeekNumOverall smallint ,
@WeekName_W char(4),
@ISOWeek smallint

WHILE @StartDate <= @EndDate
BEGIN
SET @DateKey = CASE
WHEN (SELECT COUNT(*) FROM Dim_Calendar) > 0) THEN (SELECT MAX(DateKey) FROM Dim_Calendar) + 1
ELSE 1 -- Dim_calendar is empty
END
SET @WeekNumInYear = DATEPART(week,@StartDate)
SET @Month = MONTH(@StartDate)
SET @MonthAbbrev = SUBSTRING(DATENAME(month,@StartDate),1,3)
SET @MonthBeginDateKey =
CASE
WHEN
(
SELECT COUNT(MonthBeginDateKey)
FROM dbo.Dim_Calendar
WHERE MONTH(@StartDate) = MONTH(FullDate)
AND YEAR(@StartDate) = YEAR(FullDate)
AND DAY(FullDate) = 1
) > 0
THEN
(
SELECT DISTINCT MonthBeginDateKey
FROM dbo.Dim_Calendar
WHERE MONTH(@StartDate) = MONTH(FullDate)
AND YEAR(@StartDate) = YEAR(FullDate)
AND DAY(FullDate) = 1
) -- @StartDate is not the first date in the month
ELSE @DateKey -- @StartDate is the first day of the month
END

SET @Quarter = DATEPART(quarter,@StartDate)
SET @QuarterName = 'Q' + DATENAME(quarter,@StartDate)
SET @QuarterYear = 'Q' + DATENAME(quarter,@StartDate) + ' ' + CAST(YEAR(@StartDate) AS VARCHAR(4))

SET @Year = YEAR(@StartDate)
SET @WeekNumOverall =
CASE
WHEN
(
SELECT COUNT(WeekNumOverall)
FROM Dim_Calendar
WHERE YEAR(@StartDate) = YEAR(FullDate)
AND MONTH(@StartDate) = MONTH(FullDate)
AND @WeekNumInYear = WeekNumInYear
) > 0
THEN
(
SELECT DISTINCT WeekNumOverall
FROM Dim_Calendar
WHERE YEAR(@StartDate) = YEAR(FullDate)
AND MONTH(@StartDate) = MONTH(FullDate)
AND @WeekNumInYear = WeekNumInYear
)
WHEN (SELECT COUNT(*)FROM Dim_Calendar) = 0 THEN 1 -- Dim_Calendar is empty
ELSE (SELECT MAX(WeekNumOverall) FROM Dim_Calendar) + 1 -- First week of the month
END
SET @WeekName =
CASE
WHEN DATEPART(week,@StartDate) < 10
THEN 'W 0'+ CAST(@WeekNumInYear AS VARCHAR(4))
ELSE 'W ' + CAST(@WeekNumInYear AS VARCHAR(4))
END

SET @ISOWeek = (SELECT dbo.fn_Get_ISO_Week_Number(@StartDate))

INSERT INTO Dim_Calendar
(
[DateKey]
,[FullDate]
,[WeekNumInYear]
,[Month]
,[MonthAbbrev]
,[MonthBeginDateKey]
,[Quarter]
,[QuarterName]
,[QuarterYear]
,[Year]
,[WeekNumOverall]
,[WeekName_W]
,[ISOWeek]
)
VALUES
(
@DateKey
,@StartDate
,@WeekNumInYear
,@Month
,@MonthAbbrev
,@MonthBeginDateKey
,@Quarter
,@QuarterName
,@QuarterYear
,@Year
,@WeekNumOverall
,@WeekName_W
,@ISOWeek
)

SET @StartDate = @StartDate + 1
END

END