TM1 Time Dimension Design Approaches

By Alex Reznikov

Background

Every TM1 implementation involves designing a Time dimension. In this post I wanted to summarize different criteria that can be used when selecting an approach for a specific application.

There are many design options that a TM1 modeller can choose from; however there are two main known approaches to the time dimension design: “discrete” and “continuous”. Both methods are valid and have their pros and cons.

Option 1: “Continuous” approach – single dimension

Months and years combined into a single Month dimension:

Y2010
1. Jan-10
2. Feb-10
3. Mar-10
4. …

Option 2: “Discreet” approach – two dimensions

Month and Year are two separate dimensions:

All Years
1. Y2010
2. Y2011
3. …

All Months
1. Jan
2. Feb
3. Mar
4. …

Options Comparison

Criteria	Option 1: Continuous	Option 2: Discreet
Overall design	Some users may consider it to be less straightforward, but not significantly harder to explain and understand.	Easy to understand by users; however may require time navigation cube (described below) that will complicate design.
Maintenance	Add year element and respective months every year. Discreet is easier to maintain; however addition of elements in both approaches can be easily automated through a Turbo Integrator process.	Easy to maintain. Just add one more element to Year dimension each year.
Time Series Subsets definition	Easy to meet any time series, relative time requirements. Rich selection of MDX functions (Lag, Lead, ParallelPeriod ...) can be applied to a single dimension to generate required subsets. Attributes can be easily assigned to each period to simplify reporting and rules, e.g. prior_period, next_period... Overall, this approach is much easier to use when application has extensive relative time intelligence requirements, and especially in a rolling forecast scenario.	Hard to define subsets that cross year boundaries, e.g. next 12 months. This requirement is typical for rolling forecasts. Relative time requirements, e.g. prior or next period are typically resolved by creation of a separate time navigation cube with year, month and measure dimensions. This cube is then used to derive time periods relative to the one being specified. This approach is a workable one; however it significantly complicates reporting and business rules implementation.
Multiple Fiscal Calendars	Multiple fiscal calendars are easily handled by addition of an alternate hierarchy for each calendar. There is no leaf data redundancy.	Different solutions are possible, e.g. adding alternate rollups (fiscal and calendar) to a month dimension; however none of these solutions are as straightforward as with the continuous approach.
Cross-Year aggregations	Easy to accomplish by using an alternate hierarchy, e.g. add all months of the rolling 12 months forecast.	Difficult to implement.
Ad-hoc year-over-year analysis	Hard to perform ‘ad-hoc’ analysis of year over year variance in a cube-view interface such as Analysis studio or TM1 cube viewer.	Some reports may require Month and Year elements positioned on different axes (columns and rows) simultaneously. Easy to accomplish as years and months are located in two different dimensions.

Conclusion

The importance of particular application requirements must be weighed against each other in order to determine the best design approach:

Overall, I favor continuous approach for planning and forecasting applications. In my opinion, it better supports rolling forecast, multiple fiscal calendars and relative time calculations. It makes rules creation and report building significantly easier.
Discrete approach will be more advantageous for reporting applications with heavy ad-hoc year-over-year analysis requirements.