Dimensional Modeling on the Microsoft SQL Server Platform

Focused View

Christopher Smith

3:09:00

39 View

0. Course Overview

0. Course Overview.mp4

01:25

1. Understanding How to Model Dimension Tables

0. Understanding How to Model Dimension Tables.mp4

00:35

1. De-normalization.mp4

02:26

2. Dimension Table Components.mp4

00:28

3. Surrogate Keys and Smart Keys.mp4

02:01

4. Natural Keys.mp4

00:42

5. Attributes.mp4

00:22

6. Quality Screens and Audit Flags.mp4

03:02

7. Datestamps and ETL Identifiers.mp4

00:55

8. Example- DimProduct.mp4

00:58

9. Dealing with Changes.mp4

01:38

10. Example- SCD Types 1, 2, and 3.mp4

02:07

11. What We Learned.mp4

00:35

2. Loading a Dimension Table in SQL Server

0. Loading a Dimension Table in SQL Server.mp4

01:11

1. SSIS SCD Component - Overview.mp4

01:48

2. SSIS SCD Component - Setup and Configuration.mp4

06:37

3. Demo- SSIS SCD Component.mp4

02:19

4. SSIS SCD Component - Downsides.mp4

01:30

5. SSIS SCD Component - Downsides Demo.mp4

04:10

6. Staging Tables and T-SQL Merge.mp4

02:29

7. Staging Tables and T-SQL Merge - SCD Type 1 Demo.mp4

04:28

8. Staging Tables and T-SQL Merge - SCD Type 2,3 Demo.mp4

06:38

9. SentryOne Task Factory.mp4

00:44

10. Using Temporal Tables as a Dimension.mp4

02:40

11. Demo- Temporal Dimension Table.mp4

03:33

12. Temporal Table Pros and Cons as a Dimension Table.mp4

01:31

13. What We Learned.mp4

00:54

3. Understanding How to Model Fact Tables

0. Understanding How to Model Fact Tables.mp4

00:44

1. Fact Table Overview.mp4

01:29

2. Foreign Keys.mp4

00:43

3. Measures- Additive, Semi-additive, and Non-additive.mp4

02:22

4. Degenerate Dimensions and Other Components.mp4

01:05

5. Value Bands.mp4

01:20

6. Demo- Value Bands.mp4

01:16

7. Value Bands- Alternate Approach.mp4

00:59

8. Demo- Value Bands- Alternate Approach.mp4

01:34

9. Transaction, Periodic Snapshot, and Accumulating Snapshot Fact Tables.mp4

02:47

10. What We Learned.mp4

00:45

4. Loading a Fact Table Using SSIS

0. Loading a Fact Table Using SSIS.mp4

00:49

1. Data Flow Components.mp4

03:39

2. Demo- Loading a Fact Table Using SSIS.mp4

05:36

3. Determining Data Flow Bottlenecks.mp4

03:55

4. Data Flow Source Component Bottlenecks.mp4

01:49

5. Data Flow Transformation Component Bottlenecks.mp4

04:53

6. Data Flow Destination Component Bottlenecks.mp4

02:22

7. General SSIS Performance Considerations.mp4

05:14

8. Methods to Deal with Late Arriving Dimension Values.mp4

03:31

9. Demo- Late Arriving Dimension Inferred Member Approach.mp4

03:28

10. What We Learned.mp4

00:40

5. Exploring Incremental ETL Options

0. Exploring Incremental ETL Options.mp4

01:08

1. Historical vs. Incremental ETL and Incremental Load Strategies.mp4

02:22

2. Incremental Load Strategies- Database Triggers.mp4

06:58

3. Incremental Load Strategies- Timestamps.mp4

02:51

4. Incremental Load Strategies- Full Diff Compare.mp4

07:12

5. Microsoft Solutions- Change Data Capture and Change Tracking.mp4

04:15

6. What We Learned.mp4

00:41

6. Loading Data Incrementally Using Change Data Capture

0. Loading Data Incrementally Using Change Data Capture.mp4

00:56

1. CDC Set up and Configuration.mp4

03:09

2. Querying CDC Change Data.mp4

02:33

3. Demo- Configuring and Querying CDC.mp4

02:56

4. CDC SSIS Components.mp4

03:53

5. Demo- CDC SSIS Components.mp4

06:08

6. CDC Behavior with DDL Operations.mp4

01:11

7. What We Learned.mp4

00:58

7. Loading Data Incrementally Using Change Tracking

0. Loading Data Incrementally Using Change Tracking.mp4

00:47

1. Setup, Configuration, and Querying with Change Tracking.mp4

03:45

2. Demo- Set up, Configuration, and Querying.mp4

03:50

3. Snapshot Isolation and Change Tracking.mp4

01:44

4. Demo- Incremental ETL with Change Tracking.mp4

05:39

5. What We Learned.mp4

00:41

8. Exploring Options for Modeling Many-to-many Relationships

0. Exploring Options for Modeling Many-to-many Relationships.mp4

00:52

1. Modeling Many-to-many Relationships.mp4

01:39

2. Bridge Tables.mp4

03:06

3. Demo- Bridge Tables.mp4

03:11

4. Bridge Alternatives- Allocate to Fact.mp4

01:54

5. Bridge Alternatives- Assign Primary-Secondary Values.mp4

01:48

6. Bridge Alternatives- Named Attributes and Concatenated Values.mp4

02:25

7. What We Learned.mp4

01:42

Description

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.

More details

User Reviews

Rating

average 0

Total votes0

Focused display

SQL Server

Microsoft SQL

Christopher Smith

Instructor's Courses

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 Methods for Business Decisions. He’s also a Microsoft Certified Solutions Associate in SQL 2016 BI Development. When he’s not immersed in the world of BI, he enjoys biking, hiking, and spending time with his wife and twin boys.

Pluralsight

View courses Pluralsight

Pluralsight, LLC is an American privately held online education company that offers a variety of video training courses for software developers, IT administrators, and creative professionals through its website. Founded in 2004 by Aaron Skonnard, Keith Brown, Fritz Onion, and Bill Williams, the company has its headquarters in Farmington, Utah. As of July 2018, it uses more than 1,400 subject-matter experts as authors, and offers more than 7,000 courses in its catalog. Since first moving its courses online in 2007, the company has expanded, developing a full enterprise platform, and adding skills assessment modules.