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;