Date Dimension

[Daniel Eichenberger]

Hi all,

I develop a forecast model at the moment. The problem is that the requirements from business and financial side are different. The finance department is interested in monthly data, the business would need the data weekly reflecting every day for there resource planning. How could I set up the dimensions best.

The main problem is, that every year the weeks are different and the month start / end can be in the middle of the week. Are there easy ways to allocate days to weeks without a huge mapping effort?

Thanks you in advance for your support.

[Martin Ryan]

There's an indepth discussion of 1d vs 2d time here: http://forums.olapforums.com/viewtopic.php?f=3&t=207

For my money I would suggest you have one dimension (e.g. 'Days') for your input cube where days roll up into months and then into years. Then have a second cube that has months and years as two dimensions. This will then be fairly easy to rule across from cube A to cube B for your finance users to do their analysis.

As for your weeks requirement, the days dimension in cube A could have an alternative hierarchy that rolls up into the weeks required by your business users.

You may in fact just need the one cube and do it all with the two different hierarchies in the 'Days' dimension in Cube A. Depends on how your finance users want to slice their data.

Regards,
Martin