One vs Two Time Dimensions
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
One vs Two Time Dimensions
Hi All,
We are in the process of rebuilding our TM1 setup as we upgrade our ERP. The subject came up as to whether one or two dimensions to measure time should be preferred. We currently have two, one for Year (2017, 2018, 2019 etc) and one for Period, which is an organization of months ( Jan, Feb, Mar, Q1, Mar YTD, Year etc).
We do some 12 month trend analysis, for which a single dimension approach would be easier for ad hoc work. It might also be easier to control security for certain periods. On the other hand, year over year analysis is hard and it requires a bit more maintenance.
Does anyone out here have experience switching from two dimensions to one or vica versa? I would love to hear what reflections the forum has.
We are in the process of rebuilding our TM1 setup as we upgrade our ERP. The subject came up as to whether one or two dimensions to measure time should be preferred. We currently have two, one for Year (2017, 2018, 2019 etc) and one for Period, which is an organization of months ( Jan, Feb, Mar, Q1, Mar YTD, Year etc).
We do some 12 month trend analysis, for which a single dimension approach would be easier for ad hoc work. It might also be easier to control security for certain periods. On the other hand, year over year analysis is hard and it requires a bit more maintenance.
Does anyone out here have experience switching from two dimensions to one or vica versa? I would love to hear what reflections the forum has.
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: One vs Two Time Dimensions
What version are you on?
If you have access to workspace I would go with a single time dimension but use alternative hierarchies so you can view it by 2 as well. Best of both worlds then. (Admittedly with a bit of extra maintenance but it’s theoretically easy enough when you get your head around that way of working.)
If you have access to workspace I would go with a single time dimension but use alternative hierarchies so you can view it by 2 as well. Best of both worlds then. (Admittedly with a bit of extra maintenance but it’s theoretically easy enough when you get your head around that way of working.)
Declan Rodger
- Steve Rowe
- Site Admin
- Posts: 2417
- 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: One vs Two Time Dimensions
In principle the new hierarchy functionality means the classic 1d vs 2d argument goes away, everyone should go 1d and use hierarchies to deliver the cross-tab year / month analysis.
Unfortunately in the absence of a "DBRH" type formula, in practice a lot of hierarchies based designs remain a paper exercise in theory crafting as the results can not be delivered to the users (in the traditional way), nor can existing packs be converted.
Even once that arrives (and it is alleged to be soon) I suspect we'll find that the rules syntax is not ready yet either.
If you have plenty of internal resource and hardware there is nothing wrong with having both builds in your system. Load your data into 1d and use this to drive time series analysis and rolling totals and averages. Then have a reporting cube that is in 2d and use this for reporting, this can link back to your 1d cube to pull the calculated time series values forward, I'd probably populate the main data set for the reporting cube with a TI though.
RAM is (or should be) cheap and shouldn't constrain your designs.
HTH
Unfortunately in the absence of a "DBRH" type formula, in practice a lot of hierarchies based designs remain a paper exercise in theory crafting as the results can not be delivered to the users (in the traditional way), nor can existing packs be converted.
Even once that arrives (and it is alleged to be soon) I suspect we'll find that the rules syntax is not ready yet either.
If you have plenty of internal resource and hardware there is nothing wrong with having both builds in your system. Load your data into 1d and use this to drive time series analysis and rolling totals and averages. Then have a reporting cube that is in 2d and use this for reporting, this can link back to your 1d cube to pull the calculated time series values forward, I'd probably populate the main data set for the reporting cube with a TI though.
RAM is (or should be) cheap and shouldn't constrain your designs.
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Re: One vs Two Time Dimensions
Thanks for this. Right now we are on 10.2.2, but are upgrading to PAx before our rebuild). We are actually restricted on RAM because it's allocated on a virtual server and IT is stingy with what they give.
- Steve Rowe
- Site Admin
- Posts: 2417
- 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: One vs Two Time Dimensions
Unfortunately this seems to be a bit of a common story. Running in a VM with the ability to easily add RAM to a TM1 model should be a feature of your infrastructure to adapt to a changing environment in a dynamic and cost effective manner. It does seem to have drifted into being a hard constraint. Your quote should readWe are actually restricted on RAM because it's allocated on a virtual server and IT is stingy with what they give.
For me getting your operating model wrt to RAM / VMs and your ability to vary this in a dynamic cost effective manner is as, if not more, important than getting your TM1 design and requirements correct.We are flexible with RAM because it's allocated on a virtual server and IT are happy to facilitate the needs of the business.
(If you are on enterprise TM1 then don't forget that you need ILMT as well).
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: One vs Two Time Dimensions
Just as a heads up, if you're going from 10.2.2 to PA2.0+ then expect a significant increase in RAM consumption. We are moving to the cloud and our production server uses about 50GB or RAM. When we moved it to the cloud at 64GB it wouldn't even come up. We are having to upgrade our cloud environment to 128GB.
-
- MVP
- Posts: 3659
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: One vs Two Time Dimensions
Yeah. Nah. Not quite.Steve Rowe wrote: ↑Sun Dec 02, 2018 10:00 am In principle the new hierarchy functionality means the classic 1d vs 2d argument goes away, everyone should go 1d and use hierarchies to deliver the cross-tab year / month analysis.
While in the past I have been unreservedly in the "multiple time dimensions camp" with the advent of alternate hierarchies I am now very firmly in the "single time dimension camp" for all reporting applications. However, there are still cases for data input (forecasting) applications where separate year and month dimensions is still a much better fit to requirements. For example any forecast application that makes heavy use of spreading to copy from one year/version to another year/version. Once you go single dimension although this can still be achieved with a combination of TI and lookups there is no way for users to just do it themselves via simple right-click or spreading shortcuts.
For any read-only application I really see no compelling reason to remain with multiple time dimensions. Although certain KPIs like YoY growth might be easier to write with separate year and month dimension they also aren't that hard to solve using a single time dimension. But for forecasting, especially single year forecasting models (as opposed to rolling forecast) look at the requirements as split might still be worth maintaining.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 36
- Joined: Fri Mar 10, 2017 8:26 pm
- OLAP Product: TM1
- Version: PA 2.0.8
- Excel Version: 2016
- Location: Dallas, TX
Re: One vs Two Time Dimensions
Thank you, everyone, for these very helpful replies. Given that we do a lot of budget work and year-over-year analysis and very little rolling 12 month trending, there may not be much of an advantage in 1D from our business perspective.
On a similar note, does anyone have thoughts, one way or the other, on "measures" dimensions? We have a few that rule-calculate amounts in thousands and millions for certain cubes. IBM recommends them to us, our outside consultants recommend against them. Our main cube currently has 3GB of data, although we are hoping to get it under 500MB in our rebuild. Does anyone find measure dimensions particularly helpful or hurtful as far as functionality, efficiency, or even usefulness?
On a similar note, does anyone have thoughts, one way or the other, on "measures" dimensions? We have a few that rule-calculate amounts in thousands and millions for certain cubes. IBM recommends them to us, our outside consultants recommend against them. Our main cube currently has 3GB of data, although we are hoping to get it under 500MB in our rebuild. Does anyone find measure dimensions particularly helpful or hurtful as far as functionality, efficiency, or even usefulness?
- 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: One vs Two Time Dimensions
Hi
Be very careful about upgrading if you are short on RAM. PA can easily use 50 to 100%+ more than it did in previous versions.
I have used a single time dimension for many years. This is because I have spent a lot of time working in insurance which needs time dimensions with different meanings such as inception month, earning month, accident month, debit month, etc. So as you can imagine if you have separate year and month dimensions for each of these the cube will get unwieldy.
Even in conventional Finance systems I still find that there is often a requirement for more than one type of time dimension, eg at more than one customer they have wanted an Effective Month and a Planning/Posting month. The Planning/Posting Month tells you the month in which the forecast was made or the budget was set, and the Effective Month dimension tells you when the forecast or budget was expecting things to happen. This allows the accountants to do comparisons of eg forecasts made at different times in the year, to see how accurate earlier forecasts were in comparison with more recent forecasts. This seems to be a common requirements. Again, this gets unwieldy if you have separate year and month dimensions since you need 4 dimensions for the two different types of time.
When it comes to year on year analysis, the time dimension builder that I use generates consolidations that give a year on year difference. I would agree that if comparing phasing across months in different years then the two time dimension approach is better. However, often when comparing year on year, you are only comparing eg Current Period, YTD, or Full Year, and that is just a matter or picking out the relevant periods from your single time dimension.
The only real requirement we have for any period by period year on year comparison that we have is for Actuals so I just added a Prior Year Actual version and pulled the data forward via rules. The a period by period year on year comparison is just a matter of comparing the two versions. This gives a performance hit but overall we get more benefit from using the single time dimension. We have to do a lot of multi year forecasting and budgeting which is easier for users to visualise with the single time dimension.
There is never going to be a perfect answer and it will always depend on your particular application.
One area where two dimension dimensions definitely fall down heavily is on company mergers, etc, where the financial year can change. With separate year and period, you can end up re-designing the system. With a single time dimension you can just add different month consolidations.
If your dimension only goes down to month level you should be OK. However, if it goes down to day level, I would definitely separate off the day part. We tried a day level dimension in PA, that was fine in 10.1. However, because PA insists on creating all possible MUNs at server startup the increase in RAM was enormous. We therefore had to remove all but the bare minimum of alternate hierarchies from the day level dimension.
On your query about measures, if you just want a measure to be expressed in thousands, millions, etc, it will in general be more efficient to do this by consolidation than rules. Rules are around 100 times slower than consolidation. They also need to be fed which takes up space, whereas the consolidation is naturally fed by the underlying value in units and does not need a feeder, and takes up no space at all if it is not actually requested.
Regards
Paul Simon
Be very careful about upgrading if you are short on RAM. PA can easily use 50 to 100%+ more than it did in previous versions.
I have used a single time dimension for many years. This is because I have spent a lot of time working in insurance which needs time dimensions with different meanings such as inception month, earning month, accident month, debit month, etc. So as you can imagine if you have separate year and month dimensions for each of these the cube will get unwieldy.
Even in conventional Finance systems I still find that there is often a requirement for more than one type of time dimension, eg at more than one customer they have wanted an Effective Month and a Planning/Posting month. The Planning/Posting Month tells you the month in which the forecast was made or the budget was set, and the Effective Month dimension tells you when the forecast or budget was expecting things to happen. This allows the accountants to do comparisons of eg forecasts made at different times in the year, to see how accurate earlier forecasts were in comparison with more recent forecasts. This seems to be a common requirements. Again, this gets unwieldy if you have separate year and month dimensions since you need 4 dimensions for the two different types of time.
When it comes to year on year analysis, the time dimension builder that I use generates consolidations that give a year on year difference. I would agree that if comparing phasing across months in different years then the two time dimension approach is better. However, often when comparing year on year, you are only comparing eg Current Period, YTD, or Full Year, and that is just a matter or picking out the relevant periods from your single time dimension.
The only real requirement we have for any period by period year on year comparison that we have is for Actuals so I just added a Prior Year Actual version and pulled the data forward via rules. The a period by period year on year comparison is just a matter of comparing the two versions. This gives a performance hit but overall we get more benefit from using the single time dimension. We have to do a lot of multi year forecasting and budgeting which is easier for users to visualise with the single time dimension.
There is never going to be a perfect answer and it will always depend on your particular application.
One area where two dimension dimensions definitely fall down heavily is on company mergers, etc, where the financial year can change. With separate year and period, you can end up re-designing the system. With a single time dimension you can just add different month consolidations.
If your dimension only goes down to month level you should be OK. However, if it goes down to day level, I would definitely separate off the day part. We tried a day level dimension in PA, that was fine in 10.1. However, because PA insists on creating all possible MUNs at server startup the increase in RAM was enormous. We therefore had to remove all but the bare minimum of alternate hierarchies from the day level dimension.
On your query about measures, if you just want a measure to be expressed in thousands, millions, etc, it will in general be more efficient to do this by consolidation than rules. Rules are around 100 times slower than consolidation. They also need to be fed which takes up space, whereas the consolidation is naturally fed by the underlying value in units and does not need a feeder, and takes up no space at all if it is not actually requested.
Regards
Paul Simon