By Brady Marr
Senior Director, TM1 Practice
eCapital Advisors
For as long as I have been working in cube-based (OLAP) software, there has been a long running debate about whether to build your cube’s OLAP time dimensions with a single continuous time dimension, or rather to separate into a Month dimension and a Year dimension. To describe the continuous time dimension to my clients, I always referred to one of my favorite movies from my childhood – Back to the Future.
In general, it always seems like Report Authors preferred the single continuous time dimension and budgeting modelers preferred separate Month and Year dimensions. In addition, using a continuous time dimension allows a TM1 modeler to use the Dimension Index (DIMIX) function to easily identify the Previous Month, and Next Month even when the months cross over into the another year. To dig into the debate just a little deeper, one of the many reasons that budgeting models like separate dimensions is that it helps to have a reference to prior year data when setting the forecast for the coming year – especially if seasonality is in play. Having separate dimensions also makes it much easier to produce a popular line chart which visual shows year over year comparisons.
Almost every other type of report would be easier to produce with data modeled in a continuous time dimension. Thus, it was not uncommon for developers to build budgeting cubes that contained separate Month and Year dimensions and then also build summary cubes that contained the single continuous time dimension.
Thanks to the new release of Planning Analytics 2.0 (available on premise and in the cloud), the debate is finally over. The new release offers new functionality that allows each dimension to have separate, distinct hierarchies (that can be derived automatically based on attributes), and more importantly, for the user to be able to use those hierarchies independently of each other.
From a non-time dimension example, your Store dimension could have 1 hierarchy that roles up stores into City and States, and another hierarchy that roles up Stores into Store Type, and another that identifies the Geography Type. The user then has the ability to next all of the hierarchies in the rows, or potentially even put the State hierarchy on the Rows and the Store Type hierarchy in the columns.
The time dimension debate is resolved by using the single continuous time dimension in our cubes, and by building one hierarchy that roles up the leaf-level months into years, and one hierarchy that roles up the leaf level months (Jan-17, Feb-17, etc..) into Months (without the year reference – Jan, Feb, etc…). This will allow us the best of both worlds as we could then slice the data so that the Year hierarchy is on the Rows and the Month hierarchy is on the Columns.
The real benefits are that we potentially need fewer cubes in the model (no more summary cubes), our cubes will take up less memory because they will have fewer dimensions, and most importantly we save time by not having to engage in the endless debate of Continuous vs. Separate Time dimensions. If you have any questions, please email me at bmarr@ecapitaladvisors.com.
