Dimensional Modeling on the Microsoft SQL Server Platform
This course teaches you how to use Microsoft SQL Server and SQL Server Integration Services to design and efficiently load data to your data warehouse. You'll also learn techniques for troubleshooting performance bottlenecks in your ETL.
What you'll learn
A properly designed dimensional model is essential to delivering large volumes of data in a fast and easily understood manner. In this course, Dimensional Modeling on the Microsoft SQL Server Platform, you'll learn how to design and efficiently load dimensions, facts, and bridge tables on the Microsoft SQL Server platform. First, you’ll learn about type 1, 2, and 3 slowly changing dimensions and several methods for loading data into these dimensions. Then, you’ll discover how to use SSIS to load data into fact tables along with several options to process data incrementally, including 2 built-in Microsoft technologies: Change Data Capture and Change Tracking. Finally, you'll explore modeling techniques to handle many-to-many relationships. When you’re finished with this course, you'll have the skills and knowledge to design a proper dimensional model and load data efficiently using the Microsoft SQL Server platform.
Table of contents
- Version Check 0m
- Understanding How to Model Dimension Tables 1m
- De-normalization 2m
- Dimension Table Components 0m
- Surrogate Keys and Smart Keys 2m
- Natural Keys 1m
- Attributes 0m
- Quality Screens and Audit Flags 3m
- Datestamps and ETL Identifiers 1m
- Example: DimProduct 1m
- Dealing with Changes 2m
- Example: SCD Types 1, 2, and 3 2m
- What We Learned 1m
- Loading a Dimension Table in SQL Server 1m
- SSIS SCD Component - Overview 2m
- SSIS SCD Component - Setup and Configuration 7m
- Demo: SSIS SCD Component 2m
- SSIS SCD Component - Downsides 2m
- SSIS SCD Component - Downsides Demo 4m
- Staging Tables and T-SQL Merge 2m
- Staging Tables and T-SQL Merge - SCD Type 1 Demo 4m
- Staging Tables and T-SQL Merge - SCD Type 2,3 Demo 7m
- SentryOne Task Factory 1m
- Using Temporal Tables as a Dimension 3m
- Demo: Temporal Dimension Table 4m
- Temporal Table Pros and Cons as a Dimension Table 2m
- What We Learned 1m
- Understanding How to Model Fact Tables 1m
- Fact Table Overview 1m
- Foreign Keys 1m
- Measures: Additive, Semi-additive, and Non-additive 2m
- Degenerate Dimensions and Other Components 1m
- Value Bands 1m
- Demo: Value Bands 1m
- Value Bands: Alternate Approach 1m
- Demo: Value Bands: Alternate Approach 2m
- Transaction, Periodic Snapshot, and Accumulating Snapshot Fact Tables 3m
- What We Learned 1m
- Loading a Fact Table Using SSIS 1m
- Data Flow Components 4m
- Demo: Loading a Fact Table Using SSIS 6m
- Determining Data Flow Bottlenecks 4m
- Data Flow Source Component Bottlenecks 2m
- Data Flow Transformation Component Bottlenecks 5m
- Data Flow Destination Component Bottlenecks 2m
- General SSIS Performance Considerations 5m
- Methods to Deal with Late Arriving Dimension Values 4m
- Demo: Late Arriving Dimension Inferred Member Approach 3m
- What We Learned 1m
- Exploring Incremental ETL Options 1m
- Historical vs. Incremental ETL and Incremental Load Strategies 2m
- Incremental Load Strategies: Database Triggers 7m
- Incremental Load Strategies: Timestamps 3m
- Incremental Load Strategies: Full Diff Compare 7m
- Microsoft Solutions: Change Data Capture and Change Tracking 4m
- What We Learned 1m
- Exploring Options for Modeling Many-to-many Relationships 1m
- Modeling Many-to-many Relationships 2m
- Bridge Tables 3m
- Demo: Bridge Tables 3m
- Bridge Alternatives: Allocate to Fact 2m
- Bridge Alternatives: Assign Primary/Secondary Values 2m
- Bridge Alternatives: Named Attributes and Concatenated Values 2m
- What We Learned 2m
About the author
Christopher is a Business Intelligence Engineer at Strategic Healthcare Programs, a data analytics and benchmarking company in the healthcare industry. He’s worked in BI for over 10 years, focusing primarily on SQL Server’s BI stack. Over the course of his career he’s worked on data warehousing solutions in the banking, auto insurance, and healthcare industries. He is a proud alumnus of Bentley University, where he holds an MBA with concentrations in Information Technology and Quantitative Metho... more