Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fiscal Year and Account Dimension

3 posters

Go down

Fiscal Year and Account Dimension Empty Fiscal Year and Account Dimension

Post  peter_weinstein 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

Back to top Go down

Fiscal Year and Account Dimension Empty Re: Fiscal Year and Account Dimension

Post  LAndrews 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

Back to top Go down

Fiscal Year and Account Dimension Empty Re: Fiscal Year and Account Dimension

Post  peter_weinstein 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

Back to top Go down

Fiscal Year and Account Dimension Empty Re: Fiscal Year and Account Dimension

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

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

http://aginity.com

Back to top Go down

Fiscal Year and Account Dimension Empty Re: Fiscal Year and Account Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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