10777 Implementing a Data Warehouse with Microsoft SQL Server 2012

Learning Objectives

• 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 Data Flow in an SSIS Package.
• Debug and Troubleshoot SSIS packages.
• Implement an SSIS solution that supports incremental DW loads and changing data.
• Integrate cloud data into a data warehouse ecosystem infrastructure.
• Implement data cleansing by using Microsoft Data Quality Services.
• Implement Master Data Services to enforce data integrity at source.
• Extend SSIS with custom scripts and components.
• Deploy and Configure SSIS packages.
• Describe how information workers can consume data from the data warehouse.

Course Outline

• Module 1: Introduction to Data Warehousing

1. Describe data warehouse concepts and architecture considerations
2. Considerations for a Data Warehouse Solution

• Module 2: Data Warehouse Hardware Considerations

1. The Challenges of Building a Data Warehouse
2. Data Warehouse Reference Architectures
3. Data Warehouse Appliances

• Module 3: Designing and Implementing a Data Warehouse

1. Logical Design for a Data Warehouse
2. Physical Design for a Data Warehouse

• Module 4: Design and implement a schema for a data warehouse

1. Introduction to ETL with SSIS
2. Exploring Source Data
3. Implementing Data Flow

• Module 5: Implementing Control Flow in an SSIS Package

1. Introduction to Control Flow
2. Creating Dynamic Packages
3. Using Containers
4. Managing Consistency

• Module 6: Debugging and Troubleshooting SSIS Packages

1. Debugging an SSIS Package
2. Logging SSIS Package Events
3. Handling Errors in an SSIS Package

• Module 7: Implementing an Incremental ETL Process

1. Introduction to Incremental ETL
2. Extracting Modified Data
3. Loading Modified Data

• Module 8: Incorporating Data from the Cloud in a Data Warehouse

1. Overview of Cloud Data Sources
2. SQL Server Azure
3. Azure Data Market

• Module 9: Enforcing Data Quality

1. Introduction to Data Cleansing
2. Using Data Quality Services to Cleanse Data
3. Using Data Quality Services to Match Data

• Module 10: Using Master Data Services

1. Master Data Services Concepts
2. Implementing a Master Data Services Model
3. Using the Master Data Services Excel Add-in

• Module 11: Extending SSIS

1. Using Custom Components in SSIS
2. Using Scripting in SSIS

• Module 12: Deploying and Configuring SSIS Packages

1. Overview of Deployment
2. Deploying SSIS Projects
3. Planning SSIS Package Execution

• Module 13: Consuming Data in a Data Warehouse

1. Using Excel to Analyze Data in a data Warehouse.
2. An Introduction to PowerPivot
3. An Introduction to Crescent