Time : 1d vs 2d

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Time : 1d vs 2d

Post by Steve Rowe »

OK so it was bound to happen in the end.

To stop other threads being hijacked by this issue which is one of the first design decisions everyone makes when they build a TM1 system, I thought I'd start a thread on the 1d vs 2d issue.

I don't have time to post my thoughts right now but I will do soon. The short answer is there is no right answer.

Anyway post away in here on this issue.
Enjoy, this could go on for ever!
Technical Director
www.infocat.co.uk
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Time : 1d vs 2d

Post by Andy Key »

If you're doing mostly historic reporting then 2d.

If you're doing mostly forecasting then 1d.

Mostly.

But not all the time.

And certainly never on a Tuesday.
Andy Key
ScottW
Regular Participant
Posts: 152
Joined: Fri May 23, 2008 12:08 am
OLAP Product: TM1 CX
Version: 9.5 9.4.1 9.1.4 9.0 8.4
Excel Version: 2003 2007
Location: Melbourne, Australia
Contact:

Re: Time : 1d vs 2d

Post by ScottW »

From an end user ease of use and ease of analysis perspective 2d is ALWAYS better.

Even for forecasting always 2d. Very easy to get around year start month / end month with attributes and time lookup cube. The only exceptions to this would be applications with complex linear time based calculations such as:-
- Magazine or pay TV subscription revenue
- Insurance underwriting earnings
- Asset depreciation tables
As for such calculations the rules are complex to write and can be handled much better using consolidations in a 1d time dimension

... but even in these cases the 1d cube can handle the calculations and the user can still interact with a 2d time cube by ruling values across. Then you get the best of both worlds.
Cheers,
Scott W
Cubewise
www.cubewise.com
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Time : 1d vs 2d

Post by David Usherwood »

I'm firmly in the 2d camp (or strictly the >1d camp). There are two arguments which carry weight with me:

a Maintenance - some OLAP tools (eg OFA - RIP) have built in structure maintenance of months/quarters etc - TM1 doesn't. You can work around it with XDIs but that isn't (I would assert) easy to maintain or (especially) communicate to your successor WTF you are/were trying to do.

b Rules - since the left hand side of a rule MUST be a fixed reference, you really don't want to have to maintain them every year. (Ajay, if you are reading this, remember the system you and I inherited?)

Having said that, there is often a need to link periods across year breaks. To address this, Martin R-C passed onto us some time ago a technique I adopted.

Build a 'flat' YYMM dimension, way out into the future, and create a set of references in a cube eg last month, next month, days in month, blah blah...

_Don't_ hold real data against it - but use it as a lookup when you need to to wrap arounds or whatever.

This has worked well for us - thanks Martin.

Back to the >1d issue. How do forumers handle daily data? My present favourite is 3 dims:
7 days to total
52 weeks to quarters to year
Flat year dimension
You have the usual 4/4/5 issue but that's a fact of life in mfg and retail.
Feedback welcome.
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 : 1d vs 2d

Post by Martin Ryan »

I'm also usually a big fan of 2+d. I would prefer 1d if rolling averages were required, or possibly if financial years didn't match calendar years, and analysis from both types of years were required.

On David's question of daily data, this is another instance I'd think about a 1d dimension, as the 1st of September might be in September one year and August the next. Guess it depends on what's important to you - daily/weekly/annual analysis, in which case David's method is better, or monthly analysis in which case I think a 1d approach would be better.

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
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Time : 1d vs 2d

Post by Eric »

I thought I was in the minority! :o I agree with all of the points made, especially the dreaded rewriting rules year after year.

>1 all the way. <High Fives & Chest Bumps to Scott, Martin, & David>

For Daily information I do the following

Dims
Year
Month
Day

This is great for any analysis. I can see weekly analysis being tricky, I have not needed it. However, off the top of my head I might try to get fancy with attributes for each year in the day and month dims to build week reference....maybe... need to think about it.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Time : 1d vs 2d

Post by Steve Rowe »

Obviously just my views, feel free to pick my argument apart. Sorry in advance for the long post!

Summary
1d
Pros
• Offers maximum flexibility and future proofing
• Most time series calculations can be done (at least partially) with consolidations, leading to performance gains.
Cons
• Is less user friendly in the cube viewer since it is harder to switch between years.
• Cube viewers laid out with periods on columns and years on rows are not possible.
2d
Pros
• It is more user friendly
Cons
• Very inflexible it usually only works for the “time structure” of the organisation as it stands when the system is built.
• Time series must be done with rules and which lead to performance overhead.

