In this course, you will learn how to implement a data warehouse infrastructure to support information worker analytics using SQL Server 2012. You will discover how to create a data warehouse with SQL Server 2012, implement extract, transform, and load (ETL) with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services (DQS) and SQL Server Master Data Services.

Global Knowledge Exclusive!

Continue developing your skills after class with 24/7 access to our live remote labs for six months, providing you with valuable extra time for hands-on practice.

What You'll Learn

• 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 a SQL Server Integration Services (SSIS) package
• Debug and troubleshoot SSIS packages
• Implement a SSIS solution that supports incremental data warehouse loads and changing data
• Integrate cloud data into a data warehouse ecosystem infrastructure
• Implement data cleansing using Microsoft DQS
• Implement Master Data Services to enforce data integrity at source
• Extend SSIS with custom scripts and components
• Deploy and configure SSIS packages
• How information workers can consume data from the data warehouse

Who Needs to Attend

Database professionals who need to fulfill a BI developer role focused on hands-on work, creating BI solutions included data warehouse implementation, ETL, and data cleansing

Database professionals responsible for implementing a data warehouse, developing SSIS packages for data extraction, loading, transferring, transforming, and enforcing data integrity using Master Data Services, and cleansing data using DQS

Prerequisites

• Knowledge of relational databases
• Basic knowledge of Microsoft Windows operating systems and its core functionality

• Querying Microsoft SQL Server 2012 (M10774)

Follow-On Courses

• Implementing Data Models and Reports with Microsoft SQL Server 2012 (M10778)

Certification Programs and Certificate Tracks

This course is part of the following programs or tracks:

• MCSA: SQL Server 2012

Course Outline

1. Data Warehousing

• Concepts and Architecture Considerations
• Considerations for a Data Warehouse Solution

2. Data Warehouse Hardware Considerations

• Challenges of Building a Data Warehouse
• Reference Architectures
• Appliances

3. Designing and Implementing a Data Warehouse

• Logical Design
• Physical Design

4. Creating an ETL Solution with SSIS

• ETL with SSIS
• Exploring Source Data
• Implementing Data Flow

5. Implementing Control Flow in an SSIS Package

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

6. Debugging and Troubleshooting SSIS Packages

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

7. Implementing an Incremental ETL Process

• Incremental ETL
• Extracting Modified Data
• Loading Modified Data

8. Incorporating Data from the Cloud into a Data Warehouse

• Cloud Data Sources
• SQL Server Database

• Windows Azure Marketplace

9. Enforcing Data Quality

• SQL Server 2012 Data Quality Services (DQS)
• Using DQS to Cleanse Data
• Using DQS to Match Data

10. Using Master Data Services

• Master Data Services Concepts
• Implementing a Master Data Services Model
• Using the Master Data Services Excel Add-In

11. Extending SSIS

• Custom Components in SSIS
• Scripting in SSIS

12. Deploying and Configuring SSIS Packages

• Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution

13. Consuming Data in a Data Warehouse

• Business Intelligence
• Reporting
• Data Analysis

Labs

Lab 1: Explore a Data Warehouse Solution

Lab 2: Implement a Data Warehouse Schema

Lab 3: Implement Data Flow in a SSIS Package

Lab 4A: Implement Control Flow in a SSIS Package

Lab 4B: Use Transactions and Checkpoints

Lab 5: Debug and Troubleshoot a SSIS Package

Lab 6A: Extract Modified Data

Lab 6B: Load Incremental Changes

Lab 7: Use Cloud Data in a Data Warehouse Solution

Lab 8A: Cleanse Data

Lab 8B: De-Duplication Data

Lab 9: Implement Master Data Services

Lab 10: Use Scripts and Custom Components

Lab 11: Deploy and Configure SSIS Packages

Lab 12: Business Intelligence Tools