Monthly snapshot fiscal calendar issue

View previous topic View next topic Go down

Monthly snapshot fiscal calendar issue

Post  ebry74 on Mon May 18, 2015 3:41 pm

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

ebry74

Posts : 5
Join date : 2011-06-20

View user profile

Back to top Go down

Re: Monthly snapshot fiscal calendar issue

Post  ngalemmo on Mon May 18, 2015 9:02 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Monthly snapshot fiscal calendar issue

Post  nick_white on Tue May 19, 2015 2:15 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Monthly snapshot fiscal calendar issue

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum