Saturday, July 20, 2013

How to model DMR relative time

1. Summary

This document demonstrated a new method to model DMR relative time. The method has archived three goals: 1) report can be generated with good performance; 2) Framework manager model is easy to build; 3) Framework manager model is easy to use for report author, 4) Model is easy to extend to new relative time and 5) Model is easily to be integrated with MDX functions.

2. Introduction
Implementing relative time as part of the report solution is a very popular requirement. There are three articles from IBM proven practice to handle this issue. Please see all reference below
However, these solutions may not be very practical, as they are difficult to use in real world. This article is intended to resolve the same problem, but with much less effort to build framework manager model. The problem is to provide a relative time dimensions like power cube, so that relative time can be easy to use by report author or analytical engine

2.1 Applicability
This document applies to DMR model with Cognos version 10.2.

3. Sample
In order to explain the concept, the following report is used.

There are three relative time frames:
  • Current month:  201307
  • YTD: 201301 - 201307
  • Rolling 13 Month: 201207-201307

Cognos version 10.2 sample databases (SQL server) and Framework manager are used.

The goal is to build a framework manager as below

4. Implementation
4.1 Database
Add an indicator in time dimension column for the sake of query engine, which will be explained later
Alter table [gosales].TIME_DIMENSION add RelativeTimeIndicator varchar(50)
update [gosales].TIME_DIMENSION set RelativeTimeIndicator ='RelativeTime'
Create a new table to host relative time as below
create table [gosales].RelativeTimeParameters (Parameter varchar(50), Value int)
insert into [gosales].RelativeTimeParameters values ('CM_BeginDateKey', 20130701)
insert into [gosales].RelativeTimeParameters values ('CM_EndDateKey', 20130720)
insert into [gosales].RelativeTimeParameters values ('YTD_BeginDateKey', 20130101)
insert into [gosales].RelativeTimeParameters values ('YTD_EndDateKey', 20130720)
insert into [gosales].RelativeTimeParameters values ('R13M_BeginDateKey', 20120701)
insert into [gosales].RelativeTimeParameters values ('R13M_EndDateKey', 20130720)
The value is actually a date key, as it will provide better performance when filtering data.

4.2 Framework manager
4.2.1 Add the indicator in both database layer and business view layer.

4.2.2 Create a query object to host Relative Time

4.2.3 Based on this query, create a dynamic parameter map
4.2.3 Create a new dimension at Dimension view level for each relative time, using YTD as sample.
Copy time dimension as a base, and then change it as follows
[Business view].[Time].[Relative Time Indicator]
The reason to assign YTD not hardcoded is to allow Cognos engine to aware of this YTD dimension.

Then define a filter for each relative dimension.

[Business view].[Time].[Day key] between #$[Relative Time Parameters]{'YTD_BeginDateKey'}#  and #$[Relative Time Parameters]{'YTD_EndDateKey'}#
[Business view].[Time].[Day key] between 20130101  and 20130720

4.2.4 Design all other relative dimension as  YTD, actually, the first level key is always defined as [Business view].[Time].[Relative Time Indicator]; while the filter will defined different as below

Current Month:
[Business view].[Time].[Day key]  between #$[Relative Time Parameters]{'CM_BeginDateKey'}#   and #$[Relative Time Parameters]{'CM_EndDateKey'}#
[Business view].[Time].[Day key]  between 20130701   and 20130720
Rolling 13 Months:
[Business view].[Time].[Day key]   between #$[Relative Time Parameters]{'R13M_BeginDateKey'}#   and #$[Relative Time Parameters]{'R13M_EndDateKey'}#
[Business view].[Time].[Day key]   between 20120701   and 20130720

4.2.5 Make relative time dimensions under a folder with shortcuts, and then publish the package

4.3 Report
4.3.1 Design report as below

Expand package
Bring all three items to Query


4.3.2 Change title and make it drillable
As the define title is relativetime ( from relative time indicator),  we need to overwrite column title to get current name for the top level. (By the way, you can make it flexible using parameter map as well)
  • if ( [Query 1].[Current Month] ='RelativeTime' ) then ('Current Month') else ([Query 1].[Current Month])
  • if ( [Query 1].[YTD] ='RelativeTime' ) then ('YTD') else ([Query 1].[YTD])
  • if ( [Query 1].[Rolling 13 Months] ='RelativeTime' ) then ('Rolling 13 Months') else ([Query 1].[Rolling 13 Months])

4.3.3 Check generated query or runtime query from SQL profile; you can see this approach can achieve the best performance.

Date key is used to filter, and group by is used to bring minimal data back to Cognos server. This SQL will perform very well.

5. Note
To summarize,
  • Report can be generated with good performance, as Cognos engine generated efficient SQL
  • Framework manager model is easy to build. We don’t need to create new dimension and fact table in both database and business layer. The change is happing at dimension layer with original time dimension as start point.
  • Framework manager model is easy to use for report author. Obviously, instead of using MDX function, report author, or analytical engine, such as Analysis studio can use easily to use this model.
  • Model is easy to extend to new relative time. When adding new relative times, we need only to add new records into relative time table, and a new dimension in dimension layer.
  • Model is easily to be integrated with MDX functions. We can still use traditional MDX functions to get complicated report done without touching database and Framework manager model at all.


  1. This is the information that I was looking for and let me tell you one thing that is it is very useful for who is looking
    for cognos Online Training.

  2. V. Good information. I tried this but there is no data appearing on my columns for relative times. I tested it on FM (Test dim) there is data. Does it work only on 10.2 ?

    1. I ran into a similar issue, it had to do with adding more than one member to a slicer of context filter. This issue was specific to CQM (Compatible Query Mode) I tested in DQM (Dynamic Query Mode) and did not face this issue. I would suggested for you to test in DQM.

  3. Man, You are brilliant. Very precise and useful

  4. With inventory measures, in the DMR, you can select the type of rollup (first, last,...). Will this solution still work using inventory rollup, if so, what do you expect performance wise?