Duration: 5 Days
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.
This course incorporates materials from Implementing a Data Warehouse with Microsoft SQL Server 2012 (MS-10777) and covers the skills and knowledge measured by Exam 70-463. Along with on-the-job experience, this course helps prepare you for the exam.
Proven Impact 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 Will 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
Audience
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
Prerequistes
- 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. 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
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
Course 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
