OneSource Professional Training Solutions, Inc. presents
 

Implementing a Data Warehouse with Microsoft SQL Server 2012 On-Site Training

Implementing a Data Warehouse with Microsoft SQL Server 2012

Course Description/Agenda

In this course, you will learn how to implement a business intelligence (BI) platform to support information worker analytics using the prerelease version of the SQL Server 2012 software in the virtual machines for the labs. 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. Many of the labs are SQL Azure enabled.

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

Course Prerequisites

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

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. Design and Implement a Schema for a Data Warehouse

• 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 in a Data Warehouse

• Cloud Data Sources
• SQL Server Azure
• Azure Data Market

9. Enforcing Data Quality

• Data Cleansing
• 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

• Using Excel to Analyze Data in a Data Warehouse
• PowerPivot
• Crescent

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: Use a Data Warehouse

More Seminar Information

OneSource Professional Training Solutions, Inc. Delivery Method  On-Site Training Also Available As  Seminar