If you’re reading this odds are you’ve been googling information on relative time dimensions in TM1. You've come across YouTube videos, TM1 blogs, forums, IBM developerWorks pages, and maybe even one TM1 enthusiast named Wim Gielis who has dedicated an entire website to the intricacies of TM1. Yet a comprehensive overview of all the possibilities is still missing.
For the quick cut to one of the most scalable ways to build relative time dimensions, skip the next few paragraphs down to Figure 1.7
Odds are you’re a Cognos user and know one of the main areas IBM is a leader and visionary (Figure 1.1) is within performance management and advanced analytic space, especially TM1. The introduction of Performance modeler was an action taken by IBM’s development team to empower end users by making it easier to do more with what you already know. The trend is to make Business Intelligence (BI) more accessible to users. It seems no matter where you look, whether it is BI solutions or content management platforms like Squarespace, drag and drop platforms that empower the end user are everywhere.
There are those old school TM1’ers who will always be loyal to using server explorer via Architect or Perspectives and rightfully so. Those developers are artists, they are able to construct a custom in-memory application that will automate and streamline as many business cases as you can throw at them. They can tell you about the days when you needed an excel macro to create and format your time dimension. Which is great, but unless you have the spare time to learn how to do all of those functions, it may not be the best option. (Figure 1.2)
But we aren’t here to discuss the past: Let’s jump to performance modeler where the time dimension has been simplified. The customization and enterprise calendar attributes that may need to be built into an application still exist in Architect, which can turn into a bit of a process. However, in performance modeler all you need to do is right-click to create a new time dimension, select time as dimension type, double click the new time dimension to open it, click the dimension icon to begin the population, and you’re off. (Figure 1.3)
In a matter of seconds, like magic, the time dimension has been populated. The nice thing here is the time roll-ups are not static. If a new business need arises while looking at a range, all you need to do is right click the dimension anytime and scroll to time rollups. (Figure 1.4)
From there performance modeler allows you to reformat the time values in a multitude of ways. As you can see these formats range anywhere from current month, last month, to comparing year over year variance from the same day, same month, same quarter, or same year as before. If by chance these preset options do not fit your needs you can always create a custom date range. (Figure 1.5)
If you build an application that fits the requirements, is on-time, under budget, and yet the end users still do not use it, it’s a loss. Name of the game here is end user adoption. Keep your BI developers and analytic enthusiasts in mind. Make it a breeze from them to plug into the TM1 servers and build the all reports that they need to their heart’s desire. Tiny additions such as the attributes of the metadata, which performance modeler does with ease, will streamline the construction of dynamic reports. (Figure 1.6)
Here we go the moment we’ve all been waiting for, relative time dimensions in TM1. The stage has been set for what the current capabilities are available in TM1 to build a simple time dimension, with light weight dynamic characteristics. Yet there are still limitations when it comes to building reporting capabilities relative time dimensions. Always build with the end in mind.
TM1 developers like to get hands on and get into the thick of it. In this example (Figure 1.7) of a multiple alternative ragged hierarchies is an opportunity to dive straight in. After talking with clients and experienced Cognos professionals alike, this structure, though not the simplest, is the most effective at delivering business value when implementing relative time dimensions. The majority of TM1 users are using the software in a budgeting, planning, or forecasting application, and a continuous time dimension (a single time dimension) is the best fit. Just as anything else in analytics, data plays a central role. (This structure was originally found on Wim Gielis’ website from the IBM TM1 9.5 master course.)
Whether it’s your ETL team, an excel macro to a Turbo Integrator (TI) process, or just a TI process, the solution is based off of the repeatability of this structure. Once the TI process is there, it’s smooth sailing with updates only once a year for a new year. How can you beat it? (Figure 1.8)
The reports and cube views will reference the same consolidations, and the children will automatically update as data changes from the TI processes. This continuous approach of a single time dimension bypasses the discreet approach of two separate dimensions for years and months. Now some may say using a discreet approach allows you to easily compare year over year variance with two separate time dimensions of month and year. Remember earlier when we discussed performance modeler has time dimension plug-ins that allows for relative variance comparisons? This structure allows creating views of Previous Period and Next Period much easier. (Figure 1.9)
If you’re going to dedicate the resources to build it, build it right the first time. There are other ways to achieve the same results for a relative time dimensions, but there will be limitations.
For more insights into IBM Cognos, contact a Business Intelligence professional at 813.265.3239 or email@example.com.