Cognossimplified: September 2015

Monday 28 September 2015

Selection of Datasource at Runtime

Each Cognos connection to a database is composed of three parts : DataSource -> Connection -> Signon.

- Each DataSource can have 1..N Connections

- Each Connection can have 1..N Signons

You should be able to accomplish your goal by having 1 DataSource with 2 Connections (one connection points at your production DB, the second connection points at your day old copy).

Then you point your single Package at the 1 DataSource. Each time you run a report associated with this Package, the user will be prompted : "Which connection would you like to use?"

Sunday 27 September 2015

Using Merge statement for SCD type 2

When we are in Type2

Insert new rows in case of new records
Update old rows in case of mismatch for value columns like State of residence for person , make end date as null
Insert new records in case of changes and make end date as 31-12-9999

If we use Merge statement in oracle we get only 2 options . Insert or Update . If a new record easy to insert . If a existing record we have 2 steps

End date the current record

Insert the new record

Since merge executes only once per record there is no way of doing insert and update for same record . So we have to create a dataset which has 2 records in case of SCD Type2 changes. Which means we will have the same record twice , with row_type_indactor . When 1 then insert and when 2 then update .

Key	Value
1	A
2	B

Key_code	State
2	Hyd

When we join on Condition Key = Key_code then we will get only one row

Key_code	State
2	Hyd

When we join on Condition Key < = Key_code then we will get only one row. This is because 2 is greater than 1 and 2 is equal to 2. So 2 rows. It looks simple but took me more than 1 hour to figure out

Key_code	State
2	Hyd
2	Hyd

Below is a good link on the subject

http://databobjr.blogspot.in/2011/04/load-slowly-changing-dimension-type-2.html

Monday 7 September 2015

ETL vs ELT

ETL -- Extract transform ( in etl tool like datastage) and Load (database like oracle)

ELT - Extract load into database and do the transformation in database works well if you have huge volumes of data Now even IBM agress to it , they have come up with Balanced optimization which pushes queries to database