Welcome to ONLC Training Centers
  1. Home
  2. Courses
  3. SQL Server
  4. SQL Server Integration Services (55321/20767) - 20767

SQL Server Integration Services (55321/20767) - 20767 Course

Need a price quote?

Follow the link to our self-service price quote form to generate an email with a price quote.

Need a class for a group?

We can deliver this class for your group. Follow the link to request more information.

Email Alert

Receive an email when this class is available as "Ready to Run" or "Early Notice" status.

Train from your home or office

If you have high-speed internet and a computer you can likely take this class from your home or office.


  1. Home
  2. Courses
  3. SQL Server
  4. SQL Server Integration Services (55321/20767) - 20767

SQL Server Integration Services (55321/20767) - 20767 Course

 

Overview

This 5-day instructor led course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with Microsoft® SQL Server® 2016 and with Azure SQL Data Warehouse, to implement ETL with SQL Server Integration Services, and to validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

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.

Prerequisites

In addition to their professional experience, students who attend this training should already have the following technical knowledge:
At least 2 years’ experience of working with relational databases, including:
Designing a normalized database.
Creating tables and relationships.
Querying with Transact-SQL.
Some exposure to basic programming constructs (such as looping and branching).
An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.

At course completion

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

Course Outline

Module 1: Introduction to Data Warehousing

  • Describe data warehouse concepts and architecture considerations.
  • Lessons
  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution
  • 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
  • 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

  • This module describes how you go about designing and implementing a schema for a data warehouse.
  • Lessons
  • Logical Design for a Data Warehouse
  • Physical Design for a Data Warehouse
  • Lab : Implementing a Data Warehouse Schema

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

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
  • 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
  • Introduction to ETL with SSIS
  • Exploring Source Data
  • Implementing 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
  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • 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
  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package
  • Lab : Debugging and Troubleshooting an SSIS Package

Module 9: Implementing an Incremental ETL Process

  • 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
  • Temporal Tables
  • Lab : Extracting Modified Data
  • Lab : Loading Incremental Changes

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
  • 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
  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Managing Master Data
  • Creating a Master Data Hub
  • 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
  • Using Custom Components in SSIS
  • Using Scripting in SSIS
  • Lab : Using Scripts and Custom Components

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

Module 14: Consuming Data in a Data Warehouse

  • This module describes how to debug and troubleshoot SSIS packages.
  • Lessons
  • Introduction to Business Intelligence
  • Introduction to Reporting
  • An Introduction to Data Analysis
  • Analyzing Data with Azure SQL Data Warehouse
  • Lab : Using Business Intelligence Tools

 

View outline in Word

A20767

Attend hands-on, instructor-led SQL Server Integration Services (55321/20767) - 20767 training classes at ONLC's nationwide locations. Not near one of our locations? Attend these same live classes from your home/office PC via our Remote Classroom Instruction (RCI) technology.

For additional training options, check out our list of SQL Server Courses and select the one that's right for you.

GENERAL INFO

Class Format
Class Policies
Student Reviews

HAVE QUESTIONS?
First Name

Last Name

Company

Phone

Email

Location

Question/Comment


ONLC TRAINING CENTERS
800-288-8221
www.onlc.com
Our Classes
Why Register with ONLC
Other Information
For Companies
For Professionals
For Government
Live Classes from Home or Office
About ONLC
Other Details
Connect