I think the best approach is 1d structure to hold the data where (hopefully) all the calculations happen and a 2d structure for the viewing and reporting of the results.

Detail.
To expand a bit more on my comments, really just to justify my point of view so we can discuss in more detail.

I’ve only included in my pros and cons the items that I think are of primary importance when designing a system. I think these can be categorised into two areas.

• Has an effect on the majority of users on a day to day basis.
o Performance and response.
o User friendliness.
o Ability to deliver required functionality.
• Restricts the ability of the system to cope with changes to it’s operating environment. “Future Proofing”

There are other issues that exist under the different approaches which I don’t think are as important so I’ve not listed them under pros and cons
Maintenance of rules.
I don’t really think that having to maintain rules is a good argument either way.
• If a rule applies to all years then you don’t have to specify anything on the LHS of the rule for either approach, these never need maintaining.
• If a rule applies to a whole year then in the 1d world the LHS can use a {‘P00-200x’, ….,’P01 -200x’} construct or an IF statement on the RHS. In the 2d world this is done with a ‘200x’ reference on the LHS. It’s not as pretty in the 1d world but not particularly challenging either.
Dimension layout “mess”.
In a 1d system the periods dimension tends to get very cluttered with lots of consolidations doing time series consolidations. Whilst this is messy it’s only messy for a very limited set of users. The vast majority of users consume TM1 data through a static Excel layout or will use subsets if using the cube viewer.

To go back to my main pros and cons.

Performance.
Where you can use rules as an argument is if it leads to a performance advantage. Here I think 1d may well win since consolidations for use in time series calculations can be done within the dimension definition. This saves both rule and feeder evaluation overheads and memory usuage.

In the 2d world for example it’s common to calculate a rolling YTD amount as the sum of three different consolidations.

i.e.
[‘Widgets’,‘Mar 12 Mth Total’ , ‘2008’]= N:[‘Widgets’,’Full Year’,’2007’] – [‘Widgets’, ‘Feb YTD’,’2007’] + [‘Widgets’, ‘Mar YTD’,’2008’];

This is considerably more work than referencing a consolidation.

Obviously if you are never for the life of the system going to do time series calculations then the above does not apply.

User friendliness.
Not much to say here I think 2d wins hands down.

Future Proofing.
This is where, for me, the 2d world is broken. When a system is built and lets say the period dimension runs for a financial year Apr-Mar. This is fine and will work with no issues. What happens then when the company changes to a financial year that is the calendar year?
Your system is basically broken and requires all the data to be exported the period dimension to be rebuilt and the data re imported and probably rules re written data reconciled etc. Basically you need to rebuild the system because your time structure is only capable of reporting in one way.
If you are storing YTDs rather than movements (a different discussion) then you are in an even bigger mess.
What happens when the organisation decides to report on both a financial and calendar year basis due to the requirements of head office. Again you have a major issue which requires two cubes to supply the results.
I’ve encountered all the above situations in mature 2d TM1 systems and it was much much more work to fix than you would think.
In a 1d system you can just reference a different consolidation in the period dimension.

Ability to deliver required functionality.
This obviously varies for every system. For the system I have now that is basically doing a group consolidation I need a 1d period dim in order to do the consolidations. This is because the operational part of the group operates on 13 4 week period basis and the non-op part of the group works on a calendar year basis. To complicate things further the 13 4 week periods are not all the same.

Anyway I think that results of this is that
• Data should be stored in a 1d cube, this future proofs your system.
• Where possible time series calculations should be done in the 1d cube leading to performance gains.
• 2d viewing cubes can be built to view the data in the 1d cube. It’s important that the 2d cubes never contain data or you lose your future proofing. In the 2d cube the rules should be very simple
[ ] =DB (‘1dCube’,………..);
and that’s it.

[Just to say for daily data I am firmly 1d on this too. Again for flexibility reasons, the operational bits of the business have weeks that start and finish on different days, this makes it hard to use other structures.]
Technical Director
www.infocat.co.uk
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Time : 1d vs 2d

Post by paulsimon »

Hi

As was said at the start of this thread there is no right answer.

In retail systems where the week and weekend are often all important I can see the advantages of the 3 d approach mentioned by David.

When I worked in that area I used a Year and Day dimension. The days were just numbers 1 to 366. I then used an Alias to cope with the fact that Day 1 might be 27 Dec 05 in one year and Day 1 might be 2 Jan 07 in the next year, since with a rolling week, the concept of a year is fairly arbitrary. Reconciling the concept of a week with a calendar month or year is never an easy task. Perhaps a 1d approach would work.

