Fiscal Year and Account Dimension

Hello;

I am currently designing a slowly changing (type 2) Account dimension. A change to an Account attribute can occur through out the fiscal year. Unfortunately a fiscal year is not based on a traditional 12 month calendar and instead is based on a 13 month calendar. In addition, more than one fiscal year can be active in a given calendar year. This presents a problem since my slowly changing effective start and end dates are based on a calendar year.

Currently I am considering two solutions

1. Make fiscal year a business key along with Account Number. This scenario assumes that the Account life cycle is from the beginning of the fiscal year to the end.

2. Make fiscal year a part of the slowly changing attributes along with effective start and end date. In this scenario the Account life cyle crosses fiscal years.

Can you suggest other solutions?

Thank you in advance,
Pete.

Why do you feel the need to combine fiscal year with account?

Your facts should always have a date associated with it. (transaction date, "As-of" date etc). This date will be used to look up any dimensional surrogate keys. If your account dimension has effective start and end dates, then the lookup for the account dimension SK is straightforward.

The periods in your fiscal calendar should also have a start and end date - so the lookup for the fiscal period SK is easy as well.

The only wrinkle could be the 13th period .... does it truly exist in the fiscal calendar, or is just a construct in the transaction system? (I've seen systems where it is used to hold year-end rollover transactions/restatements etc ....)

Yes, it's the 13th period that is causing the problem. A change to an Account attribute can occur in the 13th period of the old fiscal year that does not affect the Account attributes found in the 1st period of the new fiscal year. As you know both fiscal years can be active at the same time, hence the 13th period. Since the SCD effective start and end date is traditionally based on a calendar year a another approach may be needed to accurately record history.

The usual effective/expiration date values in a type 2 dimension typically reflect when the particular change was made, correct? But, as you have realized, doesn't reflect when it is effective from a business point of view. Having a second set of effective/expiration pairs, be they dates or, in this case, fiscal periods, to reflect its use by the business is fine. Just be careful how these values are maintained.