How to handle DMR relative time

Check list of all posts

Overview

Even DMR and cube both belongs multi dimensional reporting, DMR based report does present some challenges for relative time. By default, DMR framework manager doesn’t provide relative time functions, which is built in functions in power cube. To explore the similar relative time functionalities in cube, this document is intended to provide three solutions to handle relative time, listed below:

Option 1 handles it at database level: create a column for each relative time, and then create a hierarchy within time dimension for each relative time. This approach is not flexible, as you need to add column into database, but it is very easy to use for report authors.

Option 2 handles it at framework manager level: Instead of creating column for time dimension, a relative time table is created. When there is any change for relative time, you need to only add records. At the same time, create all relative time dimensions at dimension layer. This approach is relatively flexible, and it is easy to use for report authors as well.

Option 3 handles it at report level: Without applying any changes in database and framework manager, you can dynamically figure out current date, and then apply MDX time functions to determine all periods as requested on the fly. It is most flexible solution, however, it request report authors to have good understanding of MDX functions.

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

The solution will be demonstrated by using Cognos version 10.2 sample databases (SQL server) and Framework manager.

Option 1 handles DMR relative time at database level

Database

Add additional relative time flag columns in Time dimension, and update it on daily basis. In this case, create additional three columns as

Alter table [gosales].TIME_DIMENSION add CurrentM varchar(10)

Alter table [gosales].TIME_DIMENSION add YTD varchar(10)

Alter table [gosales].TIME_DIMENSION add Rolling13M varchar(10)

With values

update [gosales].TIME_DIMENSION set CurrentM ='NA'

update [gosales].TIME_DIMENSION set YTD ='NA'

update [gosales].TIME_DIMENSION set Rolling13M ='NA'

update [gosales].TIME_DIMENSION set CurrentM ='CurrentM' where  MONTH_KEY = 201307

update [gosales].TIME_DIMENSION set YTD ='YTD' where  MONTH_KEY between 201301 and 201307

update [gosales].TIME_DIMENSION set Rolling13M ='Rolling13M' where  MONTH_KEY between 201207 and 201307

Framework manager

Create each time hierarchy for each individual relative time. add these three columns to TIME_DIMENSION in Database layer and Business Layer.

Database view

Business view

Dimension view

Detailed as

All three relative time is mapped as below

[Business view].[Time].[Current Month]

[Business view].[Time].[ YTD]

[Business view].[Time].[Rolling 13 Month]

Report

When published, the package should be displayed as follows

Then bring all three highlighted members into report query or report

Option 2 handles DMR relative time at framework manager level

The main disadvantage of option 1 is to change database when adding a new relative time. To resolve this issue, this option is to provide a solution without change time dimension. The idea is to create a new relative time table. When changing relative time, you only need to add record(s).

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 Indicator varchar(10)

update [gosales].TIME_DIMENSION set Indicator ='Time'

Create a new table to host relative time as below

create table [gosales].RelativeTime (Parameter varchar(10), Value int)

insert into [gosales].RelativeTime values ('CM', 201307)

insert into [gosales].RelativeTime values ('YTD', 201301)

insert into [gosales].RelativeTime values ('R13M', 201207)

The value is actually a month key. Please note that you can define more complicated table, such as begin date, and end date.

Framework manager

Add an indicator in both database layer and business view layer.

Then make time filter dynamic based on relative time table.

Create a query object to host Relative Time

Based on this query, you can create a dynamic parameter map

Now create each relative time as a separated dimension,

You can copy date dimension and then rename and adjust it. The key point is to define a filter for each relative dimension.

For YTD

[Business view].[Time].[Month key]  between #$[Relative Time]{'YTD'}# and #$[Relative Time]{'CM'}#

[Business view].[Time].[Month key]  between 201301 and 201307

For Rolling 13 Months

[Business view].[Time].[Month key]  between #$[Relative Time]{'R13M'}# and #$[Relative Time]{'CM'}#  

[Business view].[Time].[Month key]  between 201207 and 201307

Another key pint is to define top level member, somehow you need to add a reference of date dimension, so that the query engine know to apply the filter of the used dimension.

The same way is applied for YTD and Rolling 13 Months

[Business view].[Time].[Indicator]   + ' ' +'YTD'

[Business view].[Time].[Indicator]   + ' ' +'Rolling 13 Months'

Report

When published, package looks as fellows

Query

Layout

Option 3 handles DMR relative time at Report level

The options above provide report user a clear framework manager model, however you need to change database or framework manager. This is a way to develop relative time based report without changing database and framework manager. Please see query below

Current date

item(tail(filter(members([Sales (analysis)].[Time].[Time].[Day]),

tuple([Sales (analysis)].[Sales].[Revenue], currentMember([Sales (analysis)].[Time].[Time]))  is not null), 1), 0)

Get the set of members from the Time dimension at the Date level.

members([Sales (analysis)].[Time].[Time].[Day])

Filter the set of Date to find the members that have data for a measure you know only goes up to the current period, in this case Revenue.

filter(members([Sales (analysis)].[Time].[Time].[Day]),

tuple([Sales (analysis)].[Sales].[Revenue], currentMember([Sales (analysis)].[Time].[Time]))  is not null)

Get the last date that has data. The tail function returns a member set. In this case only one member is returned since the integer argument is 1.

tail(filter(members([Sales (analysis)].[Time].[Time].[Day]),

tuple([Sales (analysis)].[Sales].[Revenue], currentMember([Sales (analysis)].[Time].[Time]))  is not null), 1)

Because the desired result is a single member, the item function is used passing 0 as its index value argument to return only the current period member.

item(tail(filter(members([Sales (analysis)].[Time].[Time].[Day]),

tuple([Sales (analysis)].[Sales].[Revenue], currentMember([Sales (analysis)].[Time].[Time]))  is not null), 1), 0)

Returning a member is important if you want to use this data item in another dimensional function.

Current Month

parent( [Current date] )

YTD

total( currentMeasure within set  periodsToDate ( [Sales (analysis)].[Time].[Time].[Year], [Current Month] )  )

Rolling 13 Month

total( currentMeasure within set  lastPeriods( 13, [Current Month] )  )

Combined approach

As seen, all three approaches have their advantages and disadvantages. We can combine all three approaches together to address the practical issue. We can define the most common used relative time in framework manager or database, then use MDX to handle complicated relative time.