Overview
This 5-day course describes how to implement a data warehouse platform to support a business intelligence (BI) solution. Participants will learn how to create a data warehouse with Microsoft SQL Server, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services.
Prerequisites
Participants who wish to take up this course should 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
Who Should Attend?
This course is intended for database professionals who need to fulfill a Business Intelligence Developer role.
Course Outline
- Overview of Data Warehousing
- Considerations for a Data Warehouse Solution
Lab: Exploring a Data Warehousing Solution
- Considerations for Data Warehouse Infrastructure
- Data Warehouse Reference Architectures and Appliances
Lab: Planning Data Warehouse Infrastructure
- Logical design for a data warehouse
- Physical design for a data warehouse
Lab: Implementing a Data Warehouse Schema
- Introduction to ETL with SSIS
- Exploring Data Sources
- Implementing Data Flow
Lab: Implementing Data Flow in an SSIS Package
- Introduction to Control Flow
- Creating Dynamic Packages
- Using Containers
- Managing Consistency
Lab: Implementing Control Flow in an SSIS Package
Lab: Using Transactions and Checkpoints
- Debugging an SSIS Package
- Logging SSIS Package Events
- Handling Errors in an SSIS Package
Lab: Debugging and Troubleshooting an SSIS Package
- Introduction to Incremental ETL
- Extracting Modified Data
- Loading Modified Data
Lab: Extracting Modified Data
Lab: Loading Incremental Changes
- Introduction to Data Quality
- Using Data Quality Services to Cleanse Data
- Using Data Quality Services to Match Data
Lab: Cleansing Data
Lab: De-duplicating data
- Master Data Services Concepts
- Implementing a Master Data Services Model
- Managing Master Data
- Creating a Master Data Hub
Lab: Implementing Master Data Services
- Using Scripts in SSIS
- Using Custom Components in SSIS
Lab: Using Custom Components and Scripts
- Overview of SSIS Deployment
- Deploying SSIS Projects
- Planning SSIS Package Execution
Lab: Deploying and Configuring SSIS Packages
- Introduction to Business Intelligence
- Introduction to Reporting
- An Introduction to Data Analysis
Lab: Using Business Intelligence Tools
