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?" 

Using Merge statement for SCD type 2

When we are in Type2

1. Insert new rows in case of new records
2. Update old rows in case of mismatch for value columns like State of residence for person , make end date as null
3. 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

1. End date the current record

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

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

Understanding Hive

Hi

I am learning hive and capturing few links here that can help me learn better

https://cwiki.apache.org/confluence/display/Hive/Tutorial

http://wiki.pentaho.com/display/BAD/Transforming+Data+within+a+Hadoop+Cluster