Data Warehouse Design - Chapter 1.4

Question 1

What happens in the data staging layer?

Answer 1

The ETL processes that extract, integrate and clean data from operational sources to feed the data warehouse layer.

Question 2

When does ETL happen?

Answer 2

When a data warehouse is populated for the first time. After that, it occurs every time the data warehouse is regularly updated.

Question 3

How is the ETL stage often defined in the three-layer architecture and why?

Answer 3

Reconciliation.

• As the ETL processes feed the reconciled data layer, which in turn feeds the data warehouse. This is considered the most complex stage.

Question 4

Which four stages are present in ETL?

Answer 4

1. Extraction (capture)
2. Cleansing (cleaning/scrubbing)
3. Transformation
4. Loading

*Note: Cleansing and transformation stage are often blurred together.

Question 5

What is the main difference between the cleansing and the transformation stage?

Answer 5

Cleansing focusses on rectifying data values, whereas transformation is focussed on managing data formats.

Question 6

Which three types of data extraction are possible?

Answer 6

1. Static extraction
   - Making a snapshot of operational data, done when you populate the data warehouse for the first time
2. Incremental extraction
   - Used to update data warehouses. Based on a log maintained by the operational DBMS
3. Source driven extraction
   - You rewrite your operational applications to give notifications of all the data changes

Question 7

What happens in the cleansing stage of ETL?

Answer 7

Remove inconsistencies and mistakes that make the data dirty to improve data quality.

Question 8

What are the most frequent mistakes with data that require cleansing?

Answer 8

1. Duplicate data
2. Inconsistent values that are logically associated
3. Missing data
4. Unexpected use of fields
5. Imposible or wrong values
6. Incosistent values for a single entity because different practices were used
7. Inconsistent values for one individual entity because of typing mistakes

Question 9

What are the main data cleansing features in ETL and what do they do?

Answer 9

1. Rectification (Rectify mistakes)
2. Homogenization (Make them similar)

-> Using specific dictionaries to rectify typing mistakes and to recognize synonyms.

Question 10

What is rule-based cleansing?

Answer 10

A technique to achieve rectification and homogenization. It enforces domain-specific rules and defines appropiate associations between values.

Question 11

What happens in the transformation phase?

Answer 11

Data is converted from its operational source format into a specific data warehouse format.

-> In a three-layer architecture, this results into the reconciled layer.

Question 12

Which data aspects are rectified in the transformation phase?

Answer 12

1. Loose texts that may hide valuable information
   (BigDeal LtD might hide that it is a limted partnership company)
2. Different formats can be used for individual data
   (Data saved as a string or as three integers)

Question 13

What are the three main transformation processes in the transformation phase?

Answer 13

1. Conversion and normalization that operate on both storage formats and units of measure to make data uniform.
2. Matching that associates equivalent fields in different sources
3. Selection that reduces the number of source fields and records.

Question 14

Which transformation technique is used when populating a data warehouse after the transformation phase?

Answer 14

Denormalization.

so it requires aggregation to sum up data from the data warehouse as end-user

Question 15

Which two ways of loading (into the data warehouse) exist?

Answer 15

1. Refresh
   Replacing al the older data, rewrite the database completely. Normally done at the beginning
2. Update
   Only add the changes in data. Normally done to update.