Feeds:
Posts
Comments

Archive for December, 2011

The existing date dimension only has Calendar year. There are 2 different ways to add the Fiscal Year calculation i.e.

  1.  Writing t-sql to calculate Fiscal Year 
  2.  Utilising the built-in Microsoft SQL Server Analysis Services Dimension wizard to create a new Time dimension and select either “Generate a time table on the server” or “Generate a time table on the server”.

To add Year to Date, Month To Date calculations into Fiscal hierarchy, use the Add Business Intelligence wizard in the SSAS. Select “Define dimension intelligence” then map the Fiscal Year to Fiscal Year and Fiscal Quarter to Fiscal Quarter of the Date dimension. Once mapped, select “Add Business Intelligence” wizard. This time select “Define time intelligence. Seleted Date\Fiscal  hierarchy then tick all of the available time calculations and the available measures.

I have opted for option 1 – manually do the calculation in t-sql.

The script to do that:

CREATE FUNCTION [Warehouse].[GetDateList] ()
RETURNS @DateList TABLE
(
 Date DATETIME NOT NULL PRIMARY KEY
 ,[DayOfMonth] int NULL
 , MonthOfYear int NULL
 ,[Year] int NULL
 ,[DayOfWeek] varchar(15) NULL
 ,WeekOfYear int NULL
 ,WeekName varchar(20) NULL
 ,[DayOfYear] int NULL
 ,[MonthName] varchar(20) NULL
 ,[Quarter] int NULL
 ,QuarterName varchar(20) NULL
 ,IsLastDayOfMonth bit NULL
 ,FiscalYear int  NULL
 ,FiscalQuarter int NULL
 ,FiscalQuarterName varchar(20) NULL
)

BEGIN

 DECLARE @ThisDate DATETIME
 DECLARE @StartDate DATETIME
 DECLARE @EndDate DATETIME

 SELECT @StartDate = MIN(MinDate) FROM
   ( SELECT MIN(EventDate) AS MinDate
    FROM c_Events
    
    
    UNION ALL
    
    SELECT MIN(RosterDate) AS MinDate
    FROM f_RosterDays
    
    UNION ALL
    
    SELECT MIN(Month) AS MinDate
    FROM c_Exposure
    
   ) sub
 
 SELECT @EndDate = MAX(MaxDate) FROM
   ( SELECT Cast(Cast(YEAR(Max(EventDate)) AS VARCHAR(4)) + ‘-12-31’ AS DATETIME) AS MaxDate
    FROM c_Events
    
    UNION ALL
    
    SELECT MAX(RosterDate) AS MaxDate
    FROM f_RosterDays

    UNION ALL
    
    SELECT MIN(Month) AS MaxDate
    FROM c_Exposure
    
    UNION ALL
    
    SELECT MAX(ReviewDate) AS MaxDate
    FROM c_events
    
   ) sub
   
 SET @StartDate = CAST( FLOOR( CAST( @StartDate AS float) ) AS SMALLDATETIME)

 SET @ThisDate = @StartDate

 WHILE (@ThisDate <= @EndDate)
 BEGIN
  INSERT INTO @DateList
   (  [Date]
   ,[DayOfMonth]
   ,MonthOfYear
   ,[Year]
   ,[DayOfWeek]
   ,WeekOfYear
   ,WeekName
   ,[DayOfYear]
   ,[MonthName]
   ,[Quarter]
   ,QuarterName
   ,FiscalYear
   ,FiscalQuarter
   ,FiscalQuarterName
  )
  VALUES( @ThisDate
    ,day(@ThisDate)
    ,Month(@ThisDate)
    ,Year(@ThisDate)
    ,datename(weekday,@ThisDate)  –WeekDay
    ,datename(week,@ThisDate)  –Week
    ,’Week ‘ + datename(week,@ThisDate) –WeekName
    ,datename(dayOfYear,@ThisDate)  –dayOfYear
    ,datename(month,@ThisDate)  –MonthName
    ,datename(quarter,@ThisDate) –Quarter
    ,CASE datename(quarter,@ThisDate)
      WHEN 1 THEN ‘First Quarter’
      WHEN 2 THEN ‘Second Quarter’
      WHEN 3 THEN ‘Third Quarter’
      WHEN 4 THEN ‘Fourth Quarter’ END  –QuarterName
               — LastDayofMonth
       ,CASE  WHEN MONTH(@ThisDate) < 7 THEN YEAR(@ThisDate)-1
                       ELSE YEAR(@ThisDate) END
    ,CASE Month(@ThisDate)
      WHEN 1 THEN 3
      WHEN 2 THEN 3
      WHEN 3 THEN 3
      WHEN 4 THEN 4
      WHEN 5 THEN 4
      WHEN 6 THEN 4
      WHEN 7 THEN 1
      WHEN 8 THEN 1
      WHEN 9 THEN 1
      WHEN 10 THEN 2
      WHEN 11 THEN 2
      WHEN 12 THEN 2           
    END  –[FiscalQuarter]
    ,CASE Month(@ThisDate)
      WHEN 1 THEN ‘Third Quarter’
      WHEN 2 THEN ‘Third Quarter’
      WHEN 3 THEN ‘Third Quarter’
      WHEN 4 THEN ‘Fourth Quarter’
      WHEN 5 THEN ‘Fourth Quarter’
      WHEN 6 THEN ‘Fourth Quarter’
      WHEN 7 THEN ‘First Quarter’
      WHEN 8 THEN ‘First Quarter’
      WHEN 9 THEN ‘First Quarter’
      WHEN 10 THEN ‘Second Quarter’
      WHEN 11 THEN ‘Second Quarter’
      WHEN 12 THEN ‘Second Quarter’             
    END — [FiscalQuarterName]
              )
  SET @ThisDate = DateAdd(Day, 1, @ThisDate)
 END
 
 UPDATE @DateList
  SET IsLastDayOfMonth = CASE WHEN Date = dbo.fn_LastDayOfMonth(Date) THEN 1
  ELSE 0 END
  
 RETURN
 
END

CREATE VIEW [Warehouse].[s_vw_dm_Dates]
AS

SELECT *
FROM Warehouse.GetDateList()

UNION ALL

SELECT ‘1900-01-01 00:00:00.000’, 1, 1, ‘1990’, ‘Monday’, 1, ‘Week 1’, 1, ‘January’, 1, ‘First Quarter’, CAST(0 AS BIT), ‘1900’, 7, ‘FirstQuarter’

Read Full Post »

%d bloggers like this: