$1,445.00 CAD

Get online access to:

  • Microsoft Official Course
  • Microsoft Labs Online
  • Official Digital Courseware
  • 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.
  • Digital edition of the Microsoft Official Curriculum (DMOC) manual for reference throughout your course. All DMOC come with fresh editions so your courseware will always be up to date.
  • 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.

 

Prerequisites

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.

 

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