Thousands of Fiscal Calendars

I'm involved in a project which is DW as a Service. We will eventually migrate almost 50 thousand of our customers to it. These customers are in separate OLTP source databases and I'm using SSIS to load each into one enterprise DW.

My problem is that each customer may have a different Fiscal Calendar. While it is unlikely that if I have 50,000 source databases, that I would end up with that many fiscal calendars, it is not impossible that I will end up with thousands of variations in Fiscal Calendar. This is particularly problematic when you include the fact that each customer may have different "Workdays" and those would be kept via this same model as well. So, as I see it, I have a few options.

1. Have one primary Calendar Table including standard and fiscal calendar info with a set of records for each customer. This would likely create performance issues.
2. Split out each customer's Calendars into separate tables potentially creating a maintenance issues.
3. A multiple table setup where the standard calendar is separate and then use a separate table to keep track of each customer's fiscal calendar. A new fiscal calendar would only be added when a new variation was needed. This still may be a performance issue when the Workdays problem is addressed because we'll have so many variations.
4. Model each Calendar Year as a unit which will keep the number of variations among the Fiscal Calendars down?
5. Work on my resume. jk

We will mostly be consuming the data in SSAS Cubes and Cognos Cubes.

Have any of you tackled something like this before and can give some advice?

Why not create a Fiscal Year Dimension table? I assume a row in a fact table can belong in 1 and only 1 Fiscal Year. If that's the case, then wouldn't a column in the fact table that essential said this row belongs the Fiscal Year X work? The Fiscal year Dimension Table could contain the Date Dimension Keys for the beginning and end of the fiscal year and you might even include the Customer Key in the Fiscal year Dimension to faciliate assigning the key to the fact table. You could load the fact table with the Date Dimension Key and the Customer Key. Then you could assign the Fiscal Year Key by setting the Customer Keys equal to each other and the Date Key between the begin and ending date keys on the Fiscal Year Dimension.

I was thinking of a similar design to Jeff, but rather than a Customer Key I think you could have a Calendar Type Key (as many of your customers will share common calendar definitions). This would help reduce the volume of the Fiscal Calendar table.

Pardon my ignorance on this, but if there are only 365 days in a year, how can you have 1000's of different fiscal calendars?

I'm agreeing with Box and Lines. I don't see how you can have more than 365 fiscal calendars. But the same solutions apply.

Jeff Smith wrote:I'm agreeing with Box and Lines. I don't see how you can have more than 365 fiscal calendars. But the same solutions apply.

You can have a month based calendar, a 4-5-4 calendar, a 13 period calendar, a semester calendar, a trimester calendar, different days to start a week, yeah... it can get into the 1000's, but not very likely...

But, if you are going to shove everybody in the same database and you want to give them the appearance of control and ownership of their data, you are going to wind up implementing a calendar for each client. This allows each to tweak it without messing up somebody else.

Frankly, I'm not keen on the idea of putting everybody into the same database. Security, control, and loads will be major issues.

I'm still trying to get my head around the logistics of integrating 50,000 data sources.

As for the Fiscal dimension, the more complex you make the dimension, the harder it be to manage (and larger it will be).

For example, if you keep the dimension simple (FYear --> FQtr --> Fmonth --> Fweek), then the dimension will be straightforward, as the complexities of the 50,000 versions will be hidden in the staging/ETL process.

If the dimension includes more complex attributes (e.g. Fiscal Week Start_date, Fiscal Week End_date, aging etc), then the combinations within the 50,000 clients will grow significantly).

Thanks for the input guys. I have to be general in the details I provide because my employer considers this project sensitive.

Basically we have multiple levels of our business to address. Imagine if you were selling analytics to companies which manage companies beneath them. So, if Management Company "Acme" had a fiscal calendar and they had two sub companies named "Rockets" and "Anvils" which had their own fiscal calendars, that is my situation. Acme needs to be able to look at the data using their Fiscal Calendar, but also be able to drill down into the Sub Companies according to that level of Fiscal Calendar as well.

I have almost 50,000 Management Companies (Acme types), and they can have up to a few thousand sub companies under them. The whole thing gets overwhelming quickly.

Also, when you consider that I am pulling these fiscal calendars out of OLTP like databases for each Management Company and Sub Company and these companies don't specify their fiscal calendars at the same time and not to the same future date, then you see that I really need to have a Fiscal Calendar for each and every company. This is especially true if they are going to specify their workdays accurately.

I'm not sure how to deal with this but I do need to come up with a solution soon.

Time is a fundamental dimension in most DW (Data Warehouse) schemas. When you analyze the data in the DW, you almost always need to consider time to get a meaningful answer to most questions.

One of the first things that I learned when modeling DW schemas was that there were often many time measures used outside of the DW, but during the ETL (Extract-Transform-Load) process into the atomic level of the DW I had to pick one time or the model (and the users) would go insane!

OLTP and ERP systems need to deal with time on a parochial level, where holidays, fiscal years, etc. have very important meaning. The DW needs to "rise above" these details to provide a clearer view of the data that is not encumbered by the component calendars, currencies, etc. and "gets to the meat of the problem" instead of dealing with the details.

When dealing with multi-national organizations, there are frequently fundamentally incompatible calendars. As an example, when dealing with one client I have the bulk of the organization using the western Gregorian calendar, but with important sub-groups dealing with the Hebrew, Arab, and two Asian calendars which are lunar based instead of solar based. All of the DW data is rolled up and reported using the Gregorian calendar, because there is no way to produce usable/comparable data for the users with cubes running on different calendars!

Your users may be able to cope with different calendars. I'll bet that they can't cope with multiple cubes based on those calendars, because there is no way to produce consistant, comparable, and repeatable results.

The short answer boils down to pick one calendar and in the ETL step that pulls data from the authoratative source into the atomic level of the DW make the conversion to that one calendar. I've had by far the best luck with converting to the Gregorian UTC calendar, although I've used several different calendars for specific purpose DW projects.

Note that converting to a single calendar for your DW schema emphatically does NOT preclude you from building DM (Data Mart) structures like star schemas or cubes using alternate calendars. Your DW might be in UTC and from that you can produce multiple DMs (cubes) in whatever time units are handy for the DM being built (Hebrew, CDT, UTC, etc) as you and your users see fit.

-PatP