- Understanding a Time Dimension
- Creating an Empty Time Dimension
- Populating Dates in the Time Dimension
- Import Date/Time Type Columns into a Time Dimension
- Creating Time Roll-Ups in IBM Cognos Insight
- Creating Custom Time Ranges
- Comparing Two Roll-Ups
- Profitability Analysis Use Case
- Downloadable resources
IBM Business Analytics Proven Practices
Time Dimension in IBM Cognos TM1 Performance Modeler and IBM Cognos Insight
Product(s): IBM Cognos TM1 Performance Modeler, IBM Cognos Insight
This content is part # of # in the series: IBM Business Analytics Proven Practices
This content is part of the series:IBM Business Analytics Proven Practices
Stay tuned for additional content in this series.
Purpose of Document
The purpose of this document is to explain how time dimensions work in IBM Cognos Insight and IBM Cognos TM1 Performance Modeler and to describe how a date time dimension can be created from scratch as well as imported from an existing data source. It will also demonstrate how the time roll-up feature can be used to create simple analyses.
This document applies to IBM Cognos Insight Version 10.2.1 and higher, and IBM Cognos TM1 Performance Modeler Version 10.2 and higher.
Exclusions and Exceptions
Some steps or information may not be the same or may not exist in previous or future releases. For up-to-date information on how to create, import or use time dimension related features, please refer to the user guide of the corresponding product.
An assumption is made that the reader is familiar with using IBM Cognos Insight 10.2.1 or later and/or IBM Cognos TM1 Performance Modeler 10.2 or later.
Understanding a Time Dimension
A time dimension has the following attributes for its members:
- Invariant Name: Used by the TM1 engine. This has to be unique.
- Caption: Used to display the names in Cognos Insight and TM1 Performance Modeler.
- Start Date: The first date of the member, e.g. Jan 1, 2013 for Q1 2013.
- End Date: The last date of the member, e.g. Mar 31, 2013 for Q1 2013.
- Last Period: The final period in the sequence.
- First Period: The initial period in the sequence.
- Previous Period: The previous period in the sequence.
- Next Period: The next period in the sequence.
- Index: Index of the member.
- Weight: A factor applied, usually of -1, to change a positive value to a negative value.
Start Date, End Date, Last Period, First Period, Previous Period, and Next Period attributes don’t get populated while using Cognos Insight but do get populated in TM1 Performance Modeler while populating a time dimension with dates. They can be used for writing advanced rules.
Cognos Insight and TM1 Performance Modeler represent time dimensions with a slightly different icon from a regular dimension. These time dimension icons are shown in Figure 1. For example, the icon on the left is used in Cognos Insight and displays a clock symbol whereas the icon on the right is used in TM1 Performance Modeler and displays a gear symbol. Note that icons may vary slightly from version to version but will look similar to one of the icons shown in Figure 1.
Figure 1: Time dimension icons, one displaying a clock and the other a gear symbol
Creating an Empty Time Dimension
An empty time dimension can be created using IBM Cognos TM1 Performance Modeler. The process involves two parts, creating an empty time dimension and subsequently populating dates in the time dimension. Dates can be populated using a built in generator (described next in the Populating Dates in the Time Dimension section) or can be imported from an external data source using guided import which is described in the Import Date/Time Type Columns into a Time Dimension section.
The steps to create an empty time dimension are as follows:
- Once TM1 Performance Modeler is launched and connected to a TM1
server, from the context menu in the Model Design
tab, select New > Dimension.
Figure 2: Context menu New and Dimension options highlighted
The Create new dimension dialog appears.
- For Dimension Type, select Time.
Figure 3: Create new dimension dialog with Time selected as the Dimension Type
- Click OK and a new time dimension without any data will be created.
Populating Dates in the Time Dimension
Generating dates for a time dimension is very simple.
Once the date dimension is created, double-click on the new dimension to open it up and then invoke the Populate Time Dimension action that will bring up a dialog box shown in Figure 4.
There are three steps at this point.
Step 1 – Period Level: In this step we will define the period roll up levels. In the Create Time Dimension dialog for the period level, relevant options are shown as the user activates a particular level and some useful tips are shown at the bottom of the dialog box. Select the levels you wish to include in the time dimension - in this case Years, Quarters, Months, Weeks, and Days are selected and the type of month chosen is Calendar as shown in Figure 4.
Figure 4: Create Time Dimension dialog on Period Level section with all levels selected and type of month set as calendar
Step 2 - Duration: In the Duration step you need to define the duration of the time dimension. Relevent members and levels will be created (as selected in the previous step) between values specified in the Start Date and End Date fields. The SwitchOver Date field is the cut-over between actual (historic) periods and future (forecast/budget) periods. Some time-based functions in rules use this.
Figure 5: Create Time Dimension dialog on Duration section specifying a Start Date, End Date and a SwitchOver Date
Step 3 - Member Names: This step is optional and allows you to define the naming patterns. In order to define a naming pattern, you need to select the appropriate date level and then provide the values you would like to appear for members in that level in the Prefix or Suffix fields. In Figure 6, we want to prefix member names for the Quarters level with a value of QT.
Figure 6: Create Time Dimension dialog on Member Names section with QT entered in the Prefix box for the Quarters level
Import Date/Time Type Columns into a Time Dimension
One alternative to generating times and dates is to import from an existing data source with date/time data that can be recognized. Using Guided Import functionality (TM1 Performance Modeler only) or the Import Data or Import Dimensions functionality in both IBM Cognos Insight and IBM Cognos TM1 Performance Modeler, one can import a date/time type dimension. This can be achieved either as part of importing a cube (where the dimension will be part of the cube) or as importing an individual dimension.
In order to make sure that dates are recognized, on the Data Mapping page of the import wizard, select Show Properties, and then make sure the Dimension Type property is set to Time as shown in Figure 7. This applies to both Import Data and Import Dimension scenarios.
Figure 7: Import wizard Data Mapping page showing Date dimension’s Dimension Type property set to Time
Upon finishing the import, the dates from the data source will be used to create a time dimension in the cube.
Creating Time Roll-Ups in IBM Cognos Insight
A time roll-ups feature is available in IBM Cognos Insight. This can be accessed by right-clicking on any member in a time dimension in the workspace (as shown in Figure 8) or in the Edit dimension dialog box. This feature will not be available for non-date/time type dimension. If the option is not present, please make sure that your version of Cognos Insight has the time roll-ups feature and the dimension is of the right type by checking the icon.
Figure 8: Right-click menu for a member from a time dimension showing the Time Roll-ups option highlighted
The Time Roll-ups dialog is as shown in Figure 9. The radio button options at the top let you pick the reference date. The first option lets you pick today which depends on the day when this dialog box is opened. If the dialog box is invoked on a later day and OK is clicked, then the roll-up data will get updated or refreshed for that day. This option is suitable for creating dashboards that need to be updated on later days. The other highlighted sections are the Time range selection, Start Date and End Date for each range and a Custom button, which will be discussed later.
Figure 9: Time Roll-ups dialog showing different areas
The second radio button option is for a fixed date as shown in Figure 10. This is handy when the user is working on a data set that is old or the user doesn’t want the dashboard to be updated on later days. A user can pick a particular day and all the roll-ups in the table will get updated according to the selected date.
Figure 10: Time Roll-ups dialog showing a fixed date of Nov 18, 1994 selected
Once a reference date is selected, you can select time ranges according to your need. There are some commonly used ranges already defined for you in the dialog. If a time range you are interested in cannot be found in the list, you can define your custom time range(s). If a custom time range is unselected in future, it gets removed from the dialog box. If you wish to keep the time range but not to show it in the OLAP view for any reason, you can always right-click on the range and select Hide in the right-click menu.
Creating Custom Time Ranges
As mentioned previously, if the Time Roll-ups dialog box does not have a time range you are interested in, a custom time range, as shown in figure 11, can be defined using a name, start, and end date. The name has to be unique. Using a calendar, you can pick the start and end dates as required.
Figure 11: Custom Time Range dialog showing a start and end date selected
Comparing Two Roll-Ups
One of the most common usages of time roll-ups is to compare between two different roll-ups. Using very simple steps, it is possible to create an interesting dashboard.
In the Time Roll-ups dialog (shown earlier in the Creating Time Roll-Ups in IBM Cognos Insight section), you can select Current quarter and Last quarter. That will result in new members as highlighted in the top left corner in Figure 12. Now a calculation needs to be created from the right-click context menu. Make sure both the Current quarter and Last quarter items are selected, click on Calculate and then click Compare Current quarter vs. Last quarter, also shown in Figure 12.
Figure 12: Right-click menu for multiple selected items (highlighted) showing Calculate and Compare Current quarter vs Last quarter also highlighted
Once the calculation is created, you can see a new member such as Compare Current quarter vs. Last Quarter (Figure 13) with a graphical representation of the comparison. By default, IBM Cognos Insight assumes that if the ratio is less than 90% then it shows red, more than 110% displays green and yellow is the ratio is in between.
Figure 13: An example displaying the comparison, in this case for both value and the count, current quarter is less than the last quarter
However, these default ratios can be modified very easily. You can right-click the calculation and then select Calculate > Edit this calculation. This will pop up the Calculation dialog box (Figure 14) which includes default calculation values used to calculate red and green (in this case 0.9 and 1.1) and the calculation’s resulting values used by Cognos Insight to display the appropriate color (-1 for red, 1 for green and 0 for yellow).
Figure 14: Calculation dialog showing the default calculation values
The graphical representations cannot be changed. However, you can modify the comparisons as needed. For example, if the user wants to show red for ratios below 80%, then the only thing that needs to change is 0.9 to 0.8 in the first line.
Profitability Analysis Use Case
Let us assume we have profitability and margin data where product margin is presented across all time from 2010 to 2013 as partially shown in the workspace in Figure 15.
Figure 15: An example cube containing sales information for year 2010-2013
Let us also assume that we are only partway through this year - current data is as of April 25, 2013. Therefore, we can’t just compare this year (2013) to last year (2012). What we need is year-to-date, a relative time perspective.
The time roll-ups feature in IBM Cognos Insight provides us what we need to do this comparison. We can simply invoke the Time Roll-ups dialog by right-clicking on any column header, such as 2013, and selecting the Time Roll-ups context menu item. Then select Year to date and Year to date, last year in the Time Roll-ups dialog as shown in Figure 16.
Figure 16: Time Roll-ups dialog with Year to date and Year to date, last year selected
Hiding all other time dimension members and the total, you can focus on these two relative time perspectives for a powerful analysis and comparison, as shown in Figure 17.
Figure 17: Workspace showing Year to date and Year to date, last year comparisons for Revenue, Cost, and Margin measures by Product name
Selecting both relative time members and invoking the Calculation menu to choose the Compare calculation presents graphical indicators about which products have improving, lagging, or stable margins as we progress through the current year. The indicators are represented by red, green, and yellow graphics as shown in Figure 18.
Figure 18: Compare calculation showing performance by Product for the current year versus the previous year by displaying red, green or yellow
Once created, the time roll-ups and the comparison calculation can be used with any perspective available for the context of this workspace.
By simply dragging the location dimension to the rows, you can get the comparison between Year to date and Year to date, last year for all locations. You can simply identify the locations with improving margins, the locations with lagging margins, and the locations with stable margins from last year to this year up to today's date as seen in Figure 19.
Figure 19: Compare calculation showing performance by Location for the current year versus the previous year by displaying red, green or yellow
The same analysis can be created with respect to the sales representatives with a simple drag and drop gesture by dragging the Sales Rep dimension to the Rows drop zone as seen in Figure 20. The names shown in Figure 20 are samples and any relationship to real persons, living or dead, is purely coincidental.
Figure 20: Compare calculation showing performance by Sales Rep for the current year versus the previous year by displaying red, green or yellow
A plan for the best actions can then be taken to reach year end margin targets.
Both IBM Cognos Insight and IBM Cognos TM1 Performance Modeler provide powerful tools to create and analyze time related data.