Time Considerations

Cognos TM1 Time Considerations

The importance of time Cognos TM1 applications are most often used to view, compare, and predict information over time. For the most part, the information in TM1 will be:

Trended over time
Compared between different time periods
Forecasted for future time periods

When considering the concept of time in a TM1 cube you need to determine:
The lowest level of time detail required

If the data in your cube requires regular and consistent time cycles
If the data in your cube requires any unusual, overlapping, or inconsistent time cycles
If your cube requires any rolling time periods

Additionally, it is important to consider:

Will your cube be sharing data with other cubes already in your system?
What will be the level of effort that may be required for the end user when accessing data?
What will be the level of effort required to load data from external systems?
What level of data is available from external systems now and in the future?
What will be required for the application or system administrator to support a system based upon the way in which time is determined and structured within the application?

You must consider the following points:

Flexibility when comparing data across multiple time periods
Flexibility when creating new consolidated time periods in the future
Maintenance effort required to update a time dimension(s) as new cycles are added
Your rules—since the left-hand side of a rule must be a fixed reference, you really don’t want to have to maintain them every year because of your time dimension design

Granularity—the lowest level of time detail required

Granularity is the extent to which a system is broken down into small parts, either the system itself or its description or observation. The most common granularities for a time dimension are:

Month
Week
Day

Month—two-time dimensions

Cognos TM1 cubes that store data at the monthly level of detail may have one or two time dimensions.
Some of the advantages of separating the year and month into two dimensions include:

It is easier for year on year comparisons.
It will require less maintenance for the system administrator requiring that only a single element be added to the year dimension once a year.
It is easier to take advantage of TM1’s relative data spreading

Disadvantages might include:

It makes it difficult to view continuous time spans, for example, the previous 12 months.
It limits the ability to easily keep year-month combinations.

You would set up the month dimension with elements running from July through June (as July, August, September, October, November, December, January, February, March, April, May, and June):

The month dimension may also include consolidated elements for:

Year total
Quarter totals
Half-year totals

And optionally:

Quarter to Date totals
Year to Date totals

For non-financial cubes, the year and month dimensions may follow the structure as shown:-

For financial cubes, it may be more appropriate that the year and month dimensions use a calendar year (with a set of months running from January to December):

Month—one time dimension

In some situations, using a single time dimension may be more useful. In fact, if forecasting is the purpose of the application, you will almost always see a single time dimension:

Single time dimensions usually:

Support requirements like rolling forecasts.
Make rule writing somewhat easier.
May be easier to maintain by a system administrator (remember, Cognos TM1 does not have any built-in process to maintain dimensions (such as time). So, you will most likely need to provide some method to maintain this dimension.

Weeks

For cubes which must store weekly data design, the options you need to consider are as follows:
Will the model follow a fiscal year or calendar year?
A 365 or 366 day year does not contain an exact number of weeks!
A 29, 30, or 31 days month does not contain an exact number of weeks!
Using a 52 week system will eventually result in the days that are assigned to each year becoming more and more out of synchronization with the calendar.
Weeks just do not fit neatly into a month!
Leap years!
Time pattern changes (if required) will affect the dimension design.

Days

For cube designs that store data at the daily level of detail, there are a number of different options such as using a three dimensional approach with year, week, and weekday.

Date format flexibility

One of the most commonly requested requirements for Cognos TM1 dates is the ability to select and display the date in a variety of formats. The planning sample model which is part of the Cognos TM1 installation offers a simple example of the use of element attributes to manipulate the value of dates. The plan_time dimension has the following attributes defined as type A (alias):

Time_ChineseSimplified
Time_Spanish
Time_Italian
Time_Japanese
Time_French
Time_German
Time_English

Time dimension aggregation examples

Week: Weeks may be consolidated simply by week number within the month. That is, Sep-WK1, Sep-WK2, Sep-WK3, and so on, which will rollup to Sep.
Month: Of course, months may rollup to Quarter, Half-year, or directly to Year, depending upon your requirements. In addition, months may also rollup to fiscal years, QTD (quarter to date), and QTG (quarter to go) consolidations.
Quarter: Quarters may rollup to Half-year or directly to Year—again, depending upon your requirements.
Year: Most likely, Year will be a top-level consolidation. However, Years may also be grouped into alternative rollups depending upon your reporting needs. For example, all Years prior to an acquisition or other fundamental business event may rollup to Historic.
Fiscal Year: Again, most likely, Fiscal Years will be a top-level consolidation. However, like YEARS, Fiscal Years may also be grouped into alternative rollups depending upon your reporting needs.
QTD: QTD or Quarter to Date is a common aggregation or rollup that is used to include all periods from the start of a quarter to the current date. For example, this can be used to determine how a Quarter is shaping up.
QTG: QTG or Quarter to Go is an aggregation or rollup that is used to include all periods from the current date to the end of the quarter.

Time dimension maintenance

Time dimensions, like any other dimension in IBM Cognos TM1, must first be initialized (created) and then maintained to keep the current dimension with the model’s requirements.

Initialization

In some cases, the initialization of the model will include multiple forward years so that the time dimension maintenance will almost be non-existent for the foreseeable future. This means that the time dimension will include multiple dates past the current and next calendar and/or fiscal years. This is referred to as future proofing the time dimension.

Maintenance—manual

Depending upon the size of the application and/or number of users (and other factors), it may be completely acceptable for a TM1 Administrator to manually modify this dimension using the TM1 Dimension Editor.

Maintenance—automated

In other situations, a TI process may accept appropriate parameters and then update the time dimension.

Maintenance—in general

In any case, all applications deserve a documented and tested approach to maintain the model’s time dimension.

Date and time Rules functions

The following Cognos TM1 functions deal with dates and times and therefore, I believe that they are important enough to warrant a quick review:

DATE

The DATE function returns a date string like YY-MM-DD for a given serial number. Good to know because the function NOW will return us a serial number!

Foo=DATE(NOW, 1);

# — will return a string of ‘YYYY-MM-DD”

DATES

DATES is a little more practical (in my opinion). It takes a numeric value for a given year, month, and day and returns a string in a similar format:

Foo=DATES(2012,12,2);

# — will return a string of “YYYY-MM-DD”

DAY

DAY returns a numeric value for the day in a given date string:

Foo=DAY('02-05-25');

# — will return 25

DAYNO

DAYNO is reverse of DAY, the DAYNO function returns the serial date number corresponding to a given date string:

Foo=DAYNO('98-03-09');

# — will return 13947.

MONTH

To easily pull the month from a date string, the MONTH function returns a numeric value for the month in a given date string:

Foo=MONTH('98-03-09');

# — will return 3

NOW

This function can be used to return the current date and time from the system clock. Keep in mind though, that it returns the date/time in a serial number format.
NOW;

TIME

TIME is a “sister” to NOW, the TIME function returns a string, in HH:MM format, representing the system time on the TM1 Server:
TIME;

TIMST

The TIMST function takes a date/time serial number and returns a formatted date/ time string based upon the format parameter’s value which you provide. You can also pass an optional parameter called extended years:

FOO=TIMST(NOW, 'M D, Y');

# — will return “DEC 27, 2011”

TIMVL

The TIMVL function is a lot like the TIMST function. It accepts a date/time serial number and two parameters, but returns the numeric value of a component (year, month, and so on) of a date/time value.

Foo=TIMVL(NOW, 'Y');

# — will return “2011”

TODAY

This function has an optional parameter to define the resulting format. No parameter given will return a two digit year within the string (YY-MM-DD) and a parameter of 1 will return a four digit year within the string (YYYY-MM-DD):