Advanced Integration Services
This course teaches how to work with Integration Services packages in the context of a data warehouse development project to perform extract, transform, and load operations.
What you'll learn
This course explains how to apply Integration Services features to build packages that support the extract, transform, and load operations of a data warehouse.
It covers design patterns for staging data and for loading data into fact and dimension tables. In addition, this course describes how to enhance ETL packages with data cleansing techniques and offers insight into the buffer architecture of the data flow engine to help package developers get the best performance from packages.
This course was written for SQL Server 2012 Integration Services, but most principles apply to SQL Server 2005 and later.
Before taking this course, you should be familiar with SSIS basics
Table of contents
- Introduction 1m
- Introduction to Data Warehousing 5m
- Dimensional Modeling 6m
- Data Profiling 2m
- Demo: Data Profiling 8m
- ETL Design Patterns 3m
- Extract Package 4m
- Demo: Extracting to Staging Tables 12m
- Load Patterns 2m
- Fact Extract for Ongoing Load 3m
- Demo: Extracting Records Since Last Load 8m
- Summary 4m
- Introduction 1m
- Slowly Changing Dimensions 3m
- Dimension Load Patterns: Type 0, Type 1, and Type 2 5m
- Dimension Load Package 1m
- Demo: Transforming and Loading Data for Type 0 7m
- Demo: Loading Data with the Slowly Changing Dimension Wizard 14m
- Fact Table Design Patterns 2m
- Demo: Performing ETL for a Fact Table 7m
- Demo: Selecting Data for Ongoing Fact Table Load 2m
- Analysis Services Objects 1m
- Demo: Using the Analysis Services Task 4m
- Demo: Orchestrating the ETL Process 3m
- Summary 2m
- Introduction 1m
- Data Flow and Data Quality 4m
- Column Problems: Missing Data Default 1m
- Demo: Derived Column 4m
- Column Problems: Derive Missing Data 1m
- Demo: Lookup Transformation 6m
- Column Problems: Translation 2m
- Demo: Lookup Transformation and Cache Transformation 6m
- Column Problems: Data Type 2m
- Demo: Data Conversion Transformation 5m
- Column Problems: Truncation 2m
- Demo: Flagging Unfixable Data Flow Errors 3m
- Record Problems: Missing Dimension Data 3m
- Demo: Inferred Members 11m
- Record Problems: Lookup Failures 2m
- Demo: Fuzzy Lookup Transformation 7m
- Record Problems: Duplicate Data 1m
- Record Problems: Fuzzy Grouping and Data 4m
- Business Rule Problems: Out of Range Values 1m
- Data Quality Services 3m
- Summary 4m
- Introduction 1m
- Pipeline Buffer Architecture 4m
- Non-Blocking Streaming Transformations 2m
- Demo: Executing Non-Blocking Streaming Transformations 2m
- Non-Blocking Row-Based Transformations 1m
- Demo: Executing Non-Blocking Row-Based Transformations 2m
- Partially Blocking Transformations 1m
- Demo: Executing Partially Blocking Transformations 1m
- Blocking Transformations 1m
- Demo: Executing Blocking Transformations 1m
- Synchronous versus Asynchronous 1m
- Execution Trees 3m
- Demo: Reviewing the Pipeline Execution Tree Log 4m
- Performance Optimization: Buffers 3m
- Demo: Performance Optimization: Buffer Sizing 2m
- Parallelism, Transformations, Sources, and Destinations 3m
- Performance Monitoring 3m
- Summary 3m
About the author
Stacia Misner Varga is an instructor, author, and principal consultant of Data Inspirations. Her career spans more than 30 years, with a focus on improving business practices through technology. Since 2000, she has provided consulting and education services for Microsoft’s data platform and authored or co-authored many books covering this topic. In addition, Stacia has been a frequent speaker over the years at technology conferences worldwide.