DimDate populate data

日期维度

任何一个数据仓库都应该有一个日期维度。

因为很少有不需要通过日期维度看数据的情况存在。

日期维度的好处是，你可以通过他连接各个事实表，然后在报表端传送报表参数的时候，

直接自动过滤日期维度的相关值，而不需要自己写query.

   

去掉了西班牙语和法语的那些列。

You know that there are some holes in the sample database, DW2008R2 DIMDATE table.

So I take my own dimdate. Just remove some columns not used.

   

CREATE TABLE [dbo].[DimDate](

[DateKey] [int] NOT NULL,

[FullDateAlternateKey] [date] NOT NULL,

[DayNumberOfWeek] [tinyint] NOT NULL,

[EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,

[DayNumberOfMonth] [tinyint] NOT NULL,

[DayNumberOfYear] [smallint] NOT NULL,

[WeekNumberOfYear] [tinyint] NOT NULL,

[EnglishMonthName] [nvarchar](10) NOT NULL,

[MonthNumberOfYear] [tinyint] NOT NULL,

[CalendarQuarter] [tinyint] NOT NULL,

[CalendarYear] [smallint] NOT NULL,

[CalendarSemester] [tinyint] NOT NULL,

[FiscalQuarter] [tinyint] NOT NULL,

[FiscalYear] [smallint] NOT NULL,

[FiscalSemester] [tinyint] NOT NULL,

CONSTRAINT [PK_DimDate_DateKey] 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],

CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED

(

[FullDateAlternateKey] 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

   

BEGIN TRAN

   

declare @startdate date = '2005-01-01',

@enddate date = '2015-12-31'

Declare @datelist table (FullDate date)

   

while @startdate <= @enddate

Begin

Insert into @datelist (FullDate)

Select @startdate

Set @startdate = dateadd(dd,1,@startdate)

end

Insert into dbo.DimDate

(DateKey,

FullDateAlternateKey,

DayNumberOfWeek,

EnglishDayNameOfWeek,

DayNumberOfMonth,

DayNumberOfYear,

WeekNumberOfYear,

EnglishMonthName,

MonthNumberOfYear,

CalendarQuarter,

CalendarYear,

CalendarSemester,

FiscalQuarter,

FiscalYear,

FiscalSemester)

   

select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,

dl.FullDate,

datepart(dw,dl.FullDate) as DayNumberOfWeek,

datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,

datepart(d,dl.FullDate) as DayNumberOfMonth,

datepart(dy,dl.FullDate) as DayNumberOfYear,

datepart(wk, dl.FUllDate) as WeekNumberOfYear,

datename(MONTH,dl.FullDate) as EnglishMonthName,

Month(dl.FullDate) as MonthNumberOfYear,

datepart(qq, dl.FullDate) as CalendarQuarter,

year(dl.FullDate) as CalendarYear,

case datepart(qq, dl.FullDate)

when 1 then 1

when 2 then 1

when 3 then 2

when 4 then 2

end as CalendarSemester,

case datepart(qq, dl.FullDate)

when 1 then 3

when 2 then 4

when 3 then 1

when 4 then 2

end as FiscalQuarter,

case datepart(qq, dl.FullDate)

when 1 then year(dl.FullDate)

when 2 then year(dl.FullDate)

when 3 then year(dl.FullDate) + 1

when 4 then year(dl.FullDate) + 1

end as FiscalYear,

case datepart(qq, dl.FullDate)

when 1 then 2

when 2 then 2

when 3 then 1

when 4 then 1

end as FiscalSemester

from @datelist dl ;

   

commit tran;