How to handle relative time (relational model focused) - Update 02

Check list of all posts

Context

Besides all advanced options listed in Ex post 1 and 2, this document is intended to provide a 4 simple and easy options, assuming that current date is easy to be determined.

• Option 1: Union all queries from different periods together.   It can achieve good performance, but difficult to maintain
• Option 2: Create each relative measure as a separated column.  This approach doesn't have a very good performance, but easy to maintain
• Option 3: Combine all distinct periods without overlapping together. This approach is improved option 1 by reducing number of queries
• Option 4: Create date offset table for distinct periods. It is a generic solution, easy to maintain, but could have a big performance issue, if all detail data needed to be retrieved into Cognos server to handle.

Screenshot below is the sample requirement for all options

Assume that

Current Month: from 2012-08-01 to 2012-08-31

Previous Month: from 2012-07-01 to 2012-07-31

HTD: from 2012-07-01 to 2012-08-31

YTD: from 2012-01-01 to 2012-08-31

Please download report specification (version 10.2) for review if needed

Option 1: Union all queries from different periods together

Query

Revenue Current Month

• Region: [Sales (query)].[Retailers].[Region]
• Period: 'Current Month'
• Revenue: [Sales (query)].[Sales].[Revenue]
• Filter: [Sales (query)].[Time].[Date]  between '2012-08-01' and '2012-08-31'

Revenue Previous Month

• Region: [Sales (query)].[Retailers].[Region]
• Period: 'Current Month'
• Revenue: [Sales (query)].[Sales].[Revenue]
• Filter: [Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-07-31'

Revenue Previous Month

• Region: [Sales (query)].[Retailers].[Region]
• Period: 'Current Month'
• Revenue: [Sales (query)].[Sales].[Revenue]
• Filter: [Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-07-31'

Revenue HTD

• Region: [Sales (query)].[Retailers].[Region]
• Period: 'Current Month'
• Revenue: [Sales (query)].[Sales].[Revenue]
• Filter: [Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-08-31'

Revenue YTD

• Region: [Sales (query)].[Retailers].[Region]
• Period: 'Current Month'
• Revenue: [Sales (query)].[Sales].[Revenue]
• Filter: [Sales (query)].[Time].[Date]  between '2012-01-01' and '2012-08-31'

Option1

• Region:  [Union1].[Region]
• SortKey: case when [Period] ='Current Month' then 1
• when [Period] ='Previous Month' then 2
• when [Period] ='HTD' then 3
• when [Period] ='YTD' then 4
• end
• Period: [Union1].[Period]
• Revenue: [Union1].[Revenue]

Layout

Note

This option can archive good performance, but difficult to maintain. In real world, there are many filters and query items. If there are any changes, we need to carefully change all queries. At the same time, make sure all queries use the same sequence and data type.

Option 2: Create each relative measure as a separated column

Query

• Region: [Sales (query)].[Retailers].[Region]
• Current Month: if ([Sales (query)].[Time].[Date]  between '2012-08-01' and '2012-08-31') then ([Sales (query)].[Sales].[Revenue]) else (0)
• Previious Month: if ([Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-07-31') then ([Sales (query)].[Sales].[Revenue]) else (0)
• HTD: if ([Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-08-31') then ([Sales (query)].[Sales].[Revenue]) else (0)
• YTD: if ([Sales (query)].[Time].[Date]  between '2012-01-01' and '2012-08-31') then ([Sales (query)].[Sales].[Revenue]) else (0)

Layout

Note

This approach may not provide a very good performance, in addition, it is difficult to use for crosstab. However, it is easy to maintain, as there is only a single query to write.

.

Option 3: Combine all distinct periods without overlapping together

• Region:

[Sales (query)].[Retailers].[Region]

• Period:

case when [Sales (query)].[Time].[Date]  between '2012-08-01' and '2012-08-31' then 'Current Month'

when [Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-07-31' then 'Previous Month' end

• Revenue:

[Sales (query)].[Sales].[Revenue]

• Filter:

( [Sales (query)].[Time].[Date]  between '2012-08-01' and '2012-08-31' )

OR

( [Sales (query)].[Time].[Date]  between '2012-07-01' and '2012-07-31' )

Result is below

All other query and layout is same as Option1. The real difference is that this approach reduces number of queries.  Only periods that are NOT overlapped can be combined together as demonstrated above. Therefore, the best way to apply is this approach, improved option 1.

Option 4: Create date offset table for distinct periods

Query

Region: [Sales (query)].[Retailers].[Region]

Date: [Sales (query)].[Time].[Date]

Revenue: [Sales (query)].[Sales].[Revenue]

• Region: [All Revenue].[Region]
• SortKey: case when [Period] ='Current Month' then 1

when [Period] ='Previous Month' then 2

when [Period] ='HTD' then 3

when [Period] ='YTD' then 4

end

• Period: [Relative time].[RelativeTime]
• Revenue: [All Revenue].[Revenue]

Layout

Note

This approach is mainly from IBM best practice Modeling techniques for relative time , check this article for detail. While the article mainly focuses on framework manager, this document simply bring it at report level.  This approach is a generic solution, easy to maintain, but could have a big performance issue, if all detail data needed to be retrieved into Cognos server to handle. In addition, it is maybe against best practice, as SQL statement should NOT be directly used in report studio.