15 month fiscal year

View previous topic View next topic Go down

15 month fiscal year

Post  John Tesson on Thu Dec 23, 2010 4:03 pm

We have a need to compare monthly transaction amounts that are associated with 15 month fiscal years. A fiscal year 2009-10 would span July 2009 thru September 2010. But there can be transaction amounts in fiscal year 2010-11 associated with calendar month of September 2010, for example. And there can be transaction amounts for fiscal year 2009-10 that are for September 2010 as well. So partly there is a design issue for the time dimension. One idea is to have the extra 3 months in the year to have their own rows in the time table. We probably also need a separate fiscal year dimension for the star. But my preference is not to have extra month rows in the time table for the lapsed 3 months of each year. We'll also want to to YTD comparsisons. Appreciate ideas on this.

Happy Holidays!

John Tesson

Posts : 7
Join date : 2009-02-03

View user profile

Back to top Go down

Similar problem

Post  John Tesson on Tue Dec 28, 2010 10:43 am

I just noticed that my problem is similar to the one described in the topic "Fiscal Dim and Snapshot Dates for Budgets" in the sense that 2 transactions on the same date can be associated with different fiscal periods.
Anyone care to comment?
Thanks

John Tesson

Posts : 7
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 15 month fiscal year

Post  Jeff Smith on Tue Dec 28, 2010 10:57 am

Should Fiscal Year be a seperate dimension? If a particular date can be associated with more than one Fiscal Year, then Fiscal Year cannot be in the Date Dimension (the lowest level of a dimension cannot roll up to multiple values in a higher level).

You could create some type of junk dimension that had Fiscal year and Date in which a Date was associated with many fiscal years.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 15 month fiscal year

Post  John Tesson on Tue Dec 28, 2010 11:43 am

I like the idea of a non-standard year and date dimension to handle this, i.e. there would be a july 2010 row for fiscal year 2009-10 and a different july 2010 row for fiscal year 2010-11. One design issue is that we are trying to have a master time dimension from which various subset dimensions can be built/extracted. Whether this would work is a matter of preference I guess.

John Tesson

Posts : 7
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 15 month fiscal year

Post  Jeff Smith on Tue Dec 28, 2010 1:19 pm

Try a snowflake. Create a dimension table that had the key, fiscal Year, and Date key. The Date Key linked to the Date Dimension.

This gives you a single Date table and enables you to achieve your aim. Plus, the 2 tables are so small, that they shouldn't take any time to join.

If you had a single table with Fiscal Year and Date, then you would need a view of distinct rows of the just the date information when using the dimension with dates that have no relation to the fiscal year, such as Date of birth, load date, etc.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 15 month fiscal year

Post  ngalemmo on Wed Dec 29, 2010 2:17 am

I've never run into a fiscal calendar that had overlapping periods. Just curious, what industry is this? Why would periods overlap and how does it affect financial reports?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: 15 month fiscal year

Post  John Tesson on Wed Dec 29, 2010 12:09 pm

This is for a University budgeting system where we are tracking allocations, expenditures/encumbrances, and payment/disbursements. They have one fact table that contains all 3 of the above mentioned facts.
The extra 3 months are to cover payments/disbursements that are made for encumbrances that occurred during the normal 12 month fiscal year. the users want to be able to analyze the payments that are being made late due to slow invoicing from the vendors. I've just been asked to give some advice but am not the designer. I'm actually glad you asked about this because I've learned more than what I had known previously.

John Tesson

Posts : 7
Join date : 2009-02-03

View user profile

Back to top Go down

Solution

Post  John Tesson on Wed Dec 29, 2010 4:15 pm

I've come up with a solution that we're going with. We'll have a special 15-month Month dimension that does not contain the year. It will identify the extra 3 months as July Lapsed, August Lapsed, and September Lapsed. A separate dimension will just have the Fiscal Year. We'll then be able to report on multiple Fiscal Years on all 15 months this way, distinguishing, for example, July from "July Lapsed". Will also have a 3rd dimension for a normal calendar dimension. This gives us a fair amount of flexibility in reporting and can allow for handling of special "reappropriated" account types also.

John Tesson

Posts : 7
Join date : 2009-02-03

View user profile

Back to top Go down

Re: 15 month fiscal year

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