The Data Warehouse OLTP v DSS Data Modeling

The Data Warehouse

OLTP v. DSS

Data Modeling & Normalization • One-to-One Relationships • One-to-Many Relationships • Many-to-Many Relationships

Figure 6. 1 A simple entityrelationship diagram

Data Modeling & Normalization • First Normal Form • Second Normal Form • Third Normal Form

Entity Integrity Rule • Primary key properties 1. Uniqueness 1. All the rows in a relational table can be uniquely identified 2. Minimality 1. Key consists of 1+ attributes 2. Minimality property ensures the length of the key is no longer than necessary to insure uniqueness is guaranteed

Normalization Example Order Number Primary Key Item Number Primary Key Order date Customer ID Product Description Quantity Determinant rules identify dependencies 1. Order Number determines Customer ID 2. Order Number & Item Number determine Product ID 3. Order Number & Item Number determine Product Description 4. Order Number & Item Number determine Quantity 5. Order Number determines Order Date

Normalization Example Order Number Primary Key Item Number Primary Key Order date Customer ID Product Description Quantity Order Number Primary Key Order date Customer ID Order Item Order Number Primary Key Item Number Primary Key Product ID Product Description Quantity

Normalization Example Order Number Primary Key Order date Customer ID Order Item Order Number Primary Key Item Number Primary Key Product ID Product Description Quantity Order Number Primary Key Item Number Primary Key Product ID Quantity Product ID Primary Key Product Description

The Relational Model

Data Warehouse Design

Figure 6. 2 A data warehouse process model

Entering Data into the Warehouse • Independent Data Mart • ETL (Extract, Transform, Load Routine) • Metadata

Structuring the Data Warehouse: The Star Schema • Fact Table • Dimension Tables • Slowly Changing Dimensions

Figure 6. 3 A star schema for credit cared purchases

The Multidimensionality of the Star Schema

Figure 6. 4 Dimensions of the fact table shown in Figure 6. 3

Additional Relational Schemas • Snowflake Schema • Constellation Schema

Figure 6. 5 A constellation schema for credit card purchases and promotions

Decision Support: Analyzing the Warehouse Data • Reporting Data • Analyzing Data • Knowledge Discovery

On-line Analytical Processing

OLAP Operations • • • Slice – A single dimension operation Dice – A multidimensional operation Roll-up – A higher level of generalization Drill-down – A greater level of detail Rotation – View data from a new perspective

Figure 6. 6 A multidemensional cube for credit card purchases

Concept Hierarchy A mapping that allows attributes to be viewed from varying levels of detail.

Figure 6. 7 A concept hierarchy for location

Figure 6. 8 Rolling up from months to quarters

Creating a Simple Pivot Table

Figure 6. 9 A pivot table template

Figure 6. 10 A summary report for income range

Pivot Tables for Hypothesis Testing

Figure 6. 12 A pivot table showing age and credit card insurance choice

Figure 6. 13 Grouping the credit card promotionn data by age

Figure 6. 14 Pivot. Table Layout Wizard

Creating a Multidimensional Pivot Table

Figure 6. 15 A credit card promotion cube

Figure 6. 16 A pivot table with page variables for credit card promotions