Multiple fiscal calendars

View previous topic View next topic Go down

Multiple fiscal calendars

Post  rcman on Fri Jul 16, 2010 12:36 pm

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

rcman

Posts : 1
Join date : 2010-07-16

View user profile

Back to top Go down

Re: Multiple fiscal calendars

Post  hang on Tue Jul 20, 2010 7:20 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multiple fiscal calendars

Post  ngalemmo on Tue Jul 20, 2010 12:30 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Try a bridge table

Post  Jeff Smith on Wed Jul 21, 2010 1:28 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multiple fiscal calendars

Post  juz_b on Thu Aug 05, 2010 4:20 pm

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.

juz_b

Posts : 17
Join date : 2009-02-07

View user profile

Back to top Go down

Re: Multiple fiscal calendars

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum