Implementing a Data Warehouse with Microsoft SQL Server 2012/2014

Course Code: 5040

Overview
Schedule Classes
What you'll learn
Outline
Prerequisites
Who should attend

Overview

This five day course is intended for database professionals who need to fulfill a business intelligence developer role. The course helps participants learn how to go about creating BI solutions including data warehouse implementation, ETL, and data cleansing. The course covers how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services and validate and cleanse data with SQL Server data quality services and SQL Server master data services. The course is a good fit for participants who are interested in learning SQL Server 2012 as well as SQL Server 2014.

Schedule Classes

Looking for more sessions of this class?

Course Delivery

This course is available in the following formats:

Live Classroom
Duration: 5 days

Live Virtual Classroom
Duration: 5 days

What You'll learn

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 ETL solution that supports incremental data extraction
Implement an ETL solution that supports incremental data loading
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 BI solutions can consume data from the data warehouse

Outline

Module 1: Introduction to data warehousing

Overview of Data Warehousing
Considerations for a Data Warehouse Solution
Lab: Exploring a Data Warehousing Solution

Module 2: Data warehouse hardware considerations

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

Logical Design for a Data Warehouse
Physical design for a data warehouse
Lab: Implementing a Data Warehouse Schema

Module 4: Creating an ETL solution with SSIS

Introduction to ETL with SSIS
Exploring Data Sources
Implementing Data Flow
Lab: Implementing Data Flow in an SSIS Package

Module 5: Implementing control flow in an SSIS package

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 6: Debugging and troubleshooting SSIS packages

Debugging an SSIS Package
Logging SSIS Package Events
Handling Errors in an SSIS Package
Lab: Debugging and Troubleshooting an SSIS Package

Module 7: Implementing a data extraction solution

Planning Data Extraction
Extracting Modified Data
Lab: Extracting Modified Data

Module 8: Loading data into a data warehouse

Planning Data Loads
Using SSIS for Incremental Loads
Using Transact-SQL Loading Techniques
Lab: Loading a Data Warehouse

Module 9: Enforcing data quality

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 10: Master data services

Introduction to Master Data Services
Implementing a Master Data Services Model
Managing Master Data
Creating a Master Data Hub
Lab: Implementing Master Data Services

Module 11: Extending SQL Server integration services

Using Scripts in SSIS
Using Custom Components in SSIS
Lab: Using Custom Scripts

Module 12: Deploying and configuring SSIS packages

Overview of SSIS Deployment
Deploying SSIS Projects
Planning SSIS Package Execution
Lab: Deploying and Configuring SSIS Packages

Module 13: Consuming data in a data warehouse

Introduction to Business Intelligence
Enterprise Business Intelligence
Self-Service BI and Big Data
Lab: Using a data warehouse

Prerequisites

Participants need to have at least two years of experience working with relational databases, including, designing a normalized database, creating tables and relationships, querying with Transact-SQL and some exposure to basic programming contructs, such as, looping and branching. A basic understanding of key business priorities, such as, revenue, profitability and financial accounting is recommended.