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