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 dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendars.
How do I handle that ?
If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact and
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calendars.
> How do I handle that ?