Multi enterprise Calendar Dimension

Current I'm working for a Enterprise organisation that provide multiple vendors over the world with contract information.
My question is what is the best practice for creating a Calendar structure that can support multiple different time zones and fiscal periods.


I already did read article:
http://www.intelligententerprise.com/010629/warehouse1_2.jhtml;jsessionid=4Q4IJW5WTIH4IQSNDLOSKH0CJUNN2JVN

And the Dimensional Modeling Guide page 239 Country specific calendar...

Both ways are snowflakes but used in a different style. The old article uses a snowflake key in the Dim_Calendar and Dim_SubCalendar Dimension. The book uses the Calendar_Dim_Key to link to the Snowflake Dimension. What is the best practice?

Let's say that we have 5 vendors that all use a different fiscal period. For Example Vendor X fiscal period always starts on the first day of the year 1-1-yyyy Vendor Y on the last day of the first quarter etc. and all other vendors also use a different fiscal period. What would you do?

The first I can think of is creating a large wide calendar dimension wit all date attributes duplicated in columns with the vendor name: Year_VendorX FiscalPeriod_VendorX Year_VendorY FiscalPeriod_VendorY etc Maby you can do this for small number of different vendors but what when you have 50 vendors?
(this is just an example to see what the ideas are or the best solution will be)

The second I thought of is that you create a Dim_Calendar with a VendorKey Column like:
Dim_Calendar_Key
Dim_Vendor_Key
Year
Quarter
Month
Fiscal Week
Fiscal Week Number in Year
Fiscal Month
Fiscal Month Number in Year
Fiscal Year-Month
Fiscal Quarter
Fiscal Year-Quarter
Fiscal Half Year
Fiscal Year
etc....

Putting 10 year / 5 vendors in this dimension will result in arround 18300 lines so not a real big dimension yet.
Even 50 vendors will return 183000 lines still not that big. Even 50 years and 50 vendors are not more then 915000 rows..
What will the problems with this solution be? The way to join to the fact? Maintenance on dimension changes? What more?

So should we choose a snowflake solution? What would this then look like in a real life example?
The basic example in the book only gives only a sample of the holiday flags or workday indicator but what would it look like for different fisical periods?

Dim_Calendar
----------------------
Dim_Calendar_Key (PK)
Year
Quarter
Month
Fiscal Week
Fiscal Week Number in Year
Fiscal Month
Fiscal Month Number in Year
Fiscal Year-Month
Fiscal Quarter
Fiscal Year-Quarter
Fiscal Half Year
Fiscal Year
etc....

Dim_Calendar_Enterprise
--------------------------
Dim_Calendar_Key (FK)
Dim_Calendar_Enterprise_Key (PK)
TypeName
Fiscal Week (flag or no flag?)
Fiscal Week Number in Year(flag or no flag?)
Fiscal Month(flag or no flag?)
Fiscal Month Number in Year(flag or no flag?)
Fiscal Year-Month(flag or no flag?)
Fiscal Quarter(flag or no flag?)
Fiscal Year-Quarter(flag or no flag?)
Fiscal Half Year(flag or no flag?)
Fiscal Year(flag or no flag?)
FirstDayOfWeek_Flag
Holiday_Flag
etc...

Could this also support different relations not only vendor, but maby also supporting country?



Or should you create multiple Calendar Dimension? one for each vendor...


Is just to trigger anwsers so please let me know what you would think of.

Hi there,

We have a similar situation and we chose to take the snowflake approach.

The advantage to this approach is as follows:

1. Since the Gregorian Calender is pretty standard throughout the rest of the world, have a Base Calendar Dimension (strictly no Fiscal attributes) simplifies your ETL lookups, since you will only require an actual Date value to perform the lookup.

2. Having a Enterprise Calendar (with an FK to the Base Calendar) allows you to add the Enterprise Specific Attributes add needed. This will allow you to easily expand the number of Enterprise and Attributes as needed. This is further qualified by a FiscalCalendarType indicator.

The responsibility now lies with the report writers to fully qualify the joins to the EnterpriseCalendar (Dim_Calendar_Enterprise) using the BaseCalendarKey plus the FiscalCalendarType (in your case, the Dim_Calendar_Key and the TypeName ) to retrieve the Fiscal attributes of the Enterprise Calendar dimension.

Hopes this helps.

Hello juz_b,

Thanks for your explanation. I already started on using the snowflake method. But your explanation did clearify it a bid more.

Best regars,
Arjan