Implementing a Data Warehouse with Microsoft SQL Server 2014 (20463D)

After completing this course, students will be able to:

• 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

LESSON 1 - INTRODUCTION TO DATA WAREHOUSING

• Overview of Data Warehousing
• Considerations for a Data Warehouse Solution

LESSON 2 - PLANNING DATA WAREHOUSE INFRASTRUCTURE

• Considerations for Data Warehouse Infrastructure
• Planning Data Warehouse Hardware

LESSON 3 - DESIGNING AND IMPLEMENTING A DATA WAREHOUSE

• Data Warehouse Design Overview
• Designing Dimension Tables
• Designing Fact Tables
• Physical Design for a Data Warehouse

LESSON 4 - CREATING AN ETL SOLUTION WITH SSIS

• Introduction to ETL with SSIS
• Exploring Data Sources
• Implementing Data Flow

LESSON 5 - IMPLEMENTING CONTROL FLOW IN AN SSIS PACKAGE

• Introduction to Control Flow
• Creating Dynamic Packages
• Using Containers
• Managing Consistency

LESSON 6 - DEBUGGING AND TROUBLESHOOTING SSIS PACKAGES

• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in an SSIS Package

LESSON 7 - IMPLEMENTING A DATA EXTRACTION SOLUTION

• Planning Data Extraction
• Extracting Modified Data

LESSON 8 - LOADING DATA INTO A DATA WAREHOUSE

• Planning Data Loads
• Using SSIS for Incremental Loads
• Using Transact-SQL Loading Techniques

LESSON 9 - ENFORCING DATA QUALITY

• Introduction to Data Quality
• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Cleanse Data

LESSON 10 - MASTER DATA SERVICES

• Introduction to Master Data Services
• Implementing a Master Data Services Model
• Managing Master Data
• Creating a Master Data Hub

LESSON 11 - EXTENDING SQL SERVER INTEGRATION SERVICES

• Using Scripts in SSIS
• Using Custom Components in SSIS

LESSON 12 - DEPLOYING AND CONFIGURING SSIS PACKAGES

• Overview of SSIS Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution

LESSON 13 - CONSUMING DATA IN A DATA WAREHOUSE

• Introduction to Business Intelligence
• Enterprise Business Intelligence
• Self-Service BI and Big Data

This course is intended for database professionals who need to create and support a data warehousing solution. Primary responsibilities include:

• Implementing a data warehouse
• Developing SSIS packages for data extraction, transformation, and loading
• Enforcing data integrity by using Master Data Services
• Cleansing data by using Data Quality Services