Data Warehousing Concepts and Design

1
Data Warehousing Concepts and Design

• Chapters 31 32 in textbook

2
What is Data Warehousing?

• A subject-oriented, integrated, time-variant and
  non-volatile collection of data.
• Supports decision-making process.
• Benefits
• Potential high returns on investment.
• Competitive advantage.
• Increased productivity of corporate
  decision-makers.

3
OLTP vs. DW
OLTP Data Warehousing
- Current data - Detailed data - Dynamic data - Transaction-driven - Application-oriented - Clerical\operational users - Historical data - Detailed summarized data - Static data - Analysis-driven - Subject-oriented -Managers
4
DW Architecture
5
Data Warehousing Design

• DW Design depends on the questions that managers
  impose on a DW.
• The data itself gets
• Extracted from OLTP systems.
• Cleaned to get rid of redundancy and missing
  values.
• Stored in a warehouse.
• In step 3, we design the DW.

6
Dimensionality Modeling (DM)

• DM ER modeling with restrictions.
• Each design consists of
• 1 Fact Table group of foreign keys.
• Many Dimensional Tables each has a primary key
  corresponding to a foreign key in fact table.
• A join between all tables ? the whole
  un-normalized DB.
• Popular schemas
• Star.
• Snowflake.
• Starflake.

7
Example Star Schema
8
DW Design Methodology

1. Choosing the process.
2. Choosing the grain.
3. Identifying the dimensions.
4. Choosing the facts.
5. Storing pre-calculations.
6. Rounding-out the dimension tables.
7. Choosing the duration of the DB.
8. Tracking slowly changing dimensions.
9. Deciding query priorities and modes.