How to model DMR relative time

Check list of all posts

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

• IBM Cognos 8 Framework Manager - Dimensional Modeling for Time Period Analysis
• Modeling techniques for relative time
• IBM Cognos 8 Framework Manager - Modeling Relative Time as Measures

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

Layout

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.