ETL Process

Question 1

-takes the raw data from operational systems and prepares it for the data warehouse

Answer 1

extract, transformation,and load (ETL)

Question 2

-need handle different extract schedules
-need to join data from multiple sources for a single dimension.
-need to determine correct to use when multiple versions of a field are avaiable from different sources.

Answer 2

ETL Requirements

Question 3

-reading and understanding source data and copying needed data into data staging for further processing.
-implemented using gateways, standard interfaces, third-party products or custom development

Answer 3

extraction

Question 4

(logical extraction approaches)
-entire data source extrated-no need to track changes to data.

Answer 4

full

Question 5

(logical extraction approaches)
-only data that has changed during specified time interval is extracted
-more efficient, but requires identifying chages to data in operational source systems.

Answer 5

incremental

Question 6

(extraction in databases)
-captures changes made to tables either synchronously (using triggers) or asynchronously (usin redo log files).

Answer 6

change data capture

Question 7

(extraction in databases)
-can be used if operational system had these indicatimg date/time a row was last modified.

Answer 7

timestamps

Question 8

(extraction in databases)
-can be used if source system use range partitioning with a date key

Answer 8

partitioning

Question 9

(extraction in databases)
-typicallt used in conjuction with timestamp columns - triiger updates timestamp column upon each change on the source table

Answer 9

trigger

Question 10

online
-can be done via a reference to anohter dataase or schema.
offiline
-extraction process access data in structures outside source system.
–flat files (via SQL Plus, OCI or Pro C programs)
–SSIS packages
–export data utility (show example)
–backup files (via SQL Server Backup Utility)

Answer 10

Extraction in SQL Server

Question 11

online
-extraction process connects directly to source system to extract data.
-can be done via a databse link or direct schema reference.
offiline
-extraction process access data in structures outside source system.
–flat files (via SQL Plus, OCI or Pro C programs)
–SSIS packages
–export data utility (show example)
–backup files (via SQL Server Backup Utility)

Answer 11

extraction in Oracle

Question 12

(extraction)
-applications such a informatca and datastage can connect directly to Oracle using Native Oracle drivers.
-queries are designed in a GUI interface, then run by a server engine when

Answer 12

third-party tools

Question 13

(common types of business rules -transformation)
-only keep records with certain data values or ranges

Answer 13

filtering

Question 14

(common types of business rules -transformation)
-totaling recording into a summary record

Answer 14

summarizing

Question 15

(common types of business rules -transformation)
-combining two or more source system record into one output or target record.

Answer 15

merging

Question 16

(common types of business rules -transformation)
-converting codes to English description or data warehouse standard descriptions

Answer 16

transposing

Question 17

(common types of business rules -transformation)
-converting codes from differen sources to data warehouse standard values

Answer 17

codifying

Question 18

(common types of business rules -transformation)
-applying mathemtical formulas to rpoduce data to be stored in the data warehouse

Answer 18

derivations

Question 19

(common types of business rules -transformation)
-ensuring the quality of the data-that it is consistent, is of a known, recognized value, and conforms to the metadata definition for it.

Answer 19

cleansing

Question 20

-can be just about any type of data conversion
-during load are most efficient.
-can be done when fact data is inserted or as an update after the fact record has been loaded.
-don’t ignore or limit transformations because they are difficult!
-the difficult it is, the more important it is for the data warehouse designer to calculate if if possible.

Answer 20

transformation

Question 21

(transformation applications)
-each transformation implemented as a separate SQL operation, creating a temporary staging table.

Answer 21

multistage

Question 22

(transformation applications)
-“transform-whole-load”-transformations performed during loading process, eliminating need to create temporary staging tables.

Answer 22

pipelined

Question 23

process of loading transformed records into the warehouse.
-additional preprocessing may be done during load.
–sort
–aggregate
–compute views
–build indexes
–partition
-huge volumes of data to be loaded, yet small time window (usually at night) when the warehouse can be taken off-line.
-typically handled by batch load utlities

Answer 23

load

Question 24

(loading in oracle)
-SQL Loader
-external tables
-OCI and Direct Path APIs
-export/import

Answer 24

loading mechanisms

Question 25

(loading in oracle)
-loads data from flat files into Oracle
-can perform basic data transformations

Answer 25

SQL Loader