Time Dimensions-Best Practice

[rfielden]

The newbie is back!

We are in the process of using daily sales files to build a reporting cube. Can anyone recommend best practice for time dimensions as we need to capture: week # (with some years 53 vs 52), day (M,T,etc), GL period, etc. Is anyone doing something similar that could provide guidance?

Thanks, Rick.

[Martin Ryan]

Hi Rick,

There's a nice long debate about approaches to time here http://forums.olapforums.com/viewtopic.php?f=3&t=207

All of which says it depends. I'm generally a fan of 2 or 3 dimensions for time, but that's because I tend to do financial analysis. With sales, you may well find that a one dimension approach is a better fit and you'll find people argue strongly for both approaches.

That post will give you a feel for some of the issues.

If you can go into a little more detail on your requirements, we might be able to give some more specific advice.

Cheers,
Martin

[rfielden]

Looking for guidance on which time dimension structure, 1 dimension, 4 dimension, etc., to use to report daily sales and how the dimension(s) is structured. We are a retail company and need to report sales by day, week, month, and year. I need a structure that is easy to use for operational reports, in addition to analytical reports, week to date (by day), month to month, year to year, three year trend. I read discussions about 1 dimension versus 2 dimensions but that was directed at financial reports. I also read that users use 1 dimension for daily sales and some use 3 dimensions so I’m having a dilemma as to which way to go. We have to be able to report/analyze on day, week, month, year and also account for 52 vs 53 week years. Any insight and/or recommendations is greatly appreciated.

[Steve Vincent]

I'd be tempted with 3;

T_Days
T_Weeks
T_Years

T_Days to hold just Mon thru Sun
T_Weeks to hold W01 thru W53 with them split up in a hierarchy for each month (assuming the weeks fit nicely year on year unlike they do here)
T_Years to hold each year

Thats just my gut feeling on it. Depends how much time you have but might be worth writing several models in different ways and seeing which ones work best with your data. If you need to write rules as well then bare that in mind, as feeding models is a PITA at times and the way a cube is structured can either make that easy or a complete nightmare.

[John Hobson]

I do most of my work in retail and I have only ever used 1 dimension.

This is mainly because I have to deal with stock balances most of the time which need to flow week on week and I have a preference for using rules with dimix for this (long debate possible here) .

For just sales you COULD use Year, Week, Day dimensions but I am not sure how much it helps.

In fact for 53 week years it is probably easier of you can say in a single dimension that Year 08 is made up of week 1 to 53 08 and Year 09 is made up of Weeks 1 to 52 09.

I could probably be convinced that other approaches have equal validity though.

Best wishes

John

[jim wood]

Not the time debate again. John you'll be like a pig in muck.

As for my ten pence worth, I think it depends on your model. Sometimes a single time dimension works best, sometimes a multiple dimension approach works. For me what calculations on time you want to complete tends to answer the question. For example, if you are looking to do rolling balances then a single time dimeension can be easier.