Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Date Dimension Using Computed Columns

  • 4 August 2011
  • Author: DevinKnight
  • Number of views: 5855
  • 0 Comments

Picked up this nice script from a client last week.  Use this script below to create a Date Dimension for a data warehouse that is based off of computed columns. The only column that needs to be populated is the CalendarDate column.


CREATE TABLE [DimDate](
[DateKey] [int] IDENTITY(1,1)NOT NULL,
[CalendarDate] [datetime] NULL,
[CalendarYearNumber] AS (datepart(year,[CalendarDate])),
[CalendarYearName] AS (CONVERT([varchar](4),datepart(year,[CalendarDate]),(0)))PERSISTED,
[CalendarQuarterNumber] AS (datepart(quarter,[CalendarDate])),
[CalendarQuarterName] AS ('QTR'+CONVERT([varchar](1),datepart(quarter,[CalendarDate]),(0))),
[CalendarMonthNumber] AS (datepart(month,[CalendarDate])),
[CalendarMonthName] AS (datename(month,[CalendarDate])),
[CalendarMonthNameShort] AS (left(datename(month,[CalendarDate]),(3))),
[DayNumberOfWeek] AS (datepart(weekday,[CalendarDate])),
[DayNameOfWeek] AS (datename(weekday,[CalendarDate])),
[CalendarDayNumberOfMonth] AS (datepart(day,[CalendarDate])),
CONSTRAINT [PKdimDate] 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]
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO



Print
Tags:
Rate this article:
No rating
DevinKnight

DevinKnightDevinKnight

Other posts by DevinKnight

Please login or register to post comments.