Data Warehousing and Business Intelligence

3.

Dimensional Data Modeling

3.1.

Fundamentals of Dimensional Modeling

3.1.1.

Design Goals and Principles

3.1.1.1.

Simplicity and Usability

3.1.1.2.

Query Performance Optimization

3.1.1.3.

Business User Accessibility

3.1.2.

Dimensional vs. Entity-Relationship Modeling

3.1.2.1.

ER Modeling Characteristics

3.1.2.2.

Dimensional Modeling Characteristics

3.1.2.3.

When to Use Each Approach

3.1.2.4.

Conversion Between Models

3.2.

3.2.1.

Definition and Purpose

3.2.1.1.

Quantitative Data Storage

3.2.1.2.

Business Process Measurement

3.2.1.3.

Central Role in Schema

3.2.2.

Fact Table Granularity

3.2.2.1.

Transaction-Level Detail

3.2.2.2.

Summary-Level Aggregation

3.2.2.3.

Granularity Selection Criteria

3.2.2.4.

Impact on Storage and Performance

3.2.3.

3.2.3.1.

3.2.3.2.

Semi-Additive Facts

3.2.3.3.

Non-Additive Facts

3.2.3.4.

3.2.4.

Fact Table Types

3.2.4.1.

Transaction Fact Tables

3.2.4.2.

Periodic Snapshot Fact Tables

3.2.4.3.

Accumulating Snapshot Fact Tables

3.2.4.4.

Factless Fact Tables

3.2.4.5.

Coverage Tables

3.3.

Dimension Tables

3.3.1.

Definition and Purpose

3.3.1.1.

Descriptive Context

3.3.1.2.

Query Filtering and Grouping

3.3.1.3.

Hierarchical Organization

3.3.2.

Dimension Attributes

3.3.2.1.

Descriptive Fields

3.3.2.2.

Hierarchical Relationships

3.3.2.3.

Attribute Categories

3.3.2.4.

Attribute Naming Conventions

3.3.3.

3.3.3.1.

3.3.3.2.

3.3.3.3.

Key Management Strategies

3.3.3.4.

Key Assignment Methods

3.3.4.

Special Dimension Types

3.3.4.1.

Degenerate Dimensions

3.3.4.2.

Role-Playing Dimensions

3.3.4.3.

Junk Dimensions

3.3.4.4.

Conformed Dimensions

3.4.

Dimensional Hierarchies

3.4.1.

Natural Hierarchies

3.4.1.1.

Geographic Hierarchies

3.4.1.2.

Time Hierarchies

3.4.1.3.

Organizational Hierarchies

3.4.1.4.

Product Hierarchies

3.4.2.

Ragged Hierarchies

3.4.2.1.

Uneven Level Structures

3.4.2.2.

Handling Missing Levels

3.4.2.3.

Implementation Strategies

3.4.3.

Skipped-Level Hierarchies

3.4.3.1.

Non-Contiguous Levels

3.4.3.2.

Reporting Challenges

3.4.3.3.

Design Solutions

3.4.4.

Multiple Hierarchies

3.4.4.1.

Alternative Drill-Down Paths

3.4.4.2.

Hierarchy Selection

3.4.4.3.

Implementation Considerations

3.5.

Slowly Changing Dimensions

3.5.1.

SCD Type 0: Retain Original

3.5.1.1.

Fixed Attribute Values

3.5.1.2.

Use Cases and Examples

3.5.2.

SCD Type 1: Overwrite

3.5.2.1.

Current Value Only

3.5.2.2.

Implementation Approach

3.5.2.3.

Advantages and Limitations

3.5.3.

SCD Type 2: Add New Row

3.5.3.1.

Historical Tracking

3.5.3.2.

Effective Dating

3.5.3.3.

Current Record Indicators

3.5.4.

SCD Type 3: Add New Attribute

3.5.4.1.

Limited History Tracking

3.5.4.2.

Previous Value Storage

3.5.4.3.

Use Cases and Limitations

3.5.5.

Hybrid SCD Approaches

3.5.5.1.

Mixed Type Implementation

3.5.5.2.

Attribute-Level Decisions

3.5.5.3.

Complex Change Tracking

3.6.

Schema Design Patterns

3.6.1.

3.6.1.1.

Structure and Characteristics

3.6.1.2.

Denormalized Dimensions

3.6.1.3.

Query Performance Benefits

3.6.1.4.

Maintenance Considerations

3.6.2.

Snowflake Schema

3.6.2.1.

Normalized Dimension Structure

3.6.2.2.

Storage Efficiency

3.6.2.3.

Query Complexity Trade-offs

3.6.2.4.

When to Use Snowflaking

3.6.3.

3.6.3.1.

Multiple Fact Tables

3.6.3.2.

Shared Dimensions

3.6.3.3.

Complex Business Models

3.6.3.4.

Integration Challenges

Previous

2. Data Warehouse Architecture and Components

Go to top

Next

4. Data Integration: ETL and ELT Processes