Date dimension for multiple calendar

View previous topic View next topic Go down

Date dimension for multiple calendar

Post  bi_user on 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.

bi_user

Posts : 1
Join date : 2011-11-13

View user profile

Back to top Go down

Re: Date dimension for multiple calendar

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

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

View user profile http://aginity.com

Back to top Go down

Sparse Calendar?

Post  elmorejr on 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.

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)

elmorejr

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

View user profile

Back to top Go down

Re: Date dimension for multiple calendar

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