However, I have mostly dealt with monthly data. My last 4 clients have all been integrating companies with different financial years, and doing this with a 2d year and month approach is very difficult both at the cube level, and because of the impact on existing reports.

I have used a 1d approach for many years. I have never had a problem with having to maintain rules each year. This is largely because most of the calculations I want are handled in consolidations anyway. I only needed rules for the Growth and Growth Ratio, and with the Continue statement these rules are can be made generic.

I have worked a lot in insurance where most cubes have multiple time dimensions. When you have cubes with 5 time dimensions you don't want to use the 2d approach. Certainly with the cubes I had, with 5*2=10 dimensions gone to handle time I would not have had enough dims left to meet the old 16 dim limit on a cube.

For some types of analysis the 2d approach is more natural. Most TM1 Users are in Finance Departments and most General Ledgers have a Year and Period concept which translates naturally to the 2d approach.

However, the 2d approach does not support many common time series calculations easily such as:

Cumulative to Date
Moving Annual Totals
Moving Annual Averages

By comparison in the 1d approach these can be produced by simple consolidation.

The 1d approach also lends itself to Rolling Forecasts, eg the more enlightened companies who continually forecast 6-12 months out from the current month, rather than only doing the planning cycle once a year.

Reporting on different Financial Years can accomodated in the 1d approach by simply adding consolidations. I have written VBA programs that automatically generate the necessary consolidations.

Even if your company only has one Financial Year now, I can almost guarantee that at some point your company will be involved in some merger or acquisition that will need you to accomodate different financial years, at least during the integration phase.

Even if this is never likely to happen, being able to state data on different financial years can be useful for comparison with competitors accounts, etc.

Another point in favour of the 1d approach that has not been mentioned as yet, is the impact of MDX. MDX only recognises a single time dimension. This fits with the 1d approach. So if you plan to use MDX based front end tools at some point in the future, and you want to take advantage of any built in time series intelligence that they have, then this is another point in favour of the 1d approach.

As for usability, I have had many users happily using cubes with the 1d time dimension without difficulty. The key design technique is to make all the different consolidations join up, using zero weighting to avoid double counting. That way, instead of being presented with a mass of jumbled consolidations the used can drill down from a single top level consolidation to see that they can split data by Calendar or Financial Year, and then drill down further to see that they can split data by different types of time, such as Discrete Years and Months, Cumulative to Date, Year to Date, Moving Annual Total, etc.

The only other point mentioned was that with the 1d approach it is not possible to show a View with Periods across and Years down. That is true, however:

1) I have rarely seen that requirement
2) In the spreadsheet it can be accomodated, albeit with a little more work
3) There is always the option to rule across to a 2d cube for reporting purposes.

As I said at the start, there is no right answer, and I am sure that the different viewpoints here are all influenced by the types of systems that we have worked on in the past.

Regards


Paul Simon
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 : 1d vs 2d

Post by Martin Ryan »

PaulSimon wrote: For some types of analysis the 2d approach is more natural. Most TM1 Users are in Finance Departments and most General Ledgers have a Year and Period concept which translates naturally to the 2d approach.
I think this is a key point. The vast majority of projects I have been involved in have been financial reporting, or for the finance department in some way. Accountants tend to think in 2d and want to report in such a way. Thus I've always tended to think this way too.

The discussion in TM1 probably often comes up because 1d is (arguably!) applicable to more types of applications, but 2d is regularly used because TM1 applications are often financial applications.

As always, the answer is "it depends".

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
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Time : 1d vs 2d

Post by Eric »

First off
When you have cubes with 5 time dimensions you don't want to use the 2d approach
:shock: Head spinning, 5 time dimensions. Out of curiosity what where they?
PaulSimon wrote:For some types of analysis the 2d approach is more natural. Most TM1 Users are in Finance Departments and most General Ledgers have a Year and Period concept which translates naturally to the 2d approach.

I think this is a key point. The vast majority of projects I have been involved in have been financial reporting, or for the finance department in some way. Accountants tend to think in 2d and want to report in such a way. Thus I've always tended to think this way too.

The discussion in TM1 probably often comes up because 1d is (arguably!) applicable to more types of applications, but 2d is regularly used because TM1 applications are often financial applications.

As always, the answer is "it depends".

Martin
Plus everythign else mentioned
:idea: Maybe we are getting closer to a answer. Maybe we need to define certain types of cubes and then decide if 1d or >1d is better for that type of cube.


Financial Reporting Cube - 2 D because of natural transition for Finance users
Operation Cubes (Production Planning, Supply Chain Management) - 1 D Statistical cubes benefit from time being a continuum
Forecasting/Simulation Cubes - 1 D Statistical cubes benefit from time being a continuum

