TOP Oracle ETL Interview Questions and Answers

Read Latest Oracle ETL Interview Questions and Answers for Freshers, 2+ Years Experienced Q and A with Explanation PDF.

What are the various tools? Name a few?

A few more
– Cognos Decision Stream
– Oracle Warehouse Builder
– Business Objects XI (Extreme Insight)
– SAP Business Warehouse
– SAS Enterprise ETL Server

Along with the above, need to include the below tools
– Informatica
– Abintio
– DataStage

What are snapshots? What are materialized views & where do we use them? What is a materialized view?

Materialized view is a view in which data is also stored in some temp table.i.e if we will go with the View concept in DB in that we only store query and once we call View it extract data from DB.But In materialized View data is stored in some temp tables.

What is fact less fact table? where you have used it in your project?

Fact less table means only the key available in the Fact there is no measures available.

factless fact table means that contains only foreign keys with out any measures example:attendance report of employees in a particular company contains no measures only

Can we look-up a table from source qualifier transformation. ie. unconnected lookup?

You cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.

Where do we use connected and unconnected lookups?

If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.

If you require dynamic cache i.e where your data will change dynamically then you can go for connected lookup.If your data is static where your data won’t change when the session loads you can go for unconnected lookups

Where do we use semi and non additive facts?

Additve: A masure can participate arithmatic calulatons using all or any demensions.
Ex: Sales profit

Semi additive: A masure can participate arithmatic calulatons using some demensions.
Ex: Sales amount

Non Additve:A masure can’t participate arithmatic calulatons using demensions.
Ex: temparature

What are non-additive facts in detail?

A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.

Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.

A non additive fact, for eg measure height(s) for ‘citizens by geographical location’ , when we rollup ‘city’ data to ‘state’ level data we should not add heights of the citizens rather we may want to use it to derive ‘count’

What is a staging area? Do we need it? What is the purpose of a staging area?

Data staging is actually a collection of processes used to prepare source system data for loading a data warehouse. Staging includes the following steps:

Source data extraction, Data transformation (restructuring), Data transformation (data cleansing, value transformations), Surrogate key assignments

What is a three tier data warehouse?

A data warehouse can be thought of as a three-tier system in which a middle system provides usable data in a secure way to end users. On either side of this middle system are the end users and the back-end data stores.

What are the various methods of getting incremental records or delta records from the source systems?

One foolproof method is to maintain a field called ‘Last Extraction Date’ and then impose a condition in the code saying ‘current_extraction_date > last_extraction_date’.

What are the various tools? – Name a few?

A few are ….
– Abinitio
– DataStage
– Informatica
– Cognos Decision Stream
– Oracle Warehouse Builder
– Business Objects XI (Extreme Insight)
– SAP Business Warehouse
– SAS Enterprise ETL Server

What is latest version of Power Center / Power Mart?

The Latest Version is 7.2

What is the difference between Power Center & Power Mart?

PowerCenter – ability to organize repositiries into a data mart domain and share metadata across repositiries.

PowerMart – only local repositiry can be created.

What are the various transformation available?

Aggregator Transformation
Expression Transformation
Filter Transformation
Joiner Transformation
Lookup Transformation
Normalizer Transformation
Rank Transformation
Router Transformation
Sequence Generator Transformation
Stored Procedure Transformation
Sorter Transformation
Update Strategy Transformation
XML Source Qualifier Transformation
Advanced External Procedure Transformation
External Transformation


Popular posts from this blog

TOP Agile Testing Interview Questions and Answers

Latest Agile Testing Interview Questions and Answers

Most Asked ADO.NET Interview Questions and Answers