No of ways & Strategy to Load Date & Time Dimension Tables?

Question

Hi,

Please can suggest the no of ways & best strategy to laod Date & Time Dimension Tables?

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 1

 I got below Logic:

Create Table for Date Dimension	USE [MYDB] GO /****** Object:  Table [STAR].[DIM_DATE]    Script Date: 12/09/2012 00:28:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [STAR].[DIM_DATE](       [DATE_KEY] [int] NOT NULL,       [DATE] [datetime] NOT NULL,       [DATE_DESC] [varchar](20) NULL,       [DAYOFWEEK] [tinyint] NULL,       [DAYNAMEOFWEEK] [varchar](10) NULL,       [DAYOFMONTH] [tinyint] NULL,       [DAYOFYEAR] [smallint] NULL,       [WEEKOFYEAR] [tinyint] NULL,       [MONTH_NAME] [varchar](10) NULL,       [MONTHOFYEAR] [tinyint] NULL,       [LASTDAYOFMONTH_IND] [varchar](1) NULL,       [CAL_QTR] [tinyint] NULL,       [CAL_YR] [smallint] NOT NULL,       [HOLIDAY_IND] [varchar](1) NULL,       [REC_UPDT_DT] [datetime] NOT NULL CONSTRAINT [DF__DIMDATE__REC_UPD__7E6CC920]  DEFAULT (getdate()),       [REC_UPDT_USER] [varchar](30) NOT NULL CONSTRAINT [DF__DIMDATE__REC_UPD__7F60ED59]  DEFAULT (user_name()), CONSTRAINT [PK_DIMDATE] PRIMARY KEY CLUSTERED (       [DATE_KEY] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [MYDB_USERDATA] ) ON [MYDB_USERDATA] GO SET ANSI_PADDING OFF
Create Table for Time Dimension	USE [MYDB] GO /****** Object:  Table [STAR].[DIM_TIME]    Script Date: 12/09/2012 00:32:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [STAR].[DIM_TIME](       [Time_Key] [int] NOT NULL,       [TimeFull] [varchar](8) NOT NULL,       [TimeHour] [tinyint] NOT NULL,       [TimeMinute] [tinyint] NOT NULL,       [TimeSecond] [tinyint] NOT NULL,       [BusyTime] [varchar](1) NULL,       [AMPM] [varchar](2) NOT NULL,       [Seconds] [int] NULL, CONSTRAINT [PK_DIMTIME_1] PRIMARY KEY CLUSTERED (       [Time_Key] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [MYDB_USERDATA] ) ON [MYDB_USERDATA] GO SET ANSI_PADDING OFF
SP to Load Date Dimension	USE [MYDB] GO /****** Object:  StoredProcedure [STAR].[SPETL_DIM_DATE]    Script Date: 12/09/2012 00:27:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object:  StoredProcedure [STAR].[SPETL_DIM_DATE]    Script Date: 09/21/2007 14:37:10 ******/ ALTER PROCEDURE [STAR].[SPETL_DIM_DATE] (     @PSTART_YR VARCHAR(4)='2012' --Default Value       ,@PEND_YR   VARCHAR(4)='2015' --Default Value ) AS BEGIN SET NOCOUNT ON SET DATEFORMAT DMY      DECLARE  @VDATE     DATETIME ,@VEND_DT DATETIME ,@firstdayofmonth DATETIME ,@lastdayofmonth_ind VARCHAR(1) SET @VDATE=CONVERT(DATETIME,'01-01-'+@PSTART_YR)      SET @VEND_DT=CONVERT(DATETIME,'31-12-'+@PEND_YR) DELETE FROM STAR.DIM_DATE WHERE DATE BETWEEN @VDATE AND @VEND_DT WHILE @VDATE<=@VEND_DT       BEGIN       set @lastdayofmonth_ind=case datepart(dd,dateadd(dd,1,@vdate)) when 1 then 'Y' else 'N' end; --Insert the record INSERT INTO [STAR].[DIM_DATE]            ([DATE_KEY]            ,[DATE]            ,[DATE_DESC]            ,[DAYOFWEEK]            ,[DAYNAMEOFWEEK]            ,[DAYOFMONTH]            ,[DAYOFYEAR]            ,[WEEKOFYEAR]            ,[MONTH_NAME]            ,[MONTHOFYEAR]            ,[LASTDAYOFMONTH_IND]            ,[CAL_QTR]            ,[CAL_YR]            ,[HOLIDAY_IND]            ,[REC_UPDT_DT]            ,[REC_UPDT_USER])      VALUES(                   CONVERT(VARCHAR(8), @vdate, 112) --date_key                   ,@vdate                   ,SUBSTRING(CONVERT(VARCHAR(20), @vdate, 113),-8,20) --date_desc                   ,datepart(dw,@vdate) --dayofweek                   ,datename(dw,@vdate) --dayNameofweek                   ,DATEPART(d, @vdate) --dayofmonth                   ,datepart(dy,@vdate) --DayofYear                   ,datepart(wk,@vdate) --WeekofYear                   ,datename(month,@vdate) --Month_Name                   ,datepart(mm,@vdate) --MonthOfYear                   ,@lastdayofmonth_ind                   ,datepart(q,@vdate) --CAL_QTR                   ,datepart(year,@vdate) --CAL_YR                   ,CASE datename(dw,@vdate) WHEN 'Sunday' THEN 'Y' ELSE 'N' END --HOLIDAY_IND                   ,getdate() --REC_UPDT_DT                   ,user --REC_UPDT_USER                   ) set @vdate=DATEADD(DD,1,@VDATE) --INCREMENT DATE END --WHILELOOP end --proc
SP to Load Time Dimension	USE [MYDB] GO /****** Object:  StoredProcedure [STAR].[SPETL_DIM_TIME]    Script Date: 12/09/2012 00:27:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /****** Object:  StoredProcedure [STAR].[SPETL_DIM_TIME]    Script Date: 09/21/2007 14:41:45 ******/ ALTER PROCEDURE [STAR].[SPETL_DIM_TIME] AS BEGIN SET NOCOUNT ON SET DATEFORMAT DMY DELETE [STAR].[DIM_TIME] --insert into [NTSDSS].[DBO].[DIM_TIME] select 0,'00:00:00', 0, 0, 0 declare   @actTime datetime,   @ctr int set @acttime= '2001-01-01 00:00:00' while @acttime <= '2001-01-01 23:59:59' begin     INSERT INTO [STAR].[DIM_TIME]                   ([TIME_KEY]                   ,[TIMEFULL]                   ,[TIMEHOUR]                   ,[TIMEMINUTE]                   ,[TIMESECOND]                 ,[BUSYTIME]             ,[AMPM])       VALUES(                   convert(int,replace(right(convert(varchar,@acttime,8),8),':','')) --TimeKey(Unique Number)                   ,convert(varchar,@actTime,8) --Timefull                   ,datepart(hh,@actTime) --TimeHour                   ,datepart(n, @actTime) --TimeMinute                   ,datepart(s, @actTime) --TimeSecond                   ,case when datepart(hh,@actTime) in (8,9, 17,18) then 'Y' end  -- peak/busy time flag                   ,case when datepart(hh,@actTime) in (0,1,2,3,4,5,6,7,8,9,10,11) then 'AM' else 'PM' end  -- am/pm flag                   ) set @actTime = @actTime + '00:00:01' end --while END -- begin

