Data Warehousing and Business Intelligence

  1. Dimensional Data Modeling
    1. Fundamentals of Dimensional Modeling
      1. Design Goals and Principles
        1. Simplicity and Usability
          1. Query Performance Optimization
            1. Business User Accessibility
            2. Dimensional vs. Entity-Relationship Modeling
              1. ER Modeling Characteristics
                1. Dimensional Modeling Characteristics
                  1. When to Use Each Approach
                    1. Conversion Between Models
                  2. Fact Tables
                    1. Definition and Purpose
                      1. Quantitative Data Storage
                        1. Business Process Measurement
                          1. Central Role in Schema
                          2. Fact Table Granularity
                            1. Transaction-Level Detail
                              1. Summary-Level Aggregation
                                1. Granularity Selection Criteria
                                  1. Impact on Storage and Performance
                                  2. Types of Facts
                                    1. Additive Facts
                                      1. Semi-Additive Facts
                                        1. Non-Additive Facts
                                          1. Derived Facts
                                          2. Fact Table Types
                                            1. Transaction Fact Tables
                                              1. Periodic Snapshot Fact Tables
                                                1. Accumulating Snapshot Fact Tables
                                                  1. Factless Fact Tables
                                                    1. Coverage Tables
                                                  2. Dimension Tables
                                                    1. Definition and Purpose
                                                      1. Descriptive Context
                                                        1. Query Filtering and Grouping
                                                          1. Hierarchical Organization
                                                          2. Dimension Attributes
                                                            1. Descriptive Fields
                                                              1. Hierarchical Relationships
                                                                1. Attribute Categories
                                                                  1. Attribute Naming Conventions
                                                                  2. Dimension Keys
                                                                    1. Natural Keys
                                                                      1. Surrogate Keys
                                                                        1. Key Management Strategies
                                                                          1. Key Assignment Methods
                                                                          2. Special Dimension Types
                                                                            1. Degenerate Dimensions
                                                                              1. Role-Playing Dimensions
                                                                                1. Junk Dimensions
                                                                                  1. Conformed Dimensions
                                                                                2. Dimensional Hierarchies
                                                                                  1. Natural Hierarchies
                                                                                    1. Geographic Hierarchies
                                                                                      1. Time Hierarchies
                                                                                        1. Organizational Hierarchies
                                                                                          1. Product Hierarchies
                                                                                          2. Ragged Hierarchies
                                                                                            1. Uneven Level Structures
                                                                                              1. Handling Missing Levels
                                                                                                1. Implementation Strategies
                                                                                                2. Skipped-Level Hierarchies
                                                                                                  1. Non-Contiguous Levels
                                                                                                    1. Reporting Challenges
                                                                                                      1. Design Solutions
                                                                                                      2. Multiple Hierarchies
                                                                                                        1. Alternative Drill-Down Paths
                                                                                                          1. Hierarchy Selection
                                                                                                            1. Implementation Considerations
                                                                                                          2. Slowly Changing Dimensions
                                                                                                            1. SCD Type 0: Retain Original
                                                                                                              1. Fixed Attribute Values
                                                                                                                1. Use Cases and Examples
                                                                                                                2. SCD Type 1: Overwrite
                                                                                                                  1. Current Value Only
                                                                                                                    1. Implementation Approach
                                                                                                                      1. Advantages and Limitations
                                                                                                                      2. SCD Type 2: Add New Row
                                                                                                                        1. Historical Tracking
                                                                                                                          1. Effective Dating
                                                                                                                            1. Current Record Indicators
                                                                                                                            2. SCD Type 3: Add New Attribute
                                                                                                                              1. Limited History Tracking
                                                                                                                                1. Previous Value Storage
                                                                                                                                  1. Use Cases and Limitations
                                                                                                                                  2. Hybrid SCD Approaches
                                                                                                                                    1. Mixed Type Implementation
                                                                                                                                      1. Attribute-Level Decisions
                                                                                                                                        1. Complex Change Tracking
                                                                                                                                      2. Schema Design Patterns
                                                                                                                                        1. Star Schema
                                                                                                                                          1. Structure and Characteristics
                                                                                                                                            1. Denormalized Dimensions
                                                                                                                                              1. Query Performance Benefits
                                                                                                                                                1. Maintenance Considerations
                                                                                                                                                2. Snowflake Schema
                                                                                                                                                  1. Normalized Dimension Structure
                                                                                                                                                    1. Storage Efficiency
                                                                                                                                                      1. Query Complexity Trade-offs
                                                                                                                                                        1. When to Use Snowflaking
                                                                                                                                                        2. Galaxy Schema
                                                                                                                                                          1. Multiple Fact Tables
                                                                                                                                                            1. Shared Dimensions
                                                                                                                                                              1. Complex Business Models
                                                                                                                                                                1. Integration Challenges

                                                                                                                                                            Previous

                                                                                                                                                            2. Data Warehouse Architecture and Components

                                                                                                                                                            Go to top

                                                                                                                                                            Next

                                                                                                                                                            4. Data Integration: ETL and ELT Processes