Data Warehousing

Question 1

Data warehouse

Answer 1

A collection of data that helps analysts to make decisions

Question 2

True or false: Data warehouses store operational databases

Answer 2

False! Data warehouses store historical data.

Question 3

Features of a data warehouse

Answer 3

Subject-oriented - provides info around a subject rather than operations
Integrated - data from many different sources
Time-variant - data is identified with a particular period (e.g. last 12 months)
Non-volatile – data is not erased when new data is added

Question 4

Information processing

Answer 4

Processing data via queries or statistical analysis

Question 5

Analytical processing

Answer 5

Processing data via Online Analytical Processing (OLAP) tools

Question 6

Data mining

Answer 6

Finding hidden patterns and associations in data

Question 7

Features of an enterprise data warehouse

Answer 7

o An EDW
o An operational data store
o Data marts

Question 8

Operational data store

Answer 8

A hybrid data warehouse containing integrated information

Question 9

Data extraction

Answer 9

Gathering data from a variety of sources

Question 10

Data cleaning

Answer 10

Finding and correcting errors

Question 11

Data transformation

Answer 11

Converting data to warehouse format

Question 12

Data loading

Answer 12

Sorting/summarising/consolidating/checking integrity and building patterns accordingly

Question 13

Refreshing

Answer 13

Updating data sources to the warehouse

Question 14

Dimensional modelling

Answer 14

Different individual models (e.g. separate models for sales and inventory)

Question 15

True or false: Dimensional modelling leads to fewer tables

Answer 15

True! Data is grouped together and includes redundancies.

Question 16

Fact table

Answer 16

Measurement of business (e.g. sales)

Question 17

True or false: Fact tables are usually small

Answer 17

False! Fact tables usually range from 1 to 5 TB.

Question 18

Transaction fact table

Answer 18

Information related to events (e.g. product sales)

Question 19

Snapshot fact table

Answer 19

Information from specific moments in time (e.g. year-end accounts)

Question 20

Accumulating snapshot

Answer 20

A running tally (e.g. year to year sales figures)

Question 21

Dimension table

Answer 21

Describes dimensions of the business (e.g. product, customer)

Question 22

True or false: Dimension tables are larger than fact tables

Answer 22

False! They are usually smaller than fact tables.

Question 23

Types of dimension table

Answer 23

o	Time
o	Geography (address/location)
o	Product
o	Customer
o	Range (range of values for time, price, etc.)

Question 24

Star schema

Answer 24

Dimension tables connected to a central fact table

Question 25

True or false: A star schema is normalised

Answer 25

Both! The fact table is normalised but the dimension tables aren't, resulting in faster read queries and analysis.

Question 26

How does a star schema work?

Answer 26

Fact data is pulled from dimension tables, duplicated and stored in the fact table

Question 27

Advantages of star schema

Answer 27

o Simpler queries – dimension tables are treated as one large table o Easier business insights o Better query performance o No bottlenecks from normalised schemas o Faster queries o Read only o Can be used to build OLAP cubes

Question 28

Disadvantages of star schema

Answer 28

o Less accurate data due to normalisation o Less capable of handling complex queries o No many-to-many relationships

Question 29

Snowflake schema

Answer 29

Like a star schema, but the dimension tables connect to other dimension tables

Question 30

True or false: A snowflake schema is normalised

Answer 30

True! Its purpose is to normalise data in a star schema.

Question 31

Advantages of snowflake schema

Answer 31

o No integrity issues o Faster write queries o Uses less space than star schema

Question 32

Why are read queries slower in a snowflake schema?

Answer 32

The DBMS needs to search through multiple tables

Question 33

Roll-up

Answer 33

Removes dimensions in a data cube by: o Climbing up a hierarchy for a dimension o Dimension reduction (e.g. orders grouped by city rather than country)

Question 34

Drill-down

Answer 34

Adds dimensions to a data cube to create more detailed data

Question 35

Slice

Answer 35

Selects a dimension and creates a sub-cube

Question 36

Dice

Answer 36

Slicing multiple dimensions

Question 37

Pivot/rotation

Answer 37

Swapping data axes to provide an alternative presentation of data