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.
- Yield much better performance and therefore scalable by turning table full screen to join with stitched query; and
- It will make report development much easier.
- 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.
- Eliminate CTRL_DateOffset table, instead, accommodated them into Dim Time. The advantage is that we will have a clear STAR scheme, not snowflake.
- Further improve the design in model by applying role playing dimension concept
- Create a single fact table AG_FACT_OPS_WeeklyInventory in database layer
- Create model subject AG_FACT_OPS_WeeklyInventory based on fact table AG_FACT_OPS_WeeklyInventory from database layer
- Use ALIAS SHORTCUT to build different model subjects named with different name as PriorWeek, 4WeekAgo and BOY (the week 53 of last year).
- Create relationship for all these fact model subjects
- Combine all these subjects as a consolidated one