Simple Calling above two SP in Execute SQL Task in SSIS to load these tables.

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 2

Please provide additional detail of your issue, as the question you give is too vague. What business requirements must these tables meet? There are many different good ways, that meet varying business requirements.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 3

My Date & Tmie table Structure are similar to above one..

I just asked generic question to get an idea & from that idea I can derive my requirment...

I have give one example also to load above tables..

 

---

Thanks Shiven:) If Answer is Helpful, Please Vote

Answer 4

Why are you storing all the redundant sub-structures of date and time. Just store datetime values, and extract the desired datepart as required.

And I asked the question I did, because the generic question is meaningless (except as it convinces you to abandon the thought of storing all the dateparts separately).

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 5

Why are you storing all the redundant sub-structures of date and time. Just store datetime values, and extract the desired datepart as required.

Pieter, let me guess: you are an OLTP guy, aren't you?

Not that I'm a datawarehousing guy, but Kumar's table looks quite typical of something you would find in a data warehouse.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Since filling this table is more or less one-time thing, there is maybe not so much reason to do it elegantly and effeciently. What matters is that you get the correct data.

However, I noticed this in your script:

SET QUOTED_IDENTIFIER OFF

You don't want this setting. You probably get it because the original stored procedure was created from Enterprise Manager in SQL 2000 or from SQLCMD where this setting is OFF by default. The reason that you don't want QUOTED_IDENTIFIER OFF is that there a couple of features in SQL Server that requires this setting to be on.

You can use this query, to find all stored procedures in the database that has the incorrect setting:

SELECT object_name(object_id)
FROM   sys.sql_modules
WHERE  uses_ansi_nulls = 0 OR
       uses_quoted_identifier = 0

The script also includes this bad command:

SET ANSI_PADDING OFF

But this commands after table creation, and it is for table creation it matters. It's still a good idea to remove the command from the script.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Actually, I would have thought I came across as slightly more datawarehouse, having worked mostly in reporting rather than input.

So now I will bite: I understand the benefits of trading space for CPU in a data warehouse, where CPU is actually a scarce resource, but for datetime? really?

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.