MOC On-Demand: 20767-Implementing a SQL Data Warehouse - On-Demand Course

  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.

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