Microsoft Learning Logo is a registered trademark of Microsoft. All rights reserved.

Implementing a Data Warehouse with Microsoft SQL Server 2014

Schedule

Start End Duration Location Details

Course Details

Implementing a Data Warehouse with Microsoft SQL Server 2014

Course Code: 20463

Duration: 5 days

Prerequisite:

This course requires that you meet the following prerequisites:

  • At least 2 years’ experience of working with relational databases, including:
  • Designing a normalized database.
  • Creating tables and relationships.
  • Querying with Transact-SQL.
  • Some exposure to basic programming constructs (such as looping and branching). An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.

Course Description:

This course describes how to implement a data warehouse platform to support a BI solution. Students will learn 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.

Course Objectives:

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.

Intended 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. 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

Course Outline:

Module 1: Introduction to Data Warehousing

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

Module 2: Data Warehouse Hardware Considerations

•    Considerations for building a Data Warehouse
•    Data Warehouse Reference Architectures and Appliances

Module 3: Designing and Implementing a Data Warehouse

•    Logical Design for a Data Warehouse
•    Physical design for a data warehouse

Module 4: Creating an ETL Solution with SSIS

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

Module 5: Implementing Control Flow in an SSIS Package

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

Module 6: Debugging and Troubleshooting SSIS Packages

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

Module 7: Implementing a Data Extraction Solution

•    Planning Data Extraction
•    Extracting Modified Data

Module 8: Loading Data into a Data Warehouse

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

Module 9: Enforcing Data Quality

•    Introduction to Data Quality
•    Using Data Quality Services to Cleanse Data
•    Using Data Quality Services to Match 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

Module 11: Extending SQL Server Integration Services

•    Using Scripts in SSIS
•    Using Custom Components in SSIS

Module 12: Deploying and Configuring SSIS Packages

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

Module 13: Consuming Data in a Data Warehouse

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