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 !!!
