Implementing a SQL 2016 Data Warehouse (4 days)

Overview

This instructor led course describes how to implement a data warehouse platform to support a BI solution. Delegates will learn how to create a data warehouse with  Microsoft® SQL Server® 2016 to implement ETL solutions and to validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.

Target Audience

This course is intended for 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.

  • Working knowledge of Transact-SQL (ability to write Transact-SQL queries)
  • Working knowledge of database administration and maintenance.
  • Core Windows Server skills.
  • Writing Queries Using Microsoft® SQL Server® 2012/2014 Transact-SQL.
  • Describe data warehouse concepts and architecture considerations.
  • Select an appropriate hardware platform for a data warehouse.
  • Design and implement a data warehouse.
  • Implement Data Flow in an SSIS Package.
  • Implement Control Flow in an SSIS Package.
  • Debug and Troubleshoot SSIS packages.
  • Implement an SSIS solution that supports incremental data warehouse loads and changing data.
  • Integrate cloud data into a data warehouse ecosystem infrastructure.
  • Implement data cleansing by using Microsoft Data Quality Services.
  • Implement Master Data Services to enforce data integrity.
  • Extend SSIS with custom scripts and components.
  • Deploy and Configure SSIS packages.
  • Describe how information workers can consume data from the data warehouse.
Our courses run from 9.30 am to approximately 4.30pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with use you get the following:
  • A full colour training manual.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch in our onsite cafeteria.
  • Refreshments though out the day.
  • Training in fully air conditioned facilities from an experienced Excel instructor.
  • Module Overview
  • 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 Overview
  • Considerations for data warehouse infrastructure.
  • Planning data warehouse hardware.
  • Lab: Planning Data Warehouse Infrastructure
  • Module Overview
  • Designing dimension tables
  • Designing fact tables
  • Physical Design for a Data Warehouse
  • Lab: Implementing a Data Warehouse Schema
  • Module Overview
  • Introduction to Columnstore Indexes
  • Creating Columnstore Indexes
  • Working with Columnstore Indexes
  • Lab: Using Columnstore Indexes
  • Module Overview
  • Introduction to ETL with SSIS
  • Exploring Source Data
  • Implementing Data Flow
  • Lab: Implementing Data Flow in an SSIS Package
  • Module Overview
  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • Managing consistency.
  • Lab: Implementing Control Flow in an SSIS Package
  • Lab: Using Transactions and Checkpoints
  • Module Overview
  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package
  • Lab: Debugging and Troubleshooting an SSIS Package
  • Module Overview
  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading modified data
  • Temporal Tables
  • Lab: Extracting Modified Data
  • Lab: Loading a data warehouse
  • Module Overview
  • 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 Overview
  • Introduction to Master Data Services
  • Implementing a Master Data Services Model
  • Hierarchies and collections
  • Creating a Master Data Hub
  • Lab: Implementing Master Data Services

 

  • Module Overview
  • Using scripting in SSIS
  • Using custom components in SSIS
  • Lab: Using scripts
  • Module Overview
  • Overview of SSIS Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution
  • Lab: Deploying and Configuring SSIS Packages
  • Module Overview
  • Introduction to Business Intelligence
  • An Introduction to Data Analysis
  • Introduction to reporting
  • Analyzing Data with Azure SQL Data Warehouse
  • Lab: Using a data warehouse