How to develop light weighted ETL using Cognos Framework Manager

Check list of all posts

(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.

SQL statement can be obtained by test target table with Query information tab

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.