How to make Cognos report work with historical data snapshot

Check list of all posts

1 Context
There are many business cases where you need to run report based on historical data and produce the same report generated before. If the data amount is very big, then this requirement is very challenge. There are difficult ways to archive historical data, such as save the whole database, save the whole table. However, these solutions may not be very efficient in term of capacity and maintenance. This document is intended to share one solution based on assumptions below:
1) Only portion of data are changed
2) Cognos framework manager is used

2 Overall solutionThe diagram below illustrates the architecture about how the data flow to report based on the selection.

3 Host historical data in separated table with snapshot column
It is proposed to host two tables below:

Current FactTable
Static Fields			Dynamic Fields
SF1	…	SFn	DF1	…	DFn
XX	XX	XX	XX	XX	XX
XX	XX	XX	XX	XX	XX

Historical  FactTable
Snapshot 1				…	Snapshot n
Snapshot	DF1	…	DFn	…	Snapshot	DF1	…	DFn
Snapshot 1	XX	XX	XX	…	Snapshot n	XX	XX	XX
Snapshot 1	XX	XX	XX	…	Snapshot n	XX	XX	XX

Please note that Current FactTable holds all current data with all columns containing two sets of data:
Static data – data is not changed
Dynamic data – data is changed month by month
Historical  FactTable  holds all historical snapshots, for example, each month has one snapshot. The snapshot only saves columns that data are changed.

4 Make report generation based on snapshot
A new parameter is introduced to allow report users to choose snapshot. This parameter is built into historical FactTable with filter Snapshot = #Prompt()# with default value. If it is default, then there won’t be any data for report to use. But if users choose a snapshot, then these dynamic columns will be used for report.

5 Introduce a generic reportable Fact Table
Reportable fact table is dynamic generated based on snapshot. If there is snapshot specified, then these columns will be replaced from historical data based on selected snapshot

Reportable FactTable
Static Fields			Dynamic Fields
SF1	…	SFn	DF1	…	DFn
XX	XX	XX	XX	XX	XX
XX	XX	XX	XX	XX	XX

Basically, if new parameter is introduced to allow report users to choose snapshot. This parameter is built into historical FactTable with filter Snapshot = #Prompt()# with default value. If it is default, then there won’t be any data for report to use. But if users choose a snapshot, then these dynamic columns will be used for report.

6 Make left join relationship to accomplish replace current data with a historical snapshot
The generic reportable Fact Table is the join result from both current Fact table and historical Fact table. There are two cases:
Case 1: when no snapshot selected, then the left join simply get no data from historical snapshot. The dynamic columns in reportable fact table is actually from current fact table.
Case 2: when a snapshot is selected, then the left join get data from historical snapshot. The dynamic columns in reportable fact table will take data from snapshot over the original data from current fact table.
All the logic can be built in Cognos framework manager for business tier. The advantage is that all existing reports will not be needed to change at all. we can simply change column definition to achieve this goal, such as NVL ([Historical FactTable].[DFi], ([Currental FactTable].[DFi],))