20463 Implementing a Data Warehouse with Microsoft® SQL Server
CLASS DATE(s):
9/28/2020 - 10/2/2020
11/30/2020 - 12/4/2020

COURSE LENGTH: 5 Days

COURSE COST: $2495.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print
Register for this course
register

COURSE OVERVIEW

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

Note: This course is designed for customers who are interested in learning SQL Server 2012 or SQL Server 2014. It covers the new features in SQL Server 2014, but also the important capabilities across the SQL Server data platform.

AUDIENCE AND PREREQUISITES

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.

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.

PREREQUISITE COURSESFOLLOW UP COURSES  

*Course Cost listed does not include the cost of courseware. Course is subject to a minimum enrollment. Courses maybe run as a virtual live online instructor led and/or may run as a bootcamp 4 day stay if the minimum enrollment is not met. If you have any questions, please contact us (learn@vtec.org or 207-775-0244).

COURSE TOPICS:


Module 1: Introduction to Data Warehousing
Overview of Data Warehousing
Considerations for a Data Warehouse Solution
Lab : Exploring a Data Warehousing Solution

Module 2: Planning Data Warehouse Infrastructure
Considerations for Data Warehouse Infrastructure
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
Loading Modified data
Lab : Extracting Modified Data
Lab : Loading Incremental Changes

Module 8: 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 9: Using Master Data Services
Master Data Services Concepts
Implementing a Master Data Services Model
Managing Master Data
Creating a Master Data Hub
Lab : Implementing Master Data Services

Module 10: Extending SQL Server Integration Services
Using Scripts in SSIS
Using Custom Components in SSIS
Lab : Using Custom Components and Scripts

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

Module 12: Consuming Data in a Data Warehouse
Introduction to Business Intelligence
Introduction to Reporting
An Introduction to Data Analysis
Lab : Using Business Intelligence Tools

Module 13: Consuming Data in a Data Warehouse
Introduction to Business Intelligence
Enterprise Business Intelligence
Self-Service BI and Big Data