Microsoft 20463 – Implementing a Data Warehouse with Microsoft SQL Server

In this course you will learn how to implmenet a data warehouse platform in order to support  a business intelligence solution. You will also discuss how to create a data warehouse, implement extract, transform and load with SQL Server Integraion Services and validate and cleanse data with SQL Serve Data Quality Services and SQL Server Master Data Services.

Who needs to attend

Who needs to attend?
This course is aimed at database professionals that need to fill in the role of a business intelligence developer role responsible for creating business intelligence solutions, data warehouse implementation, ETL and data cleansing and database professionals responsible for implementing a data warehouse, developing SSIS packages for data extractions, loading, transferring, transforming and enforcing data integrity using MDS and cleansing data using DQS.

what you will learn

What you will learn

Upon completion you will know how to:

  • Data warehouse concepts and architecture considerations
  • Select an appropriate hardware platform for a data warehouse
  • Design and implement a data warehouse
  • Implement data flow and control flow in a SSIS package
  • Debug and troubleshoot SSIS packages
  • Implement a SSIS solution that supports incremental data warehouse loads and extracting data
  • Implement data cleansing using Microsoft DQS
  • Implement Master Data Services (MDS) to enforce data integrity
  • Extend SSIS with custom scripts and components
  • Deploy and configure SSIS packages
  • How Business Intelligence solutions consume data in a data warehouse


Students need to have:

  • Minimum two years experience working with relational databases, including designing a normalized database, creating tables and relationships
  • Basic programming constructs, including looping and branching
  • Focus on key business priorities, such as revenue, profitability, and financial account
Course outline

Course Outline



  • Module 1: Introduction to Data Warehousing
  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution
  • Lab : Exploring a Data Warehousing Solution
  • Module 2: Data Warehouse Hardware Considerations
  • Considerations for building a Data Warehouse
  • Data Warehouse Reference Architectures and Appliances
  • Lab : Planning Data Warehouse Infrastructure
  • Module 3: Designing and Implementing a Data Warehouse
  • Logical Design for a Data Warehouse
  • Physical design for a data warehouse
  • Lab : Implementing a Data Warehouse Schema
  • Module 4: Creating an ETL Solution with SSIS
  • Introduction to ETL with SSIS
  • Exploring Data Sources
  • Implementing Data Flow
  • Lab : Implementing Data Flow in an SSIS Package
  • Module 5: Implementing Control Flow in an SSIS Package
  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • Managing Consistency
  • Lab : Implementing Control Flow in an SSIS Package
  • Lab : Using Transactions and Checkpoints
  • Module 6: Debugging and Troubleshooting SSIS Packages
  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package
  • Lab : Debugging and Troubleshooting an SSIS Package
  • Module 7: Implementing an Incremental ETL Process
  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading Modified data
  • Lab : Extracting Modified DataLab : Loading Incremental Changes
  • Module 8: Enforcing Data Quality
  • Introduction to Data Quality
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Match data
  • Lab : Cleansing DataLab : De-duplicating data
  • Module 9: Using Master Data Services
  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Managing Master Data
  • Creating a Master Data Hub
  • Lab : Implementing Master Data Services
  • Module 10: Extending SQL Server Integration Services
  • Using Scripts in SSIS
  • Using Custom Components in SSIS
  • Lab : Using Custom Components and Scripts
  • Module 11: Deploying and Configuring SSIS Packages
  • Overview of SSIS Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution
  • Lab : Deploying and Configuring SSIS Packages
  • Module 12: Consuming Data in a Data Warehouse
  • Introduction to Business Intelligence
  • Introduction to Reporting
  • An Introduction to Data Analysis
  • Lab : Using Business Intelligence Tools

Follow on
There are no follow-ons for this course.

Certification programs

MCSA: SQL Server 2012