Dates Dimension Data Generation – MS SQL

Hi,

In this post I would like to put out some SQL to get my Dates table ready for some SSAS date dimensions.

Most of the times SSAS in itself is capable to achieve the same on its own via fancy UI’s during creation of similar dimensions, but the below SQL statements would come in handy if you would wanted it for yourself to create manually and more finely controlled at SQL level.

Or to add just add more dates to the existing dates table:

--Check if the 'dbo.Dates'table exist, and if exist drop table to re-create
IF OBJECT_ID('dbo.Dates', 'U') IS NOT NULL
  DROP TABLE dbo.Dates; 

--Create table
CREATE TABLE Dates(
DateKey INT NOT NULL,
CalenderDateValue DATE NOT NULL,
FiscalDateValue DATE NOT NULL);

GO

--Populate the 'dbo.Dates' using while loop
DECLARE @StartDate DATE = '2013-01-01';
DECLARE @FiscalMonthOffset INT = 3;
DECLARE @EndDate DATE = GETDATE();
DECLARE @TempDate DATE;

SET @TempDate = @StartDate;
SET NOCOUNT ON;
BEGIN TRAN;
WHILE @TempDate <= @EndDate
BEGIN

	PRINT CONVERT(VARCHAR, @TempDate, 112);

	INSERT INTO dbo.Dates( DateKey, CalenderDateValue, FiscalDateValue )
	VALUES  (CAST(CONVERT(VARCHAR, @TempDate, 112) AS INT), -- DateKey - int
	          @TempDate,  -- CalenderDateValue - date
	          DATEADD(m,@FiscalMonthOffset , @TempDate) -- FiscalDateValue - date
			  );

	SET @TempDate= DATEADD(d,1,@TempDate);

END
COMMIT TRAN;

GO

--Resulting Table
SELECT * FROM dbo.Dates;

Hope it helps !!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*