[b]Unusual Multiple Calendar Issue[/b]

[Nassy]

Hi everyone,

I have a requirement to build 3 cubes for a company which sells bouquets of flowers to different retailers.

Describing the issue

The client would like one of the cubes to be designed in a way so it makes use of multi-calendars. The client has a handful of customers who each have a separate calendar stored in a separate file. The client has asked me to deliver the following:

i. Design the cube so that the time dimension is based on the calendar for the selected customer.

ii. Design the cube so that if multiple customers are laid out side by side in the browser the standard calendar will be used.

I am not sure if this request is realistic. I have been thinking that I will need to create various time hierarchies for each customer and then the user will need to pick which hierarchy to use in the browser.

Where advice is needed

My reason for this post is to try to get other people's opinions on what approach they'd use if they had been given such a request.



Firsty: should I develop a time dimension for every calendar file, create lots of virtual cubes for each customer and link them? (is that possible even?), or just one time dimension table which contains the hierarchy for all the different customer calendars.

Secondly is there a completely different approach to this I haven't thought of.

[/color blue]

cheers.

Nassy

 

[johnherman]

Well, I find it hard to believe that your customers are on a different calendar, so please correct me if I am wrong. I presume your customers' calendars have 7 day weeks and 12 months a year Jan - Dec. If this is not correct, then you MOST CERTAINLY have a difficult situation (such as Chinese, Hebrew, or Arabic calendars which are lunar based rather than solar based). But even in that case, the problem is not insurmountable. Which situation iw yours?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Nassy]

Sorry for the very late response.

The customer calendars are all based on a standard calendar - it is just the sequence of the weeks that changes. The week always starts on a Sunday. For example: 05/03/2006 might be week 9 on the normal calendar but for customer A could be fiscal week 12 and for customer 15 could be fiscal week 15.

What I have done is set up a Time Dimension as such:

WeekSDate CalendarWeekNo TescoWeekNo WaitroseWeekNo
------- ------------- --------- -----------

This will allow me to create the different hierarchies ie
Tesco Hierarchy, Waitrose Hierarchy etc...

cheers

Nassy

 

[johnherman]

Yes, that is the most common solution. At my company, we also have that situation, with some constituent companies closing the week/month at COB Friday, while others close on Saturdays.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright