Toll Free - 877-442-3915

Account | Self-Paced Login

Account | Self-Paced Login | 877-442-3915

Improve your skills while working at home
Online Training options available for all of our courses
Live-Instructor - Online Group Training - On-Demand


20767-Implementing a SQL Data Warehouse  Course

20767-Implementing a SQL Data Warehouse Training Course


Course Description:  The Implementing a SQL Data Warehouse - 20767 course is a 5-day course that is designed to teach students how to Implementing a data warehouse platform to support a BI solution.

The course covers how to create a data warehouse with Microsoft SQL Server 2016 and with Azure SQL Data Warehouse, to Implementing ETL with SQL Server Integration Services. The course also covers how to validate and clean data with SQL Server Data Quality Services and SQL Server Master Data Services.

Topics covered in the course include:

  • Understanding the key elements of a data warehousing solution
  • Understanding the main hardware considerations for building a data warehouse
  • Implementing a logical design for a data warehouse
  • Implementing a physical design for a data warehouse
  • Creating columnstore indexes
  • Implementing an Azure SQL Data Warehouse
  • Understanding the key features of SSIS
  • Implementing a data flow by using SSIS
  • Implementing control flow by using tasks and precedence constraints
  • Creating dynamic packages that include variables and parameters
  • Debugging SSIS packages
  • Understanding the considerations for Implementing an ETL solution
  • Implementing Data Quality Services
  • Implementing a Master Data Services model
  • Describing how you can use custom components to extend SSIS
  • Deploying SSIS projects
  • Describing BI and common BI scenarios

Target Student:This course is designed for IT Professionals who administer and maintain SQL Server databases.

Students should possess the following experience before attending the course:

  • At least 2-years experience working with relational database
  • Designing a normalized database
  • Creating tables and relationships
  • Querying with Transact-SQL
  • Exosure to basic programming concepts (like branching and looping)
  • Understanding of core business priorities like profitability, revenue and financial accounting would be beneficial, but not required.
  • Certification Note:This course covers all the topics needed to adequately prepare for exam 70-761.

    Training Dates
    (Click on the course name below to view course details and full list of class dates)

    20767-Implementing a SQL Data Warehouse
    Locations: Available Nationwide and Online
    Class Date(s): 4/27/20 - 5/1/20
    Course Fee : $2795


    20767-Implementing a SQL Data Warehouse
    Locations: Available Nationwide and Online
    Class Date(s): 6/1/20 - 6/5/20
    Course Fee : $2795


    20767-Implementing a SQL Data Warehouse
    Locations: Available Nationwide and Online
    Class Date(s): 7/6/20 - 7/10/20
    Course Fee : $2795


    20767-Implementing a SQL Data Warehouse
    Locations: Available Nationwide and Online
    Class Date(s): 8/3/20 - 8/7/20
    Course Fee : $2795


    Course Syllabus

    Section 1: Introduction to Data Warehousing
    Describe data warehouse concepts and architecture considerations.

    Topics :
    Overview of Data Warehousing
    Considerations for a Data Warehouse Solution

    Lab :
    Exploring a Data Warehouse Solution

    Section 2: Planning Data Warehouse Infrastructure
    This section describes the main hardware considerations for building a data warehouse.

    Topics :
    Considerations for Building a Data Warehouse
    Data Warehouse Reference Architectures and Appliances

    Lab :
    Planning Data Warehouse Infrastructure

    Section 3: Designing and Implementing a Data Warehouse
    This section describes how you go about designing and implementing a schema for a data warehouse.

    Topics :
    Logical Design for a Data Warehouse
    Physical Design for a Data Warehouse

    Lab :
    Implementing a Data Warehouse Schema

    Section 4: Columnstore Indexes
    This section introduces Columnstore Indexes.

    Topics :
    Introduction to Columnstore Indexes
    Creating Columnstore Indexes
    Working with Columnstore Indexes

    Lab :
    Using Columnstore Indexes

    Section 5: Implementing an Azure SQL Data Warehouse
    This section describes Azure SQL Data Warehouses and how to implement them.

    Topics :
    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

    Section 6: Creating an ETL Solution
    At the end of This section you will be able to implement data flow in a SSIS package.

    Topics :
    Introduction to ETL with SSIS
    Exploring Source Data
    Implementing Data Flow

    Lab :
    Implementing Data Flow in an SSIS Package

    Section 7: Implementing Control Flow in an SSIS Package
    This section describes implementing control flow in an SSIS package.

    Topics :
    Introduction to Control Flow
    Creating Dynamic Packages
    Using Containers

    Lab :
    Implementing Control Flow in an SSIS Package

    Lab :
    Using Transactions and Checkpoints

    Section 8: Debugging and Troubleshooting SSIS Packages
    This section describes how to debug and troubleshoot SSIS packages.

    Topics :
    Debugging an SSIS Package
    Logging SSIS Package Events
    Handling Errors in an SSIS Package

    Lab :
    Debugging and Troubleshooting an SSIS Package

    Section 9: Implementing an Incremental ETL Process
    This section describes how to implement an SSIS solution that supports incremental DW loads and changing data.

    Topics :
    Introduction to Incremental ETL
    Extracting Modified Data
    Temporal Tables

    Lab :
    Extracting Modified Data

    Lab :
    Loading Incremental Changes

    Section 10: Enforcing Data Quality
    This section describes how to implement data cleansing by using Microsoft Data Quality services.

    Topics :
    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

    Section 11: Using Master Data Services
    This section describes how to implement master data services to enforce data integrity at source.

    Topics :
    Master Data Services Concepts
    Implementing a Master Data Services Model
    Managing Master Data
    Creating a Master Data Hub

    Lab :
    Implementing Master Data Services

    Section 12: Extending SQL Server Integration Services (SSIS)
    This section describes how to extend SSIS with custom scripts and components.

    Topics :
    Using Custom Components in SSIS
    Using Scripting in SSIS

    Lab :
    Using Scripts and Custom Components

    Section 13: Deploying and Configuring SSIS Packages
    This section describes how to deploy and configure SSIS packages.

    Topics :
    Overview of SSIS Deployment
    Deploying SSIS Projects
    Planning SSIS Package Execution

    Lab :
    Deploying and Configuring SSIS Packages

    Section 14: Consuming Data in a Data Warehouse
    This section describes how to debug and troubleshoot SSIS packages.

    Topics :
    Introduction to Business Intelligence
    Introduction to Reporting
    An Introduction to Data Analysis
    Analyzing Data with Azure SQL Data Warehouse

    Lab :
    Using Business Intelligence Tools

    SQL Server On-Site Group Training
    Arrange a private SQL Server training class for your team with one of our Certified Instructors. Private instruction for your team members at a lower per-student price than attending the public courses. Learn More or complete the quote request form below.



SQL Server On-Demand Training

"The Instructor made class truly interesting and put everything into real world terms."
- Meredith T(Jersey City, NJ)


Business Computer Skills Microsoft Partner

Business Computer Skills BBB Profile