Data Warehousing

Question 1

Data warehouse:

Answer 1

A subject-oriented, integrated, non- volatile, time-variant data store in support of management’s decisions

Question 2

Data mart:

Answer 2

A specialised, subject-oriented, integrated, volatile time-variant data store in support of a specific subset of management’s decisions.

Question 3

ETL process

Answer 3

The process of extraction, transformation and loading of the data in creating a data warehouse

Question 4

Why individual data extracts are popular:

Answer 4

Data is out of the way of high performance processing
End user now owns the data

Question 5

Why is individual extraction bad?

Answer 5

We end up with a spider web of extract processing.

Question 6

Problems with the spider web?

Answer 6

• no time basis of data
• algorithmic differential
• levels of extraction
• external data
• no commons data source

Question 7

No time basis of data:

Answer 7

Data changes so when different departments extract data at different times they get different results.
Any correlation is then coincidental

Question 8

Algorithmic differential:

Answer 8

This is the difference that occurs when different departments choose different rows to process and analyse. They neglect to mention this when presenting results

Question 9

Levels of extraction:

Answer 9

Extracting from an extract magnifies the problems of time basis and algorithmic differentials

Question 10

External data:

Answer 10

Inclusion of external data in the mainstream analysis and not specifying, creates knowledge gaps among users. Because we’re each using our own storage spaces

Question 11

No common source:

Answer 11

No synchronisation or sharing of data so you can’t expect the same results

Question 12

OLTP:

Answer 12

Online Transaction Processing
Data warehouse = data from a variety of OLTPs kept on a different platform (the DW)

Question 13

Bad data:

Answer 13

Inconsistent (when there are multiple versions of the data inconsistency can occur)
Inaccurate -> misleading
Incomplete -> not useful
Untimely -> irrelevant

Question 14

Good data

Answer 14

The right data for the right person at the right time

Question 15

Impact of Decision Support Systems on OLTP systems

Answer 15

DSS accesses large volumes of data and slows down OLTP. This data can sometimes even be locked by DSS and slow OLTP more.
DSS has unpredictable requirements that make performance tuning difficult.

Question 16

Subject-oriented vs Process-oriented

Answer 16

Process: all the data you need for a process
Subject: complete and consistent data per subject in the organisation

Question 17

DW scale

Answer 17

Data warehouses are large in scale. They span the organisation. They store historical data (5-10 years old). They integrate data from independent silos in the organisation. Their data is continually in sync with data sources

Question 18

Data granularity:

Answer 18

How summarised or raw the data is. Low granularity-> summaries
High granularity -> all details
High -> more flexible and can be used for further analysis in future

Question 19

Star schema

Answer 19

• fact table at the centre (it stores transactional data) -> facts: measurable events.
• dimension table connected to the fact table -> dimension: descriptive context which we summarise by.

Question 20

Snowflake schema

Answer 20

• similar to star but dimension tables are normalized (split into smaller related tables)