Data Warehouse Toolkit

Question 1

Why should reporting not be done from an Operational Source System?

Answer 1

The collection of data happens on a row-by-row basis. Reporting for the sake of making business decisions happens at an aggregated level. These are two different worlds.

Question 2

Name the 6 goals of a Data Warehouse.

Answer 2

• Data should be easily accessible.
• The data should be consistent and have a high level of quality.
• The DW must be resilient to change.
• The data must meet the security needs of the business.
• The DW must improve business decisions.
• The DW must be accepted and used by Business Users.

Question 3

What are the 4 components of a data warehouse

Answer 3

• Operational Source Systems
• Data Staging Area
• Data Presentation Area
• Data Access Tools

Question 4

What is an Operational Source System?

Answer 4

It is where business transaction data is collected.

Question 5

What happens in the Data Staging Area?

Answer 5

ETL.

Question 6

What are Data Marts?

Answer 6

‘A wedge of the overall presentation area pie.’ A single data mart represents the relevant data for a specific business process.

Question 7

Why should Data Marts use a Dimensional Model instead of 3rd-normal-form?

Answer 7

3rd-normal-form may be superior for operational processing, but it results in complicated schemas and is therefore confusing and cumbersome to query.

Question 8

Why is it important to give access to data more granular than the relevant summary/aggregated data?

Answer 8

Even if Business Users will rarely be concerned with the row-level detail, providing the most granular possible data will allow flexibility in what questions Business Users can ask.

Question 9

Where do numerical data generally get stored?

Answer 9

In the Fact Tables.

Question 10

Where do descriptor data generally get stored?

Answer 10

In Dimension Tables.

Question 11

What is the difference between a Star Schema and a snowflake Schema? Which is superior and why?

Answer 11

In a Star Schema there is one fact table surrounded by only one level of Dimension Tables. In a snowflake Schema, your dimension tables may be further normalized into multiple levels.
Star schema is preferred in a dimension model. While snowflake can be efficient storage-wise, it is harder to query in the same way that 3NF is.

Question 12

Marketing and Sales are both concerned with lead generation metrics. Should I give each of them a lead generation data mart?

Answer 12

No. Data Marts should be process-centric, not department centric. This simplifies the data warehousing system.

Question 13

What are the 4 Steps in the Dimensional Design Process?

Answer 13

• Select the business process to model
• Declare the grain of the business process.
• Choose the dimensions.
• Identify the numeric facts.

Question 14

How can you choose which business model to process when there may be many?

Answer 14

Choose the business model that represents the most pressing business question.

Question 15

How can you choose the grain?

Answer 15

The most atomic (most granular) level is almost always the preferred level of granularity.

Question 16

How many dimensions should I have

Answer 16

You generally should prefer 15 or less. If you have more than 25, consider combining some of the dimensions.

Question 17

How should we model a hierarchy?

Answer 17

Combine it into one dimension. Avoid snowflaking by having the first level of the hierarchy point at a higher level’s dimension table. Also avoid centipeding by having a dimension table for every level of the hierarchy.

Question 18

Why should I avoid putting profit ratio in my fact table?

Answer 18

Because it is an issue it is not additive. Adding together the gross margin for a set of rows will give the incorrect result because of the incorrect order of operations.

Question 19

Why should we prefer values such as ‘Holiday’/’Nonholiday’ instead of ‘Y’/’N’?

Answer 19

The dimension values should be as descriptive as possible for ease of use. On a report, it can be hard to understand what ‘Y’ or ‘N’ means why “Nonholiday’ is quite clear.

Question 20

How should we model time of day?

Answer 20

In a separate dimension. The Fact table will have another column that points at this time of day dimension.

Question 21

Should sqft be modeled as a dimension or a numeric fact?

Answer 21

Generally this will be used to categorize so it should be a dimension. If we know that the business users find doing calculations on sq footage important, then we might decide to include it as a numeric fact.

Question 22

What is a causal dimension?

Answer 22

A dimension that is believed to possibly change the numeric facts of the modeled business process.

Question 23

If there is a dimension that doesn’t apply to all of the rows in the fact table, what should we do?

Answer 23

We need to avoid NULLs, so we need to add a value to this dimension called “Not applicable’ or something similar.

Question 24

What is a degenerate dimension?

Answer 24

A dimension which is important for the analysis but for which the table would only be on column wide because all relevant fields are modeled in other dimensions. These are common when there is a parent level of granularity which is identified by one of our fields.

Question 25

How can I add a dimension to an already-modeled business process.

Answer 25

Simply add a dimension table and add a column in the fact table. To avoid NULLs we will need to have values such as ‘Before ‘ which we can point the old fact tables at.

Question 26

What is centipeding?

Answer 26

Adding way too many dimensions to a star schema.

Question 27

What are surrogate keys?

Answer 27

Keys for joining which are not based on codes or keys from the business process.

Question 28

What are 4 benefits to surrogate keys?

Answer 28

• It prevents changes in the business process’s codes from affecting our data warehouse.
• It allows flexibility for catch values such as ‘Not available’.
• It prevents people from trying to bypass the joining to dimension tables by parsing the key.
• It is more efficient storage-wise.