Data Warehousing

Question 1

What is the process of removing all redundant information for a schema

Answer 1

Normalisation

Question 2

What are the 4 steps of the dimensional design process?

Answer 2

1) Select the business process
2) Declare the grain in business terms
3) Identify the dimensions
4) Identify the facts

Question 3

What is Grain?

Answer 3

What a single fact table row represent

Question 4

What is a dimension?

Answer 4

Provides context for the Fact measures.

Context being What, Where, When, Why, Who and How.

Question 5

What is a Fact?

Answer 5

Measurements that result from a business process event.

Question 6

What is a degenerate dimension?

Answer 6

TBC

Question 7

What are the 3 types of fact measure

Answer 7

1) Additive - Summed across all dimensions
2) Semi Additive - Summed across some dimensions (e.g. Not Date)
3) Non Additive - Cannot be summed

Question 8

Give an example of a Factless Fact

Answer 8

A fact table recording calendar day, student, teacher, class and location.

Question 9

What are the 3 main types of fact table

Answer 9

1) Transnational
2) Snapshot
3) Accumulating snapshot

Question 10

What is a consolidated fact table?

Answer 10

Where 2 or more linked business processes are combined in a single fact table. e.g sales and forecasts

Question 11

What is the name of the type of key is used to join dimensions and facts?

Answer 11

Surrogate Key or Primary Key / Foreign Key

Question 12

What is the name of the type of key which is used in operational systems to uniquely identify resources

Answer 12

Natural Key / Business Key

Question 13

What is a degenerate dimension and how does it occur?

Answer 13

This is a dimension key in a fact table that has no associated dimension. This could happen where all the information from an invoice is expressed in the rows and dimensions of a fact leaving the invoice number which is still useful for analysis.

Question 14

What name is given to a dimension which is joined to multiple times on a single fact table

Answer 14

Role-playing Dimension e.g. Date

Question 15

What are conformed dimensions?

Answer 15

Where the same dimensions apply to multiple facts which allows data to be easily combined.

Question 16

What is a shrunken dimension?

Answer 16

One which works on a subset of rows or attributes. This could be a dimension which is associated with an aggregated fact table

Question 17

What is value chain?

Answer 17

Natural flow of a businesses primary business e.g. Quote,Application,Completion

Question 18

What is a Data Warehouse Bus Matrix?

Answer 18

A matrix of rows representing business processes and columns representing the conformed dimensions which relates to these business processes.

Question 19

What is a Implementation Bus Matrix?

Answer 19

A matrix of rows representing the data warehouse facts (Within each business process) and columns representing the conformed dimensions which relates to these facts

Question 20

What is a Opportunity / Stakeholder Matrix?

Answer 20

A matrix of rows representing business processes and columns representing the business groups / departments interested in these business processes

Question 21

What are the 7 slowly changing dimension attribute definitions?

Answer 21

Type 0: Retain original
Type 1: Overwrite
Type 2: Add new row
Type 3: Add new attribute (alt Reality)
Type 4: Add mini dimension (rapid change)
Type 5: Add mini dimension + Type 1 (outrigger)
Type 6: Add Type 1 and Type 2 
Type 7: Duel Type 1 and Type 2

Question 22

What would you do if you have a fact table where a dimension can be multi-valued e.g. Hospital diagnosis?

Answer 22

Use a bridging table which has a row for each diagnosis

Question 23

What is behavioral tag time series?

Answer 23

a customer is assigned a tag on a periodic basis based on behavioral traits. The current value is stored as well as a string showing how the tags have changed over time

Question 24

How should you store textual comments in a data warehouse

Answer 24

Not on the fact table but using a text comment dimension

Question 25

What is a step dimension?

Answer 25

In a sequential process such as a web session each event is often stored in a fact table. The step dimension associated with the fact would identify what steps the row represents and how many steps occurred in session

Question 26

What is a causal dimension?

Answer 26

One which describes the indirect conditions which existed when the fact event occurred e.g. promotion

Question 27

What type of dimension describes the indirect conditions which existed when the fact event occurred

Answer 27

causal dimension (e.g. Promotion)

Question 28

In a fact table that contains rows representing the stages of an event what type of dimension is used to store the stage number?

Answer 28

What is a step dimension

Question 29

What is the name given to periodic behavioral data codes stored in a string in the data warehouse?

Answer 29

Behavioral tag time series

Question 30

What is the name given to the natural flow of a businesses primary business?

Answer 30

Value chain

Question 31

What are the 3 types of bus matrices used in data warehousing?

Answer 31

1) Implementation Bus Matrix
2) Data Warehouse Bus Matrix
3) Opportunity / Stakeholder Matrix

Question 32

What 4 questions could you ask when you are deciding whether to combine facts into a single fact table?

Answer 32

1) What are users analytic requirements?
2) Are there really multiple unique business processes
3) Are there multiple source systems capturing metrics at different granuality
4) What are the dimensionality of the facts

Question 33

What are the three popular attributes used in behavioral tag time series

Answer 33

1) Recency - How many days since last purchase
2) Frequency - How many times has customer ordered or visted
3) Intensity / monetary - How much have they spend (e.g this year)