:idea: Maybe there are only 3 types of cubes

1) Data Storage Cubes - ?? D
2) Analytical Cubes - 1 D
3) Reporting Cubes - >1 D

Comments, ideas?


I apologize for the random stream of thought, but I think it was needed. I really think we as a groups are smart enough to get away from "it depends" and come up with best practices, knowing that sometimes there is a deviation from the best practice.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
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 : 1d vs 2d

Post by Steve Vincent »

«finds spanner and calculates trajectory to "The Works"»

I've worked with TM1 for 3.5 years, most of that being for a Financial organisation. In that time i've never used a cube with more than 1 time dimension!

One best practice here is dependant upon the "lifetime" of the cube. In my case they were annual, each year we'd take the "current" cubes (actual, forecast etc) and make a copy (2007actual, 2007forecast) including doing the same for the dimensions. That meant we only ever needed 1d for time, either year split by period or week (depending on the cube).

It's only when the cubes are rolling over year ends that it seems people look to the 2d approach.
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
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Time : 1d vs 2d

Post by Eric »

«finds spanner and calculates trajectory to "The Works"»
Huh?
I've worked with TM1 for 3.5 years, most of that being for a Financial organization. In that time i've never used a cube with more than 1 time dimension!

One best practice here is dependant upon the "lifetime" of the cube. In my case they were annual, each year we'd take the "current" cubes (actual, forecast etc) and make a copy (2007actual, 2007forecast) including doing the same for the dimensions. That meant we only ever needed 1d for time, either year split by period or week (depending on the cube).
New cube(s) every year and for every version. That is a approach I do not think I even considered, but as you have pointed out it does get the job done.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
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 : 1d vs 2d

Post by Martin Ryan »

Eric wrote:
«finds spanner and calculates trajectory to "The Works"»
Huh?
The term Steve's referring to is "throwing a spanner in the works".
Steve Vincent wrote:One best practice here is dependant upon the "lifetime" of the cube. In my case they were annual, each year we'd take the "current" cubes (actual, forecast etc) and make a copy (2007actual, 2007forecast) including doing the same for the dimensions. That meant we only ever needed 1d for time, either year split by period or week (depending on the cube).
So you've got different cubes for different scenarios? Why? Isn't avoiding such a situation the very point of having OLAP?
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
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Time : 1d vs 2d

Post by Steve Rowe »

[flash=]*Warning thread hijack alert*[/flash]

err I guess thats not what flash means....

I think we are getting into a financial accounting approach here, if all your years and versions are independent of each other then all your consolidation structures are as well. This means you don't risk (as much)disturbing your reported values when your hierarchies change. I'm guessing this is the reason for the approach.

Cheers,
Technical Director
www.infocat.co.uk
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Time : 1d vs 2d

Post by Eric »

Martin Ryan wrote:
Eric wrote:
«finds spanner and calculates trajectory to "The Works"»
Huh?
The term Steve's referring to is "throwing a spanner in the works".
Was going to say "huh" again, but threw spanner in my handy dandy translator....Spanner = Wrench....... :idea: I get it now!
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
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 : 1d vs 2d

Post by Steve Vincent »

Steve Rowe wrote: I think we are getting into a financial accounting approach here, if all your years and versions are independent of each other then all your consolidation structures are as well. This means you don't risk (as much)disturbing your reported values when your hierarchies change. I'm guessing this is the reason for the approach.
Yep, we don't create a cube per version / scenario but we do create a new one each year. It's because at the start of each new year we get a raft of changes to various hierarchies, some of which would totally ruin any historical reporting. So we create a new cube, copy all the dims and have that as the historic data. That way the new hierarchies don't affect the old data and it keeps the auditors happy and allows generation of reports to accurately reflect the business at that time.

Spanner / Wrench
Pavement / Sidewalk
Motorway / Freeway

Don't you just love the "English" language?!!!!
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
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Time : 1d vs 2d

Post by Steve Rowe »

Hmmmm,
Eric said
Maybe there are only 3 types of cubes

1) Data Storage Cubes - ?? D
2) Analytical Cubes - 1 D
3) Reporting Cubes - >1 D
I'm mostly with you as far as there is any point in generalising this much...
Data storage cubes - 1d for future proofing time
Calculations - Whatever you need to get the job done in the most efficient way
User facing - 2d for user friendliness

I think the problem is that normally all these things happen in the same cube so the user friendliness tends to win, personally I think the future proofing should win.
Night night
Technical Director
www.infocat.co.uk
Post Reply