Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Date dimension for multiple calendar

3 posters

Go down

Date dimension for multiple calendar Empty Date dimension for multiple calendar

Post  bi_user Sun Nov 13, 2011 5:33 pm

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.


Posts : 1
Join date : 2011-11-13

Back to top Go down

Date dimension for multiple calendar Empty Re: Date dimension for multiple calendar

Post  ngalemmo Sun Nov 13, 2011 7:23 pm

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.

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

Back to top Go down

Date dimension for multiple calendar Empty Sparse Calendar?

Post  elmorejr Tue Nov 15, 2011 10:19 am

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.


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)


Posts : 25
Join date : 2011-10-20
Location : Columbus, Ohio, United States of America, Earth, Sol

Back to top Go down

Date dimension for multiple calendar Empty Re: Date dimension for multiple calendar

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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