Time Dimension with fiscal periods

[lots_of_questions]

Hi all,

I am trying to develop a time dimension and I was planning on using the date as the primary key.  My issue is adding the fiscal period.  The fiscal period depends on the fiscal year.  April 2016 is period 1 for the 2016-2017 fiscal year, but April 2016 is period 13 for the 2015-2016 fiscal year.

What is the best way to tackle this?  Should my key to the date dimension include fiscal year?

Thanks.

[bdbits]

Typically, date/time dimensions include lots of attributes beyond the date itself, especially things like which numeric month, quarter, or other period you are in, calendar or fiscal. For example:

* Date Key
* Date
* Day Name
* Day Name Short
* Day In Week
* Day In Year
* Month
* Month Name
* Month Name Short
* Days In Month
* Month In Calendar Quarter
* Month In Fiscal Quarter
* Calendar Quarter
* Fiscal Quarter
* Year
* Fiscal Reporting Period
* Is Weekday
* Is Weekend
* Is Bank Holiday
* Is Company Holiday
* Holiday Name

Personally, I am a fan of using an integer representation of the date as a surrogate key in the date dimension. This maintains a recognizable foreign key without having to join to a date dimension with a sequence/identity-based key, but eliminates dealing with a date as a key (which is kind of messy to me). But then I am generally a fan of surrogate keys in data warehouses, so maybe it is just me.

Dates can be tricky, at least in the US where you often get Mondays off when holidays fall on weekends, or holidays being the nth occurrence of a certain day. So to get you started, here is a snippet for holidays from a stored proc used to populate a date dimension. Note: I am assuming you do not go very far back in time as the rules are somewhat changed historically.



case
  when datepart(month, @thedate)=1 and datepart(day, @thedate)=1 then 'y' -- New Year's Day
  when datepart(month, @thedate)=5 and datepart(day, @thedate)>=25 and datepart([weekday], @thedate)=2 then 'y' -- Memorial Day
  when datepart(month, @thedate)=7 and datepart(day, @thedate)=4 then 'y' -- Independence Day
  when datepart(month, @thedate)=9 and datepart(day, @thedate)<=7 and datepart([weekday], @thedate)=2 then 'y' -- Labor Day
  when datepart(month, @thedate)=11 and datepart(day, @thedate) between 22 and 28 and datepart([weekday], @thedate)=5 then 'y' -- Thanksgiving
  when datepart(month, @thedate)=11 and datepart(day, @thedate) between 23 and 29 and datepart([weekday], @thedate)=6 then 'y' -- day after Thanksgiving
  when datepart(month, @thedate)=12 and datepart(day, @thedate)=25 then 'y' -- Christmas
  else 'n'
end as [isholiday],
case
  when datepart(month, @thedate)=1 and (datepart(day, @thedate)=1 and not datepart([weekday], @thedate) in (1, 7) or datepart(day, @thedate) in (2, 3) and datepart([weekday], @thedate)=2) then 'New Year''s Day'
  when datepart(month, @thedate)=5 and datepart(day, @thedate)>=25 and datepart([weekday], @thedate)=2 then 'Memorial Day'
  when datepart(month, @thedate)=7 and (datepart(day, @thedate)=4 and not datepart([weekday], @thedate) in (1, 7) or datepart(day, @thedate) in (5, 6) and datepart([weekday], @thedate)=2) then 'Independence Day'
  when datepart(month, @thedate)=9 and datepart(day, @thedate)<=7 and datepart([weekday], @thedate)=2 then 'Labor Day'
  when datepart(month, @thedate)=11 and datepart(day, @thedate) between 22 and 28 and datepart([weekday], @thedate)=5 then 'Thanksgiving'
  when datepart(month, @thedate)=11 and datepart(day, @thedate) between 23 and 29 and datepart([weekday], @thedate)=6 then 'Day after Thanksgiving'
  when datepart(month, @thedate)=12 and (datepart(day, @thedate)=25 and not datepart([weekday], @thedate) in (1, 7) or datepart(day, @thedate) in (26, 27) and datepart([weekday], @thedate)=2) then 'Christmas Day'
  else 'n'
end as [holidayname];

[lots_of_questions]

Thanks for your reply.  Unfortunately, my issue is not holidays.  I can easily determine that as they depend on the date.  The issue is how to add a Fiscal Period with regards to period 13 or 14.  Fiscal Period 13 of 2016 is the same as Fiscal Period 1 for 2017.  I can't have two values for the same day.

I am going to simplify my time dimension for now.  I am going to use Period as my bottom level.  That will allow me to drill down from Fiscal Year to Quarter to Fiscal Period.

If anyone has a better idea, please let me know.