Time Dimensions-Best Practice

Post Reply
rfielden
Posts: 122
Joined: Wed Aug 06, 2008 2:50 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: Tega Cay, SC

Time Dimensions-Best Practice

Post by 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.
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Time Dimensions-Best Practice

Post by 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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
rfielden
Posts: 122
Joined: Wed Aug 06, 2008 2:50 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007
Location: Tega Cay, SC

Re: Time Dimensions-Best Practice

Post by 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.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Time Dimensions-Best Practice

Post by 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.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: Time Dimensions-Best Practice

Post by 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
John Hobson
The Planning Factory
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Time Dimensions-Best Practice

Post by 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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Post Reply