Microsoft Learning Logo is a registered trademark of Microsoft. All rights reserved.
Implementing a Data Warehouse with Microsoft SQL Server 2014
Schedule
Start | End | Duration | Location | Details |
---|
Course Details
Implementing a Data Warehouse with Microsoft SQL Server 2014
Course Code: 20463
Duration: 5 days
Prerequisite:
This course requires that you meet the following prerequisites:
- At least 2 years’ experience of working with relational databases, including:
- Designing a normalized database.
- Creating tables and relationships.
- Querying with Transact-SQL.
- Some exposure to basic programming constructs (such as looping and branching). An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
Course Description:
This course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with Microsoft® SQL Server® 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.
Course Objectives:
After completing this course, students will be able to:
- 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 Control Flow in an SSIS Package.
- Debug and Troubleshoot SSIS packages.
- Implement an ETL solution that supports incremental data extraction.
- Implement an ETL solution that supports incremental data loading.
- Implement data cleansing by using Microsoft Data Quality Services.
- Implement Master Data Services to enforce data integrity.
- Extend SSIS with custom scripts and components.
- Deploy and Configure SSIS packages.
- Describe how BI solutions can consume data from the data warehouse.
Intended Audience:
This course is intended for database professionals who need to fulfil a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing. Primary responsibilities include: ? Implementing a data warehouse.
- Developing SSIS packages for data extraction, transformation, and loading.
- Enforcing data integrity by using Master Data Services.
- Cleansing data by using Data Quality Services
Course Outline:
Module 1: Introduction to Data Warehousing
• Overview of Data Warehousing
• Considerations for a Data Warehouse Solution
Module 2: Data Warehouse Hardware Considerations
• Considerations for building a Data Warehouse
• Data Warehouse Reference Architectures and Appliances
Module 3: Designing and Implementing a Data Warehouse
• Logical Design for a Data Warehouse
• Physical design for a data warehouse
Module 4: Creating an ETL Solution with SSIS
• Introduction to ETL with SSIS
• Exploring Data Sources
• Implementing Data Flow
Module 5: Implementing Control Flow in an SSIS Package
• Introduction to Control Flow
• Creating Dynamic Packages
• Using Containers
• Managing Consistency
Module 6: Debugging and Troubleshooting SSIS Packages
• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in an SSIS Package
Module 7: Implementing a Data Extraction Solution
• Planning Data Extraction
• Extracting Modified Data
Module 8: Loading Data into a Data Warehouse
• Planning Data Loads
• Using SSIS for Incremental Loads
• Using Transact-SQL Loading Techniques
Module 9: Enforcing Data Quality
• Introduction to Data Quality
• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Match Data
Module 10: Master Data Services
• Introduction to Master Data Services
• Implementing a Master Data Services Model
• Managing Master Data
• Creating a Master Data Hub
Module 11: Extending SQL Server Integration Services
• Using Scripts in SSIS
• Using Custom Components in SSIS
Module 12: Deploying and Configuring SSIS Packages
• Overview of SSIS Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution
Module 13: Consuming Data in a Data Warehouse
• Introduction to Business Intelligence
• Enterprise Business Intelligence
• Self-Service BI and Big Data