There are a few specific points in the context of Cognos reporting:
- Audit is needed before promoted into report database. This idea will prevent report users from getting wrong report. The data issue should be resolved before reporting.
- Normally, summary table may be requested to enhance report performance. Summary table is the most effective way to enhance report performance.
- Metric source table is needed in case when scorecard requested.
When multiple sources are needed, you need to make conformed dimension and fact tables. In term of dimension tables, it is requested to have map table to “Stitch” to conformed dimension, so that confirmed dimensions can be used for fact table population. With respect to fact table, you need to “consolidate” all records from different sources. To turn this concept into SSIS ETL, it looks like follows