Cognos report performance tuning - Model relative time as measures for relational

Check list of all posts and the book 

This concept is not new, which is proposed from IBM Cognos best practice, please download document from http://www.ibm.com/developerworks/data/library/cognos/page90.html . Some major improvements are made, described at the end of this document. In addition, this solution works  with DMR with compatible model. However, there is better solution for DMR.

Advantages:

1. Yield much better performance and therefore scalable by turning table full screen to join with stitched query; and
2. It will make report development much easier.

Disadvantages:

1. When there is new requirement, you need to change framework manager to add new STAR scheme, which make Framework very complicated

A similar sample is provided below.   

The process to build these measures:

Step 1: Create multiple query object s based on the same fact table AG_FACT_OPS_WeeklyInventory, and named with different name as PriorWeek, 4WeekAgo and BOY (the week 53 of last year).  In addition, create a new date offset table to setup relative date

Step 2: Create multiple Model object s based on the all fact tables from database layer

Step 3: Build relationship to all dimensions for Model object s except data dimension. The real key is to Join fact table with CTRL_DateOffset table for two cases:

Case 1: simple join for point to time data

Case 2: join with expression using between for aggregate data, such as 4 weeks summary

Step 4: Build the relationship between date dimension and CTRL_DateOffset

Step 5: defined a combine inventory Fact on business layer as below

Two items below can be used to improve this solution, which will dramatically reduce modeling effort.

1. Eliminate CTRL_DateOffset table, instead, accommodated them into Dim Time. The advantage is that we will have a clear STAR scheme, not snowflake.

2. Further improve the design in model by applying role playing dimension concept

1. Create a single fact table AG_FACT_OPS_WeeklyInventory  in database layer
2. Create model subject AG_FACT_OPS_WeeklyInventory  based on fact table AG_FACT_OPS_WeeklyInventory  from database layer
3. Use ALIAS SHORTCUT to build different model subjects named with different name as PriorWeek, 4WeekAgo and BOY (the week 53 of last year).
4. Create relationship for all these fact model subjects
5. Combine all these subjects as a consolidated one