Oracle Extract transform load (ETL) Interview Preparation Guide
Strengthen your Oracle ETL interview skills with our collection of 39 important questions. Each question is crafted to challenge your understanding and proficiency in Oracle ETL. Suitable for all skill levels, these questions are essential for effective preparation. Dont miss out on our free PDF download, containing all 39 questions to help you succeed in your Oracle ETL interview. Its an invaluable tool for reinforcing your knowledge and building confidence.39 Oracle ETL Questions and Answers:
1 :: 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
- 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
2 :: What are snapshots? What are materialized views & where do we use them? What is a materialized view?
Materialized view is a view in wich 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.
3 :: 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
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
4 :: 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.
5 :: Where do we use connected and un connected 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 connceted lookup.If your data is static where your data won't change when the session loads you can go for unconnected lookups
If you require dynamic cache i.e where your data will change dynamically then you can go for connceted lookup.If your data is static where your data won't change when the session loads you can go for unconnected lookups
6 :: 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
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
7 :: 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'
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'
8 :: 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
Source data extraction, Data transformation (restructuring),
Data transformation (data cleansing, value transformations),
Surrogate key assignments
9 :: 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.
10 :: 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'.