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: Columnstore 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
- Using a script task
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
