Hi,
I am developing a datawarehouse for my company but am a little stuck on the best practice when it comes to the Date Dimension. Should the date Dimension have all the days of the year hand typed into it and then the ETL tool check the date and make the correct link? or should the date dimension get a new entry every time a new customer is uploaded to the warehouse, therefore there may be 1000 entries all with 8th March 2004? or am I completely missing somthing else? any ideas?

Cheers
Magnus

I beleive the best method is to have all the days of the year in your table. You would of course do it "programatically" not by hand. Also; on a DW project I worked on - our ETL tool would create today's date for the table instead of creating all future days of the year - but either way - you should have an entry for each day.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

You can use tools like DimensionModeler to generate your time dimension data and load it into your warehouse tables.

Check DimensionModelers web-site for functionality at http://www.dssdev.com/dm/dmoverview.html

Hope, this helps.

I believe that SQl Server Analysis Manager will create a date dimmension for you complete with your values. It is some kind of wizard. Right click on Shared Dimmensions and select New Dimmension / Wizard.



Sal

Hi,

If you're using SQL Server 2005 you have 2 options:
-Create the table in the DW
-User the time server dimension built-in dimension.

The second option seems simple once that you don't have to create the dimension table, but I advice a little test to figure out if it meets your requirements. If you go this way remember to create a date-time column in your facts. I went this way, and am now seeking help because it doesn't meet my requirements.

The first option means that you'll have to create and populate the table. Chose a start and end date. There is a base script for creating and filling up the table:
"
CREATE TABLE [dbo].[DimTime] (
            [TimeSurKey] [int] IDENTITY (0, 1) NOT NULL ,
            [TimeFull] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [TimeHour] [tinyint] NOT NULL ,
            [TimeMinute] [tinyint] NOT NULL ,
            [TimeSecond] [tinyint] NOT NULL
) ON [PRIMARY]

GO


-----------------------------------------------------



insert into DimTime
select '00:00:00', 0, 0, 0

declare @ctr int
set @ctr =1
declare @actTime datetime
set @actTime = '2005-01-01 00:00:00'
while @ctr < 86400
begin
      set @actTime = @actTime + '00:00:01'
      insert into DimTime
      select convert(varchar,@actTime,8), datepart(hh,@actTime), datepart(n, @actTime), datepart(s, @actTime)
set @ctr = @ctr + 1
end

"

After having made the table you have two choices:
-to use the built-in time dimension in SQL Server (do not confuse with TIME-SERVER);
-to create a regular dimension.

The balance is always between flexibility and ease of use.

Hi,

If you're using SQL Server 2005 you have 2 options:
-Create the table in the DW
-User the time server dimension built-in dimension.

The second option seems simple once that you don't have to create the dimension table, but I advice a little test to figure out if it meets your requirements. If you go this way remember to create a date-time column in your facts. I went this way, and am now seeking help because it doesn't meet my requirements.

The first option means that you'll have to create and populate the table. Chose a start and end date. There is a base script for creating and filling up the table:
"
CREATE TABLE [dbo].[DimTime] (
            [TimeSurKey] [int] IDENTITY (0, 1) NOT NULL ,
            [TimeFull] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [TimeHour] [tinyint] NOT NULL ,
            [TimeMinute] [tinyint] NOT NULL ,
            [TimeSecond] [tinyint] NOT NULL
) ON [PRIMARY]

GO


-----------------------------------------------------



insert into DimTime
select '00:00:00', 0, 0, 0

declare @ctr int
set @ctr =1
declare @actTime datetime
set @actTime = '2005-01-01 00:00:00'
while @ctr < 86400
begin
      set @actTime = @actTime + '00:00:01'
      insert into DimTime
      select convert(varchar,@actTime,8), datepart(hh,@actTime), datepart(n, @actTime), datepart(s, @actTime)
set @ctr = @ctr + 1
end

"

After having made the table you have two choices:
-to use the built-in time dimension in SQL Server (do not confuse with TIME-SERVER);
-to create a regular dimension.

The balance is always between flexibility and ease of use.