Making a Easy Date Dimension Utilizing Recursive Frequent Desk Expressions (CTE)

On this publish I’ll clarify the right way to create a easy date dimension to make use of it in your information warehouses and your BI options. So, this text is for you for those who want a quick and straightforward option to make a easy date dimension that helps probably the most generally used date parts like

·         Integer date key

·         Completely different date codecs

·         Quarter

·         Month names

·         Week numbers

·         Day of the week

·         Day of the yr

·         Is day finish of month

·         Not out there (N/A) row

As a consequence of the truth that there are many fellows which can be nonetheless utilizing SQL Server 2008 and earlier, I put the codes that help SQL Server 2008  in addition to SQL Server 2012. However, I’ve commented the 2008 strains.

I, myself, was in search of a easy date dimension and I’ve discovered a bunch of them over the Web. However, what I don’t like about most of them is that these options are inserting information on a row-by-row foundation utilizing some time loop that appears to be a bit gradual on the first time of populating the desk. That was the primary cause that I made a decision to re-invent the wheel and make one thing that’s easy and straightforward to implement and on the similar time is operating quick.

Now, let’s discuss in regards to the answer. The next code generates a date dimension, I referred to as it DimDate, utilizing recursive Frequent Desk Expressions (CTE).

To begin with create a DimDate.

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’DimDate’))

CREATE TABLE [dbo].[DimDate](

       [DateAlternateKey] [date] NULL,

       [UKDateFormat] [varchar](10) NULL,

       [USDateFormat] [varchar](10) NULL,

       [GermanDateFormat] [varchar](10) NULL,

       [FullDate] [varchar](11) NULL,

       [DateKey] [int] NOT NULL PRIMARY KEY,

       [Year] [int] NULL,

       [Quarter] [int] NULL,

       [QuarterDescription] [varchar](6) NOT NULL,

       [Month] [varchar](9) NULL,

       [MonthNumber] [int] NULL,

       [MonthYear] [varchar](7) NULL,

       [WeekNumber] [int] NULL,

       [WeekNumberDescription] [varchar](5) NOT NULL,

       [DayOfWeek] [varchar](10) NULL,

       [DayOfMonth] [int] NULL,

       [DayOfYear] [int] NULL,

       [EndOfMonth] [int] NULL,

       [IsDayEndOfMonth] [varchar](3) NOT NULL

) ON [PRIMARY]

GO

Notes:

·         If for any cause, you do NOT wish to create the DimDate first after which populate it, simply uncomment the “–into DimDate” line from the code.

·         When you determined to create the DimDate desk first, then uncomment the –INSERT INTO [dbo].[DimDate] line.

·         The beginning date is ready to ‘1990-01-01’ and the top date is outlined to be ‘2030-12-31’.

·         Because the default most variety of recursions in a CTE is 100, the choice (maxrecursion 0) needs to be added to the code.

·         For supporting Not Obtainable (N/A) dates, in case you are implementing an SSAS Tabular Mannequin then use ‘1900-01-01’ in union all, in any other case you need to use a NULL as a substitute.

·         You may add public holidays of your individual nation to the answer, however, I didn’t do this to maintain the answer easy.

·         You may also calculate fiscal (monetary) dates, however, once more I didn’t do this to maintain it easy. (Nonetheless, I’d add fiscal dates to the answer sooner or later. Who is aware of?!)

;WITH DimDateCTE AS

(SELECT CAST(‘1990-01-01’ AS DATE) DATE_

 UNION ALL

 SELECT DATEADD(DAY,1,DimDateCTE.DATE_) DATE_ FROM DimDateCTE WHERE DATE_ <‘2030-12-31’

)

–INSERT INTO [dbo].[DimDate] –Use this half if there’s an current DimDate

SELECT DATE_ DateAlternateKey

       , CONVERT(VARCHAR(10), DATE_, 103) UKDateFormat — British/French Date Format

       , CONVERT(VARCHAR(10), DATE_, 101) USDateFormat

       , CONVERT(VARCHAR(10), DATE_, 104) GermanDateFormat

       , CONVERT(VARCHAR(11), DATE_, 106) FullDate

       , CAST(CONVERT(VARCHAR(8),DATE_, 112) as int) DateKey

       , YEAR(DATE_) 12 months

       , DATENAME(qq, DATE_) Quarter

       –, ‘Qtr ‘+ CAST(DATENAME(qq, DATE_) as VARCHAR(1)) QuarterDescription — For SQL Server 2008 and earlier

       , CONCAT(‘Qtr ‘, DATENAME(qq, DATE_)) QuarterDescription  –Supported in 2012 and above

       , DATENAME(M,DATE_) Month

       , MONTH(DATE_) MonthNumber

       , RIGHT(CONVERT(VARCHAR(10), DATE_, 103), 7) MonthYear

       , DATENAME(wk,DATE_) WeekNumber

       –, ‘Wk ‘+CAST(DATENAME(wk,DATE_) as VARCHAR(2)) WeekNumberDescription — For SQL Server 2008 and earlier

       , CONCAT(‘Wk ‘, DATENAME(wk,DATE_)) WeekNumberDescription  –Supported in 2012 and above

       , DATENAME(dw,DATE_) DayOfWeek

       , DATEPART(dd,DATE_) DayOfMonth

       , DATENAME(dy,DATE_) DayOfYear

       –, DATEPART(dd,DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATE_)+1,0))) EndOfMonth — For SQL Server 2008 and earlier

       –, CASE WHEN DATEPART(dd,DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATE_)+1,0)))= DATEPART(dd,DATE_) THEN ‘Sure’ ELSE ‘No’ END IsDayEndOfMonth — For SQL Server 2008 and earlier

       , DATEPART(dd,EOMONTH(DATE_)) EndOfMonth –Supported in 2012 and above

       , IIF(DATEPART(dd,EOMONTH(date_))= DATEPART(dd,DATE_), ‘Sure’,‘No’) IsDayEndOfMonth –Supported in 2012 and above

–INTO DimDate –Use this line to create a brand new DimDate desk

FROM DimDateCTE

UNION ALL –Supporting N/A dates

SELECT ‘1900-01-01’ –This needs to be 1900-01-01 for SSAS Tabular Mannequin. In any other case, a NULL can be utilized as a substitute.

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , ‘N/A’

       , –1

       , –1

       , –1

       , ‘N/A’

       , ‘N/A’

       , –1

       , ‘N/A’

       , –1

       , ‘N/A’

       , ‘N/A’

       , –1

       , –1

       , –1

       , ‘N/A’

OPTION (MAXRECURSION 0)

The outcomes needs to be like this:

Get pleasure from!