Microsoft 70-463: Implementing a Data Warehouse with SQL Server 2012

Description

This course is specifically for participants to measure their knowledge and skills on making the appropriate job role decisions around implementing a Data Warehouse with Microsoft SQL Server 2012.

This course is designed for ETL and Data Warehouse Developers who most likely focus on hands-on work creating business intelligence (BI) solutions, including data cleansing, Extract Transform Load (ETL), and Data Warehouse implementation.

Primary responsibilities may include:

• Mastering data using Master Data Services
• Cleansing data using Data Quality Services
• ETL Package development for data loading/transfer/transformation
• Data Synchronization
• One-time data or application migration
• Ad-hoc data manipulations, transformations
• Scheduling ETL jobs
• Managing, configuring and deploying SQL Server Integration Services (SSIS) packages
• This training course on implementing a Data Warehouse with Microsoft SQL Server 2012 prepares participants for the Microsoft Exam 70-463.

This course will provide all the skills and knowledge for the following areas:

• Designing and implementing dimensions
• Designing and implementing fact tables
• Defining connection managers
• Designing and implementing data flow
• Managing SSIS package execution
• Implementing script tasks in SSIS
• Designing and implementing control flow
• Implementing package logic using SSIS variables and parameters
• Implementing data load options
• Implementing script components in SSIS
• Troubleshooting data integration issues
• Installing and maintaining SSIS components
• Implementing auditing, logging, and event handling
• Deploying SSIS solutions
• Configuring SSIS security settings
• Installing and maintaining Data Quality Services
• Implementing master data management solutions
• Creating a data quality project to clean data

Syllabus:

Module 1

• Introduction to Data Warehousing
• OLTP vs. OLAP
• Data Warehouse
• Data Warehouse Architecture
• Creating a Data Warehouse
• Presenting OLAP Data
• OLTP and OLAP Comparison Demonstration
• Exam Scenarios
• Module Review

Module 2

• Designing a Data Warehouse
• Data Modeling
• Data Warehouse Schemas
• Time Dimensions
• Data Modeling Considerations
• Implementing a Data Model
• Logical Model
• Physical Model
• Dimensions and Facts
• Exam Scenarios
• Module Review

Module 3

• ETL with SSIS
• Introduction to SSIS
• Upgrading SSIS Packages
• SSIS Tools
• Data Flow Tasks
• Data Sources
• Transformations
• Utilizing SQL Server Data Tools
• Data Flow Task
• Exam Scenarios
• Module Review

Module 4

• SSIS Control Flow
• Control Flow Tasks
• Integration Services Variables
• Expressions
• Precedence
• Containers and Annotation
• Transactions
• Checkpoints
• Control Flow Tasks and Precedence
• Exam Scenarios
• Module Review

Module 5

• Troubleshooting Packages
• Overview of Troubleshooting
• Debugging
• Breakpoints
• Error Handling
• SSIS Log Providers
• Custom Logging
• Troubleshooting and Logging
• Exam Scenarios
• Module Review

Module 6

• Advanced Packages
• Maintaining a Data Warehouse
• Change Data Capture
• Slowly Changing Dimensions
• Script Components
• Incremental Updates
• Exam Scenarios
• Module Review

Module 7

• Data Quality Services
• Introduction to Data Quality Services
• Data Quality Services Components
• Working with Data Quality Services
• Exam Scenarios
• Module Review

Module 8

• Master Data Services
• Introduction to Master Data Services
• MDS vs. DQS
• How MDS Works
• Exam Scenarios
• Module Review

Module 9

• Deployment and Security
• Deployment Options
• Package Deployment
• Securing Packages
• Deploying SSIS Packages and Projects
• Exam Scenarios
• Module Review
• Program Review

Course Delivery:    Online