Module 1: Introduction to Data Warehousing
This module describes data warehouse concepts and architecture consideration.Lessons
-
Overview of Data Warehousing
-
Considerations for a Data Warehouse Solution
Lab : Exploring a Data Warehouse Solution
-
Exploring data sources
-
Exploring an ETL process
-
Exploring a data warehouse
Module 2: Planning Data Warehouse Infrastructure
This module describes the main hardware considerations for building a data warehouse.Lessons
-
Considerations for data warehouse infrastructure.
-
Planning data warehouse hardware.
Lab : Planning Data Warehouse Infrastructure
-
Planning data warehouse hardware
Module 3: Designing and Implementing a Data Warehouse
This module describes how you go about designing and implementing a schema for a data warehouse.Lessons
-
Designing dimension tables
-
Designing fact tables
-
Physical Design for a Data Warehouse
Lab : Implementing a Data Warehouse Schema
-
Implementing a star schema
-
Implementing a snowflake schema
-
Implementing a time dimension table
Module 4: Column store Indexes
This module introduces Columnstore Indexes.Lessons
-
Introduction to Columnstore Indexes
-
Creating Columnstore Indexes
-
Working with Columnstore Indexes
Lab : Using Columnstore Indexes
-
Create a Columnstore index on the FactProductInventory table
-
Create a Columnstore index on the FactInternetSales table
-
Create a memory optimized Columnstore table
Module 5: Implementing an Azure SQL Data Warehouse
This module describes Azure SQL Data Warehouses and how to implement them.Lessons
-
Advantages of Azure SQL Data Warehouse
-
Implementing an Azure SQL Data Warehouse
-
Developing an Azure SQL Data Warehouse
-
Migrating to an Azure SQ Data Warehouse
-
Copying data with the Azure data factory
Lab : Implementing an Azure SQL Data Warehouse
-
Create an Azure SQL data warehouse database
-
Migrate to an Azure SQL Data warehouse database
-
Copy data with the Azure data factory
Module 6: Creating an ETL Solution
At the end of this module you will be able to implement data flow in a SSIS package.Lessons
-
Introduction to ETL with SSIS
-
Exploring Source Data
-
Implementing Data Flow
Lab : Implementing Data Flow in an SSIS Package
-
Exploring source data
-
Transferring data by using a data row task
-
Using transformation components in a data row
Module 7: Implementing Control Flow in an SSIS Package
This module describes implementing control flow in an SSIS package.Lessons
-
Introduction to Control Flow
-
Creating Dynamic Packages
-
Using Containers
-
Managing consistency.
Lab : Implementing Control Flow in an SSIS Package
-
Using tasks and precedence in a control flow
-
Using variables and parameters
-
Using containers
Lab : Using Transactions and Checkpoints
-
Using transactions
-
Using checkpoints
Module 8: Debugging and Troubleshooting SSIS Packages
This module describes how to debug and troubleshoot SSIS packages.Lessons
-
Debugging an SSIS Package
-
Logging SSIS Package Events
-
Handling Errors in an SSIS Package
Lab : Debugging and Troubleshooting an SSIS Package
-
Debugging an SSIS package
-
Logging SSIS package execution
-
Implementing an event handler
-
Handling errors in data flow
Module 9: Implementing a Data Extraction Solution
This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.Lessons
-
Introduction to Incremental ETL
-
Extracting Modified Data
-
Loading modified data
-
Temporal Tables
Lab : Extracting Modified Data
-
Using a datetime column to incrementally extract data
-
Using change data capture
-
Using the CDC control task
-
Using change tracking
Lab : Loading a data warehouse
-
Loading data from CDC output tables
-
Using a lookup transformation to insert or update dimension data
-
Implementing a slowly changing dimension
-
Using the merge statement
Module 10: Enforcing Data Quality
This module describes how to implement data cleansing by using Microsoft Data Quality services.Lessons
-
Introduction to Data Quality
-
Using Data Quality Services to Cleanse Data
-
Using Data Quality Services to Match Data
Lab : Cleansing Data
-
Creating a DQS knowledge base
-
Using a DQS project to cleanse data
-
Using DQS in an SSIS package
Lab : De-duplicating Data
-
Creating a matching policy
-
Using a DS project to match data
Module 11: Using Master Data Services
This module describes how to implement master data services to enforce data integrity at source.Lessons
-
Introduction to Master Data Services
-
Implementing a Master Data Services Model
-
Hierarchies and collections
-
Creating a Master Data Hub
Lab : Implementing Master Data Services
-
Creating a master data services model
-
Using the master data services add-in for Excel
-
Enforcing business rules
-
Loading data into a model
-
Consuming master data services data
Module 12: Extending SQL Server Integration Services (SSIS)
This module describes how to extend SSIS with custom scripts and components.Lessons
-
Using scripting in SSIS
-
Using custom components in SSIS
Lab : Using scripts
Module 13: Deploying and Configuring SSIS Packages
This module describes how to deploy and configure SSIS packages.Lessons
-
Overview of SSIS Deployment
-
Deploying SSIS Projects
-
Planning SSIS Package Execution
Lab : Deploying and Configuring SSIS Packages
-
Creating an SSIS catalog
-
Deploying an SSIS project
-
Creating environments for an SSIS solution
-
Running an SSIS package in SQL server management studio
-
Scheduling SSIS packages with SQL server agent
Module 14: Consuming Data in a Data Warehouse
This module describes how to debug and troubleshoot SSIS packages.Lessons
-
Introduction to Business Intelligence
-
An Introduction to Data Analysis
-
Introduction to reporting
-
Analyzing Data with Azure SQL Data Warehouse
Lab : Using a data warehouse
-
Exploring a reporting services report
-
Exploring a PowerPivot workbook
-
Exploring a power view report