Introduzione

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

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. 

Obiettivi del corso

After completing this course, students will be able to:

Describe the key elements of a data warehousing solution

Describe the main hardware considerations for building a data warehouse

Implement a logical design for a data warehouse 

Implement a physical design for a data warehouse

Create columnstore indexes

Implementing an Azure SQL Data Warehouse

Describe the key features of SSIS

Implement a data flow by using SSIS

Implement control flow by using tasks and precedence constraints

Create dynamic packages that include variables and parameters

Debug SSIS packages

Describe the considerations for implement an ETL solution

Implement Data Quality Services

Implement a Master Data Services model

Describe how you can use custom components to extend SSIS

Deploy SSIS projects

Describe BI and common BI scenarios

Prerequisiti

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.

Struttura del Corso

MODULE 1: Introduction to Data Warehousing

This module describes data warehouse concepts and architecture consideration.

Lessons

After completing this module, you will be able to:

 

Describe the key elements of a data warehousing solution

Describe the key considerations for a data warehousing solution

Exploring data sources

Exploring an ETL process

Exploring a data warehouse

 

Lab : Exploring a Data Warehouse Solution

MODULE 2: Planning Data Warehouse Infrastructure

This module describes the main hardware considerations for building a data warehouse.

Lessons

After completing this module, you will be able to:

 

Describe the main hardware considerations for building a data warehouse

Explain how to use reference architectures and data warehouse appliances to create a data warehouse

 

Lab : Planning Data Warehouse Infrastructure

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

After completing this module, you will be able to:

 

Implement a logical design for a Data Warehouse

Designing dimension tables

Designing fact tables

Implement a Physical Design for a Data Warehouse

 

Lab : Implementing a Data Warehouse Schema

MODULE 4: Columnstore Indexes

This module introduces Columnstore Indexes.

Lessons

After completing this module, you will be able to:

 

Create a Columnstore index on the FactProductInventory table

Create a Columnstore index on the FactInternetSales table

Create a memory optimized Columnstore table

Work with Columnstore Indexes

 

Lab : Using Columnstore Indexes

MODULE 5: Implementing an Azure SQL Data Warehouse

This module describes Azure SQL Data Warehouses and how to implement them.

Lessons

After completing this module, you will be able to:

 

Describe the advantages of Azure SQL Data Warehouse

Implement an Azure SQL Data Warehouse

Develop an Azure SQL Data Warehouse

Migrate to an Azure SQ Data Warehouse

Copy data with the Azure data factory

Plan for migrating to Azure SQL Data Warehouse

 

Lab : Implementing an Azure SQL Data Warehouse

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

After completing this module, you will be able to:

 

Describe ETL with SSIS

Explore Source Data

Transferring data by using a data row task

Using transformation components in a data row

Implement a Data Flow

 

Lab : Implementing Data Flow in an SSIS Package

MODULE 7: Implementing Control Flow in an SSIS Package

This module describes implementing control flow in an SSIS package.

Lessons

After completing this module, you will be able to:

 

Describe Control Flow

Create Dynamic Packages

Use Containers

Managing consistency

Using tasks and precedence in a control flow

Using variables and parameters

Using containers

Using transactions

Using checkpoints

 

Lab : Implementing Control Flow in an SSIS Package

Lab : Using Transactions and Checkpoints

MODULE 8: Debugging and Troubleshooting SSIS Packages

This module describes how to debug and troubleshoot SSIS packages.

Lessons

After completing this module, you will be able to:

 

Debug an SSIS package

Log SSIS package events

Implementing an event handler

Handle errors in an SSIS package

 

Lab : Debugging and Troubleshooting an SSIS Package

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

After completing this module, you will be able to:

 

Describe incremental ETL

Extract modified data

Load modified data

Describe temporal tables

Using a datetime column to incrementally extract data

Using change data capture

Using the CDC control task

Using change tracking

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

 

Lab : Extracting Modified Data

Lab : Loading a data warehouse

MODULE 10: Enforcing Data Quality

This module describes how to implement data cleansing by using Microsoft Data Quality services.

Lessons

After completing this module, you will be able to:

 

Describe Data Quality Services

Cleanse data using data Quality Services

Match data using data Quality Services

De-duplicate data using data Quality Services

Create a DQS knowledge base

Use a DQS project to cleanse data

Use DQS in an SSIS package

Creating a matching policy

Using a DS project to match data

 

Lab : Cleansing Data

Lab : De-duplicating Data

MODULE 11: Using Master Data Services

This module describes how to implement master data services to enforce data integrity at source.

Lessons

After completing this module, you will be able to:

 

Describe the key concepts of Master Data Services

Implement a Master Data Service model

Manage Master Data

Create a Master Data Hub

Use the master data services add-in for Excel

Enforce business rules

Load data into a model

Consume master Data Service data

 

Lab : Implementing Master Data Services

MODULE 12: Extending SQL Server Integration Services (SSIS)

This module describes how to extend SSIS with custom scripts and components.

Lessons

After completing this module, you will be able to:

 

Use custom components in SSIS

Use scripting in SSIS

 

Lab : Using scripts

MODULE 13: Deploying and Configuring SSIS Packages

This module describes how to deploy and configure SSIS packages.

Lessons

After completing this module, you will be able to:

 

Describe an SSIS deployment

Deploy an SSIS package

Create an SSIS catalog

Deploy an SSIS project

Create environments for an SSIS solution

Run an SSIS package in SQL server management studio

Schedule SSIS packages with SQL server agent

Plan SSIS package execution

 

Lab : Deploying and Configuring SSIS Packages

MODULE 14: Consuming Data in a Data Warehouse

This module describes how to debug and troubleshoot SSIS packages.

Lessons

After completing this module, you will be able to:

 

Describe at a high level business intelligence

Show an understanding of reporting

Show an understanding of data analysis

Analyze data with Azure SQL data warehouse

Explore a reporting services report

Explore a PowerPivot workbook

Explore a power view report

 

Lab : Using a data warehouse