Fiscal Dim and Snapshot Dates for Budgets

[cjportil]

Hi everyone,
I have a couple of questions regarding fiscal dates and snapshot dates/data:

1. How would someone model a "fiscal" dim when a date maps to two fiscal months?

e.g.

Fiscal Year 2010
Fiscal Month 12
Fiscal Month Date 07/01/2010

Fiscal Year 2011
Fiscal Month 01
Fiscal Month Date 07/01/2010

2. Our organization wants us to store both actuals and budgets in the warehouse. The actuals are pretty straight forward, they want the actuals to be stored for each month and have both a YTD and month activity column. However, I'm having difficulty understanding how to store the bugdets because the budgets are just snapshots of what the budgets looked like at a specific point in time. The snapshots are given a date (the date does not relate to the Fiscal Period) and are always assigned a Fiscal Month of 12 because the budgets are always full year budgets. How will someone model this? Are the budgets a seperate fact?

Here's a sample dataset of what the user is expecting to see:

[/td]

-----------	Actuals (YTD Amounts)		Budgets (Full Year Amounts)
-----------	Period1	Period2	Snapshot1	Snapshot2
Account 1	$100	$210	$150	$200
Account 2	$200	$310	$350	$350

I hope these two questions make sense. If you need some clarification, please let me know.

Thanks!
Chris J.

[ngalemmo]

Usually budgets and projections are in a separate fact table. Remember, a fact table represents a business process. Budgeting is a separate process and occurs at different times than the sales and expenditure processes.

What I don't understand is your "fiscal month date". Is that the date the budget was loaded/created? Your fact needs to record two dates, the date the budget was created (which has nothing to do with the fiscal calendar) and the fiscal period that budget is for.

[cjportil]

Hi.
The "fiscal month date" in my example is the "date" the fiscal period falls in. So in my example, I have two periods that fall into the same "date". Here's

Fiscal Period 12 begins on 7/1/2010 and ends on 7/31/2010

Fiscal Period 1 begins on 7/1/2010 and ends on 8/31/2010

So my issue is that I have overlapping dates for periods.

Thanks!

[ngalemmo]

Are you sure about that? I've never heard of overlapping fiscal periods before. What are the circumstances some action during July 2010 be booked in one period or the other?

[Sponsored content]