Monthly snapshot fiscal calendar issue
3 posters
Page 1 of 1
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.
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
Re: Monthly snapshot fiscal calendar issue
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.
You would use one of the two keys in queries to get either a calendar or fiscal perspective.
Re: Monthly snapshot fiscal calendar issue
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
Regards
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
![-](https://2img.net/i/empty.gif)
» Thoughts on monthly snapshot
» Monthly Claims Snapshot
» Fiscal Dim and Snapshot Dates for Budgets
» Updating Monthly snapshot for prior months...
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
» Monthly Claims Snapshot
» Fiscal Dim and Snapshot Dates for Budgets
» Updating Monthly snapshot for prior months...
» Monthly Periodic Snapshot Table using SCD Type1 Dimension tables - Design Question.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum