OLAP Time Dimension 1

[LurkAzusa]

Can someone refer me to a source for an good example of a complete time dimension table. What I am looking for is a table that will link with the Invoice_Date field of the fact table. It needs to include prior month, prior qtr, prior year dimensions, etc.

 

[JayKusch]

You might try looking on this site, or join there forum. have seen some good leads on your question there.

http://www.ralphkimball.com.

AND

http://www.dwinfocenter.org

Thanks

J. Kusch

 

[phillyon]

This is the t-sql cursor that I use to create a calendar table for use as a time dimension - hope it helps:

if exists (select * from sysobjects where id = object_id(N'[dbo].[MXCUSTOMCalendarTB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MXCUSTOMCalendarTB]
GO

CREATE TABLE [dbo].[MXCUSTOMCalendarTB] (
[DayID] [int] IDENTITY(1,1) NOT NULL ,
[DayStart] [datetime] NOT NULL ,
[DayEnd] [datetime] NOT NULL ,
[MonthNumber] [tinyint] NOT NULL ,
[QuarterNumber] [tinyint] NOT NULL ,
[CalendarYear] [smallint] NOT NULL,
[FinancialYear] [smallint] NOT NULL,
[WeekStart] [datetime] NOT NULL ,
[WeekEnd] [datetime] NOT NULL ,
[FinWeekStart] [datetime] NOT NULL ,
[FinWeekEnd] [datetime] NOT NULL ,
[MonthStart] [datetime] NOT NULL ,
[MonthEnd] [datetime] NOT NULL ,
[QuarterStart] [datetime] NOT NULL ,
[QuarterEnd] [datetime] NOT NULL ,
[YearStart] [datetime] NOT NULL ,
[YearEnd] [datetime] NOT NULL
)
GO

DECLARE @DateStart Datetime,
@WeekStart Datetime,
@WeekEnd Datetime,
@FinWeekStart Datetime,
@FinWeekEnd Datetime,
@MonthStart Datetime,
@MonthEnd Datetime,
@QuarterStart Datetime,
@QuarterEnd Datetime,
@YearStart Datetime,
@YearEnd Datetime,
@CalYear SmallInt,
@FinYear SmallInt


SELECT @DateStart = '01 Jan 1990' --where you want to start your dates from

WHILE DATEPART(Year, @DateStart) < 2051 --where you want to finish your dates
BEGIN
SELECT @YearStart = '01 Jan ' + Convert(Varchar, DatePart(Year, @DateStart))
SELECT @YearEnd = DateAdd(Day, -1, DateAdd(Year, 1, @YearStart)) + ' 23:59:59'
SELECT @MonthStart = '01 ' + Datename(Month, @DateStart) + ' ' + Convert(Varchar, DatePart(Year, @DateStart))
SELECT @MonthEnd = DateAdd(Day, -1, DateAdd(Month, 1, @MonthStart)) + ' 23:59:59'
SELECT @QuarterStart = '01 ' +
(CASE WHEN Datepart(Quarter, @DateStart) = 1 THEN 'Jan '
WHEN Datepart(Quarter, @DateStart) = 2 THEN 'Apr '
WHEN Datepart(Quarter, @DateStart) = 3 THEN 'Jul '
ELSE 'Oct ' END) + Convert(Varchar, DatePart(Year, @DateStart))
SELECT @QuarterEnd = DateAdd(Day, -1, DateAdd(Quarter, 1, @QuarterStart)) + ' 23:59:59'
SELECT @CalYear = Datepart(Year, @DateStart)
SELECT @FinYear = CASE WHEN Datepart(Month, @DateStart) < 7 THEN @CalYear ELSE @CalYear + 1 END
--weekstart based on Sunday
SELECT @WeekStart = CASE WHEN DATEPART(WeekDay, @DateStart) = 1 THEN @DateStart
ELSE DATEADD(Day, - (DATEPART(WeekDay, @DateStart) - 1), @DateStart) END
SELECT @WeekEnd = DATEADD(Day, 6, @WeekStart) + ' 23:59:59'
--finweekstart based on Monday
SELECT @FinWeekStart = DATEADD(DAy, 1, @WeekStart)
SELECT @FinWeekEnd = DATEADD(Day,4, @FinWeekStart) + ' 23:59:59'

INSERT INTO MXCUSTOMCalendarTB ( DayStart,
DayEnd,
MonthNumber,
QuarterNumber,
CalendarYear,
FinancialYear,
WeekStart,
WeekEnd,
FinWeekStart,
FinWeekEnd,
MonthStart,
MonthEnd,
QuarterStart,
QuarterEnd,
YearStart,
YearEnd
)
VALUES ( @DateStart,
@DateStart + ' 23:59:59',
Datepart(Month, @DateStart),
Datepart(Quarter, @DateStart),
@CalYear,
@FinYear,
@WeekStart,
@WeekEnd,
@FinWeekStart,
@FinWeekEnd,
@MonthStart,
@MonthEnd,
@QuarterStart,
@QuarterEnd,
@YearStart,
@YearEnd
)

SELECT @DateStart = DateAdd(Day, 1, @DateStart)
END

ALTER TABLE [dbo].[MXCUSTOMCalendarTB] WITH NOCHECK ADD
CONSTRAINT [PK_MXCUSTOMCalendarTB] PRIMARY KEY CLUSTERED
(
[DayID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_MXCUSTOMCalendarTB] ON [dbo].[MXCUSTOMCalendarTB]([DayStart], [DayEnd]) ON [PRIMARY]
GO

CREATE INDEX [IX_MXCUSTOMCalendarTB_1] ON [dbo].[MXCUSTOMCalendarTB]([MonthStart]) ON [PRIMARY]
GO

CREATE INDEX [IX_MXCUSTOMCalendarTB_2] ON [dbo].[MXCUSTOMCalendarTB]([MonthEnd]) ON [PRIMARY]
GO

CREATE INDEX [IX_MXCUSTOMCalendarTB_3] ON [dbo].[MXCUSTOMCalendarTB]([QuarterStart]) ON [PRIMARY]
GO