Date dimension for multiple calendar

[bi_user]

We are working on a date dimension design with following requirements -

1. It should support standard date attributes.
2. It should support finance GAAP reporting
3. It should support call plan calendar defined at country and field force level
Sales Organization Hierarchy is Organization, business unit , field force unit, region, district , sales rep.
There is a concept of cycles. Some affiliates do not follow standard month for their call planing. Instead they use a concept of cycles - which consists of a set of pre defined days - suitable for their organization. So call plan can be defined at cycle level or at standard level also.
4. call plan calendar sometimes define at region level and at district level based on business in that affiliate geography.
5. it should support standard billing reporting


Pl suggest the approach of defining date dimension. It can not be accommodated in one dimension due to different grain. Which approach should be the simple , resilient and scalable?

Thank you very much for your advise.

[ngalemmo]

Use two dimensions: date and calendar. Use the date dimension to record calendar dates and the calendar dimension to record various regional interpretations of date. Use two FKs off the fact. The calendar dimension would have a natrual key of the date and whatever business unit is applicable to the calendar. Interpret and select the proper calendar row in ETL.

[elmorejr]

I have a similar situation and was a few weeks away from tackling it. I like the idea of the separate Calendar dimension. In my situation, there are different groups that have a need to classify various ranges of dates for their own purposes. For example, Group A may classify Nov. 2 ~ Nov. 8 as Holiday Sale Weekend. While Group B may classify Nov. 4 ~ Nov. 6 as Super Weekend.

There will be spareness for when no classification is needed.

My first inclination would be to include a "no classification/no event" entry for each group in the Calendar dim.

Thoughts?

And a 2nd bonus question: How would you handle overlapping events for the same group? (i.e. two simultaneous events that need to be tracked for a single transaction)

[Sponsored content]