In What scenario ETL coding is preferred than Database level SQL, PL/SQL coding? What is the methodology and process followed for ETL testing in Data warehouse environment? Develop a mapping to extract the employee drawing maximum salary where deptno = department drawing... If LKP on target table is taken, can we update the rows without update strategy transformation? How you capture changes in data if the source system does not have option of storing date/time field... What is the Difference between a ODS and Staging Area What is the effect of the OPTIONS statement ERRORS=1 What are push and pull ETL strategies? How do you tell aggregator stage that input data is already sorted Can anyone please explain why and where do we exactly use the lookup transformations. Thanks in advance Thanks Nayana...

Interview Candidate
Aug 7th, 2007
6
5735

Showing Answers 1 - 6 of 6 Answers

Rambabu.Gali

Nov 26th, 2007

We should go for an ETL tool when we have to extract the data from multiple source systems like flat file, oracle , COBOL etc at one instance. where PL/SQL or SQL can not fit.

2. we can update the target with out using update strategy by setting the session parameters ..if the source is a database.

3. Stop on errors=1 (if you set this option to 1 the session will be stopped after occurance of 1 error row. if it is 0 the session will not be stopped even u got n number of errors.

4. Lookups can be used for validation purpose.

SQLGal
Profile Answers by SQLGal

Jun 7th, 2009

The simple answer is, when it is cost effective. ETL tools save time but can be expensive. They are more efficient than writing code because the code is built using drag and drop. These code files are organized into mappings or packages. (Code rarely has to be written and code errors are managed and fixed in the debugger). The ETL objects can be nested, shared and reused. They can be manipulated and executed using control structures.

Use the lookup transformation for scalability. If you have a where clause that looks (for example) three cities - Los Angeles, Phoenix and Dallas. Put those values in a table and use the lookup transformation. Then, if the list of cities changes, you won't have to modify the transformation.