The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and dimensional tables in a star schema.

For my Calendar (or Time) dimension, I want the standard fields like Date, DayOfWeek etc, but I also want Fiscal periods like FiscalMonth. My problem is that this data warehouse has data for different clients that have different fiscal calendars.

How do I handle that ?
Craig HB Send private email
Tuesday, December 19, 2006
 
 
"My problem is that this data warehouse has data for different clients that have different fiscal calendars."

Your answer is in your question, isn't it?

Table FiscalCalendars
FC_UID
FC_xxxxcxxxx

Table Customer
...
FC_UID -> foreign key into FiscalCalendars
Karl Perry Send private email
Tuesday, December 19, 2006
 
 
I'm not sure what you mean. Are you saying
(1) Have a different Fiscalcalendar table for each client, or
(2) Have one Fiscalcalendar table that has a clientID to show which client you are dealing with ?

I wasn't sure what "FC_xxxxcxxxx" means.
Craig HB Send private email
Tuesday, December 19, 2006
 
 
I'm sure Karl means the second one; one Fiscal Calendar table. However, the way Craig worded his summary indicates there's still some confusion.

For each customer, you essentially need to record the start of the fiscal period and the length of the fiscal period.  You also need to deal with subsets and supersets of the fiscal period.

A very specific example: most businesses need to report revenues at the end of each quarter, but budget expenses on a yearly basis. In my company, the fiscal year starts on October 1st. So we have a quarterly statement December 31st, March 31st, June 30th, and September 30th. Other companies may start their fiscal years January 1st or April 1st. (The actual start date is rather consistent within an industry; October 1st is the Federal Government's fiscal year and so most contractors use that date as well.)

I think Karl wants you to figure out all of the valid permutations (for your market) and put them into a single Fiscal Calendar table. Then each customer would simply point to the appropriate schedule within that table.

I also want to correct something else, I think. In a data warehouse, you're creating "snapshots" of the database at multiple points in time. Specifically, you want to know what the database looked like January 1st, 2006, January 1st, 2007, 2008, 2009 and so on. If you're doing any sort of financial reporting, it is critical that you're able to show the original books and the revised books separately.

Specific example: the IRS tells you to redo your taxes because you need to expense your stock options. You don't go back and change the data in 2005 - you make a new copy of it, make the changes, compare the revised books with the old books and see that you owe the IRS an additional $2 million dollars so you cut a check for exactly that much and not a penny more.

The reason I'm mentioning this is because you do not need to create a fiscal reporting period for every year going back to 1776. You just need to be able to show which reporting period is in effect at the time the books are created.

I hope that helps.
TheDavid
Tuesday, December 19, 2006
 
 
Thanks for you help, TheDavid. However, I'm still not totally sure how to implement that.

"I think Karl wants you to figure out all of the valid permutations (for your market) and put them into a single Fiscal Calendar table".

Does that mean I have a column for each permutation? In my data, Client1's fiscal year starts 1 July and Client2's fiscal year starts 1 April. So I'll have columns like Client1FiscalWeek, Client2FiscalWeek, Client1FiscalMonth, Client2FiscalMonth. Is that right ?

Another suggestion I have had is to have columns like FiscalWeek, FiscalMonth etc, but also have a ClientID column. So then for a single day I have a row for each client. That way each client gets the correct FiscalMonth or FiscalWeek for their fiscal calendar.
Craig HB Send private email
Tuesday, December 19, 2006
 
 
No no no...  I meant something like this...

Fiscal Calendar
===============
Schedule A - FY07 starts January 07, lasts 1 year.
Schedule B - FY07 starts April 07, lasts 1 year.
Schedule C - FY07 starts October 06, lasts 1 year.

Client Data
===============
Client #1 - Schedule A
Client #2 - Schedule A
Client #3 - Schedule B
Client #4 - Schedule C
Client #5 - Schedule C
Client #6 - Schedule A

And so on.

When the client enters some data, it will be for a specific day, say, next January 18th. You can then determine that for clients #1, #2, and #6, January 18th will be in the first quarter of fiscal year 2007. 

However, for client #3, it's in the last quarter of fiscal year 2006 since "2007" doesn't start til April, and for clients #4 and #5, it's the second quarter of fiscal year 2007 since 2007 started way back in October 2006.

In other words, you're creating a "lookup" table that takes the date of the transaction, combines it with the client and determines which financial period the transaction belongs to.

Similarly, you can do a reverse lookup - if someone asks you for client X's data in fiscal year 2008, you can look at the table to determine what the actual date ranges should be - say, April 1st 2008 to March 2009 in the case of schedule B - and then pull the appropriate records.

This saves you the effort of having to explicily calculate and save things like FiscalWeek and FiscalMonth on a record by record basis. You only have to save the transaction date itself.  (Meaning the date the financial event took place, not the date you entered it into the database.)

So far so good?
TheDavid
Tuesday, December 19, 2006
 
 
TheDavid explained what I was trying to say SOOOO much better than I could have.

Thanks, TheDavid.
Karl Perry Send private email
Wednesday, December 20, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz