How to make data warehouse ETL more robust for Cognos reporting using SSIS

Check list of all posts

Based on many ETL developments for Cognos reporting, the following approach is proven as a very robust and practical, regardless of whether it is single data stream, or multiple data streams.


There are a few specific points in the context of Cognos reporting:

1. 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.
2. Normally, summary table may be requested to enhance report performance. Summary table is the most effective way to enhance report performance.
3. 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