10 Data warehouse

Question 1

what is a data warehouse

Answer 1

1. central repository of integrated data
2. for data analytics and reporting
3. architecture
   - os
   - integration layer
   - data warehouse

Question 2

what is ETL

Answer 2

1. extract
2. transform
3. load

Question 3

data loading

Answer 3

1. ETL
2. staging layer
   - store raw data
3. integration layer
   - integrate data from staging layer
4. access layer
   - help user retrieve data

Question 4

data loading challenges

Answer 4

1. data volume
2. dirty data
3. data synchronisation

Question 5

data warehouse schemas

Answer 5

1. star

2. snowflake

Question 6

star schema pro/cons

Answer 6

pro

• denormalised
• simple
• query performance

cons

• not enforced data integrity
• not flexible
• not suitable for complex analysis

Question 7

snowflake schema pro/cons

Answer 7

pro
- storage saving

cons
- complex query

Question 8

OLAP

Answer 8

1. online analytical processing
2. availability/ speed are not big concerns
3. OLAP cube structure

Question 9

OLAP cube

Answer 9

slice
- keep same dimension constant and show changes for others

measure
- quantifiable eg (sum of people)

dimension
- non quantifiable eg (yr 2022)

cells
- number that represents measure

Question 10

OLAP cube operations

Answer 10

1. slice
2. dice
3. drill down
   - from summarised to detailed
4. roll up
   - from detailed to summarised
5. pivot
   - rotate cube