日期维度
任何一个数据仓库都应该有一个日期维度。
因为很少有不需要通过日期维度看数据的情况存在。
日期维度的好处是，你可以通过他连接各个事实表，然后在报表端传送报表参数的时候，
直接自动过滤日期维度的相关值，而不需要自己写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;