Fiscal Year and Account Dimension

View previous topic View next topic Go down

Fiscal Year and Account Dimension

Post  peter_weinstein on Thu Jul 14, 2011 1:29 pm

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.

peter_weinstein

Posts : 6
Join date : 2009-12-20

View user profile

Back to top Go down

Re: Fiscal Year and Account Dimension

Post  LAndrews on Thu Jul 14, 2011 3:03 pm


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 ....)

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Fiscal Year and Account Dimension

Post  peter_weinstein on Thu Jul 14, 2011 6:01 pm

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.

peter_weinstein

Posts : 6
Join date : 2009-12-20

View user profile

Back to top Go down

Re: Fiscal Year and Account Dimension

Post  ngalemmo on Fri Jul 15, 2011 1:45 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Fiscal Year and Account Dimension

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