Lecture 3

Question 1

Data lake

Answer 1

A database that holds raw data in its narrative format. The data lake approach copies all the data that might be relevant. Whereas the data warehouse approach cleans the data, the data lake approach doesn’t clean the data. Mostly, the two exist next to each other because different employees have different needs. This approach is also called schema on read.

Question 2

ETL

Answer 2

Data warehousing process that consists of:

• Extraction
• Transformation
• Load

Question 3

Extraction

Answer 3

Extracting data from a production database.

Question 4

Transformation

Answer 4

Converting the extracted data from its source from into its target form, so that it can be placed into a data warehouse. Transformation activities: transfer, cleaning, integration and extraction.

Question 5

Load

Answer 5

Putting the data into the warehouse.

Question 6

ETL data conversion

Answer 6

• Simple data type conversion.
• Complex data type conversion.
• Currency data type conversion.
• Language data type conversion.

Question 7

ETL cleaning

Answer 7

1. Data must be validated and filtered -> garbage in = garbage out (GIGO).
2. Data in the real world is dirty.
   - incomplete: lacking attribute values, lacking certain attributes of interest, or containing only aggregate data.
   - noisy: containing errors or outliers.
   - inconsistent: containing discrepancies in code or names.
3. It is often neglected or casually handled.
4. Problems exposed when data is summarized.
5. Prevention is better, but who does the cleaning?

Question 8

ETL load

Answer 8

Integral load:
- All the records are taken and processes by comparing them with the records in the data warehouse.
- Extreme case: load deletes DW tables and replaces them with complete new versions (= full load).
- When no time stamps are present it is difficult to do an incremental load.
Delta/Incremental load:
- Only the changed records are taken and processed systematically in the DW.

Question 9

ETL tools

Answer 9

1. Generation 0: do-it-yourself (generate purpose programming yourself).
2. Generation 1: code generators.
3. Generation 2: ETL engines -> highly optimized, metadata driven.

Question 10

ETL tool vendors

Answer 10

1. Informatica PowerCenter
2. Bluesky Integration Studio
3. SAP Data Integrator
4. IBM WebSphere Data Integration Suite SAS enterprise data integration
5. Oracle Warehouse Builder
6. MS SQL Server Integration Services

Question 11

BI front-end applications

Answer 11

1. Query and reporting
2. OLAP
3. Data mining models

Question 12

Query and reporting

Answer 12

• Static reports generated with SQL or SQL-generators via the meta-layer.
• Answers typical business questions.
• When a business question changes a little, the SQL query needs to be changed.

Question 13

Methods of reporting

Answer 13

• A very popular method of reporting is reporting via a meta-layer query and reporting tool.
  + used when a company does not have SQL knowledgr.
  + creates a middle layer between the database and business.
  + can be done by Microsoft Access for example.
• Reporting via SQL

Question 14

OLAP

Answer 14

• Enables users to interactively analyze multi-dimensional data from multiple perspectives.
• Answers simple and complex business questions.

Question 15

Multi-dimensional data

Answer 15

Data can be classified into measures and dimensions. Aggregating measures up to certain dimensions creates a multi-dimensional view on the data (= data cube).

Question 16

Measures

Answer 16

Summable information concerning a business process.

Question 17

Dimensions

Answer 17

Represent different perspectives on viewing measures and dimensions are organized hierarchically. Hierarchy represents different levels of aggregation.

Question 18

Fact

Answer 18

When a cell in a data cube has a value.

Question 19

OLAP operators

Answer 19

• Roll up: aggregating measures to a higher dimension level -> rollup on time from quarters to year.
• Drill down: reverse of roll up -> drill down on time from quarters to months.
• Slice (& dice): selecting subset of cells that satisfy a certain selection condition -> slice for time = Q1. Reverse is unslice and undice.
• Pivot (or rotate): rotates data axes.

Question 20

Data mining models

Answer 20

• Objective is to discover new valuable knowledge in databases.
• Answers questions like:
  + What customers are likely to respond to my promotion.
  + Which customer is at risk of leaving.

Question 21

Modeling OLAP databases, define

Answer 21

• Measures
• Dimensions
• Dimension hierarchies

Question 22

Star schema

Answer 22

A fact table in the middle connected to a set of dimension tables. It is a simple model that limits the number of joins for a better query performance.
In a star schema the primary key is composed of the primary keys of the different tables.
There is data redundancy due to denormalization (a lot is put in one table with a lot of rows). This improves query performance and less joins are needed.

Question 23

Snowflake schema (normalized)

Answer 23

A refinement of Star schema where some dimensional hierarchy is normalized into a set of smaller dimension tables, forming a shape similar to a snowflake. A Snowflake schema is used when the data volume is getting too large.

• Why ? Tackles disadvantages of the Star schema, because it is less restricted, smaller data volumes, and the possibility to store aggregations.
• However, you lose advantages of the Star schema. Is is not easily understood by end-users, high number of joins and no predictable/standard framework.

Question 24

Sparsity

Answer 24

Arises when not every cell in the cube is filled with data.

Question 25

Density

Answer 25

of actual rows / # of potential rows

Question 26

How to deal with changes in dimension attributes, or store type changes, etc.

Answer 26

Solutions by Kimball: 1. Overwrite old values. 2. Add 'version number' field as part of the key. 3. Add new column(s) to dimension table.

Question 27

How to deal with infrequent changes

Answer 27

Overwrite old value = restating. - Kills information - Use for correction of errors - Sometimes history is not important...

Question 28

How to deal with small individual changes

Answer 28

Add 'version number' field as part of the key. - Insert new record with new version number. - Optional: use additional fields like 'date from' - 'date to' - Time-sliced version of customer/store/... - Most popular solution - No referential integrity anymore, or refer with complete key.

Question 29

How to deal with suddenly big changes

Answer 29

Add new column(s) to dimension table. - Save old values in new columns - New values and old values in table - Analysis with old columns is still possible - Eventually, remove added column(s)