$1,245.00 CAD

Get online access to:

  • Microsoft Official Course
  • Microsoft Labs Online
  • Knowledge Assessments

Jump to Shopping Cart Continue shopping

20767 MOD: Implementing a SQL Data Warehouse


About this On Demand Course

The Microsoft on-demand product is an integrated on-line training experience that includes video, labs, exercises, text and knowledge checks. Attendees experience all of this through an on-demand course player.

What’s included?

  • Access to the official Microsoft Video on Demand Course for 90 days from the point of first access, allowing you to start and stop when you need to.
  • Lab access for 3 months from start of access.
  • Mentoring Support (24/7 by chat, email or phone) for the duration of your subscription

The content is based on the same official courseware we use in our instructor-led training, and videos feature engaging experts hand-selected by Microsoft. Unlike other on-demand offerings that offer simulated labs, MOC On-Demand gives you a live, real-time environment for hands-on training.


About this course

This course provides students with the knowledge and skills to provision a Microsoft SQL Server 2016 database. The course covers SQL Server 2016 provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.



In addition to their professional experience, students who attend this training should already have the following technical knowledge:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.
  • Some experience with database design.


Audience profile

The primary audience for this course are database professionals who need to fulfil a Business Intelligence Developer role.  They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing. 


At course completion

  • After completing this course, students will be able to:

  • Provision a Database Server.

  • Upgrade SQL Server.

  • Configure SQL Server.

  • Manage Databases and Files (shared).


Course Outline


Module 1: Introduction to Data Warehousing

This module describes data warehouse concepts and architecture consideration.



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.



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.



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.



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.



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.



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.



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.



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.



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.



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.



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.



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.



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.



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




Today only!


Sign up today at Global Knowledge and save 50% off eligible courses!


Select your course here >

Don’t miss out!

newsletter sign-upSign up for IT training news on upcoming webinars, free resources, new courses, offers and more.