(This is an idea shared from my co-worker Ivan Li)
There are many tools to develop ETL. This document shares a new idea to develop light weighted ETL with Cognos Framework manager. This solution could be very useful for following situations:
1) You want to quickly develop a prototype to transform data.2) You don’t use traditional DW surrogate key in fact table, instead, you want to simply use natural business key to connect dimensions and facts.
3) Each table has many columns.
The major advantage of FM solution is to allow modellers to focus on business logic instead of detail coding. In addition, it will give us a good overview in term of logic. It refers to follows:
1) What source tables are needed?2) What relationship should be built between these tables, such as left join?
3) What filters are needed?
4) How many steps you want to transform the data?
5) What columns should be exist in target table?
A sample overall picture looks like follows, same as traditional FM.
You can also make query with complicated logic as query subject.
when generate SQL script in FM, make sure to fix the more-than-30-character issue, delete the FM generated column such as c122.
Create Table by simply add WHERE 1>2
Populate Data with the same SQL script
You can make SQL script with good format with different SQL format tool.