Multiple fiscal calendars

We are an aggregator. We sell and ship our own products and act as distributors for a number of other entities. We would like to give our distributees access to our internal BI reports dealing with invoicing and shipping. They each have the potential to have different fiscal calendars and we would like them to be able to run the same reports against our fiscal calendar or against theirs. We need to be able to add new distributees constantly. What is a reasonable way to model this ?
Thanks

The simplest way is to include all the fiscal calendar attributes in the normal calendar date dimension. Normally the fiscal attributes are only applicable to aggregate level (Week, Month, Quarter and Year) and de-normalising them in date dimension is quite valid. The point is you still have a single date dimension entry for its role in the fact table. All dimensions in a good dimensional model should be easily extensible.

If your date dimension is already too wide, and you have many different fiscal calendars, then you may want to create a separate fiscal date dimension with all the possible fiscal attributes and same date key as primary key. In this way your normal date dimension is static and your fiscal date dimension keeps extending and you still only need one date key in the fact table.

Another approach would be to maintain separate schema for each distributor. Each schema would contain their particular version of the calendar (same table name, same keys, different attribute values) as well as synonyms/views referencing the rest of the DW located in a common shared schema.

This assumes the number of distributors is manageable and that the intent is to set up accounts for each in the database. You can also include a control table or views in each schema to force filters on particular attributes to limit their views to their data only.

What about a table that has the fiscal year definition for each client and the Date Key? If the Date Dimension has 36,000 rows, this table would have a maximum of 36,000 rows * the number of clients. Use a reference table to list the benining and ending of each fiscal year and any other way they want to see their data (fiscal quarter, etc).

With indexing, the performance of any joins with the table should be fast. Just be careful not to include the table in a join and then forget to put in a where statement selecting the client.

What we did was to create the following:

1. Base Calendar Dimension - strictly limited to the Non-Fiscal attributes. Choice of Primary key is totally your preference. Unless your enviroment uses more that just the Gregorian Calendar, I would suggest not using a sequential surrogate key, since it does not really buy you anything. From what I gather, many are either using the YYYYMMDD format, or simply using the actual DATE field.

2. Fiscal Calendar Dimension - Should include the following key attributes: Sequential Surrogate Key, PK of the Base Calendar Dimension, Fiscal Type Code (1 Type Code per Entity), Fiscal Attributes. Grain of the Fiscal Calendar Dimension is 1 record per Fiscal Date per Vendor.

This eliminates the need for the ETL to perform a Calendar SK lookup for the Fact tables. Simply convert it to the YYYYMMDD format, or just store it as a Date.

When running a query, simply join the Fact to the Fiscal Calendar Dimension using the Base Calendar Dimension PK, and also defining your Fiscal Type Code filter. This way, you can report your Fact records against any version of Fiscal Calendar.

Hope this helps.