Monthly snapshot fiscal calendar issue

I have a monthly snapshot, and each snapshot is given a datekey of the first day of the month.  Example, 20110401.  This allows me to join to my date dimension and have a single row returned.  My issue is, they also like to see they data from a fiscal perspective. Using the first day in the month doesn't always join to new fiscal month.  For example, both June and July of calendar 2012 show the fiscal period of June.  This essentially eliminates fiscal month May, and doubles up our employee data for June.

Any thoughts on how to use a single date key to show both calendar and fiscal data correctly.  FYI...I would have the same issue using the last day of each month.

I'm starting to think it can't be done without actually creating a fiscal snapshot.

DateMonthKey FiscalMonthLongName FiscalMonth Active Seperations Hired 20110401 March, Fiscal Year 2011 201112 1662 30 11 20110501 April, Fiscal Year 2012 201201 1659 27 24 20110601 June, Fiscal Year 2012 201203 1666 22 29 20110701 June, Fiscal Year 2012 201203 1677 27 38 20110801 August, Fiscal Year 2012 201205 1674 35 32

No. A single date key cannot serve both purposes. You need two different keys. However, it does not result in a much larger aggregate. Most of the time, the two date keys will be the same, so most of the data will aggregate to those rows. Then there is no more than two other time periods (different date keys) for a given month.

You would use one of the two keys in queries to get either a calendar or fiscal perspective.

In addition, if your snapshots are monthly then I would create a Month Dimension (effectively an aggregate of your Date Dimension) and use that as the "time" key in your snapshots. If you use a day key then effectively you are applying all the attributes of that day (day of the week, day in year, etc.) to your snapshot and they don't actually make sense. If you use a month key then all the attributes are at a month level and then do make sense for a monthly snapshot

Regards