Date Dimensions - Muliple Calendars

[Stevenw]

Hi,

We have an interesting challenge with our Date dimension. We need to carry multiple calendars - one per country, and one for the Group.
When loading the FACT, we create a record per invoice for the country and a record for the group calendars. This allows us to use a "Calendar Name" prompt on our reports which reduces the number of reports, but increases the data volumes in the DW.

Is there a better design approach to this problem?

Much appreciated.

Steven

[ngalemmo]

One option is to double up your date surrogate keys, one for the common calendar and the other for the country specific calendar. It's certainly better than doubling up the data.

There are a bunch of different ways you can implement this... one date dimension or multiple date dimensions, one for each calendar (essentially a subtype dimension). In the latter case you have only one surrogate key (as both the main calendar and the country specific calendar have the same primary key value) and the country specific subtype table only contains attributes that are unique by country.

What makes sense depends on how you plan to use it. For example, if the choice of country is restricted by user, there are various things you can do in the BI tool or the database to limit what someone can see to simplify queryies if you use the subtype approach. The subtype approach also allows restatement of other country's data using a different calendar... which may or may not be useful to the business.

[Stevenw]

Hi,

Very interesting...
The idea of using multiple date dimensions is very tempting. We do however have a constraint in our reports. Business have the requirement to change the calendar name in the same report and then to rerun the report against the different calendar. Somehow the reporting tool struggles with the dynamic switching of table names depending on the prompt input.

We are using the multiple calendars in the one dimension, but I think we have missed the trick of creating only one primary key (used as FK in FACT's) for multiple date records.("to double up your date surrogate keys") Would you mind expanding that a little further?
Do you mean we should have one date record, with different columns where appropriate? This brings us back to the problem with the report switching column names "dynamically".
To create multiple records for each date resolves the report issue, but what will the PK be?

Thank you,
Steven

[ngalemmo]

What will work depends a lot on the particular tool you are using.

The two key approach involves having all the different calendars in one table. One key points to the standard version of that date and the other key points to the country specific version of that date. In the BI layer you define two views of the calendar: standard and country specific and define joins to the appropriate view.

But, this approach does not allow you to dynamically switch to any calendar.

Another way is to define a compound primary key for the calendar table, made up of the date surrogate key (one value for each unique date... usually the date as a number in YYYYMMDD format) and country code, plus a code for the standard calendar. You then only need one surrogate key in the fact. A user would apply a filter on the date dimension to select the country they want. This would allow you to switch to any calendar in a manner any BI tool would support. You can support labeling by either carrying the country name in the calendar or have a list of values table with code and country name.

This approach is a bit 'off label', as the PK of a dimension should only be a single surrogate key, but it does do the job. The fact FK is only the surrogate key, not the entire PK. Some (probably most) modeling tools will allow you to do this. ERWin, for example, lets you define a role for the relationship if you define an index with only the date surrogate key. When you set the relationship properties, you can select that index and it will only propagate the surrogate key to the fact table. You can also define the index definition as 'logical only' so it will not actually generate that index in the physical model.

[Stevenw]

This sounds like it will work.

I will do some more tests, but the logic is sound.

Thank you !

Steven

[Sponsored content]