Audience profile
Database professionals who need to fulfill a BI developer role focused on hands-on work by creating BI solutions, including data warehouse implementation, ETL and data cleansing
Database professionals responsible for implementing a data warehouse, developing SSIS packages for data ETL, enforcing data integrity using Microsoft Data Services and cleansing data using Data Quality Services.
Prerequisites
- M20761 Querying Data with Transact SQL.
Course outline
INTRODUCTION TO DATA WAREHOUSING
- Gain an Introduction to Data Warehousing
- Consider Factors Involved in a Data Warehouse Solution
Lab: Exploring a Data Warehouse Solution
PLANNING DATA WAREHOUSE INFRASTRUCTURE
- Review Considerations for Building a Data Warehouse
- Plan Data Warehouse Hardware
Lab: Planning Data Warehouse Infrastructure
DESIGNING AND IMPLEMENTING A DATA WAREHOUSE
- Design Dimension Tables and Fact Tables
- Determine Physical Design for a Data Warehouse
Lab: Implementing a Data Warehouse Schema
COLUMNSTORE INDEXES
- Review Columnstore Indexes
- Create and Work with Columnstore Indexes
Lab: Using Columnstore Indexes
IMPLEMENTING AN AZURE SQL DATA WAREHOUSE
- Understand the Advantages of Azure SQL Data Warehouse
- Implement an Azure SQL Data Warehouse
- Develop an Azure SQL Data Warehouse
- Migrate to an Azure SQL Data Warehouse
- Copy Data with the Azure Data Factory
Lab: Implementing an Azure SQL Data Warehouse
CREATING AN ETL SOLUTION
- Gain an Introduction to ETL with SSIS
- Explore Source Data
- Implement Data Flow
Lab: Implementing Data Flow in an SSIS Package
IMPLEMENTING CONTROL FLOW IN AN SSIS PACKAGE
- Introduce Control Flow
- Create Dynamic Packages
- Adopt Containers
Lab: Implementing Control Flow in an SSIS Package
Lab: Using Transactions and Checkpoints
DEBUGGING & TROUBLESHOOTING SSIS PACKAGES
- Debug an SSIS Package
- Log SSIS Package Events
- Handle Errors in an SSIS Package
Lab: Debugging and Troubleshooting an SSIS Package
IMPLEMENTING A DATA EXTRACTION SOLUTION
- Navigate Incremental ETL
- Extract and Load Modified Data
- Manage Temporal Tables
Lab: Extracting Modified Data
Lab: Loading Incremental Changes
ENFORCING DATA QUALITY
- Apply Data Quality Services to Cleanse Data
- Use Data Quality Services to Match Data
Lab: Cleansing Data
Lab: De-Duplicating Data
USING MASTER DATA SERVICES
- Become Familiar with Master Data Services
- Execute a Master Data Services Model
- Create a Master Data Hub
Lab: Implementing Master Data Services
EXTENDING SQL SERVER INTEGRATION SERVICES (SSIS)
- Utilize Custom Components in SSIS
- Perform Scripting in SSIS
Lab: Using Scripts and Custom Components
DEPLOYING AND CONFIGURING SSIS PACKAGES
- Review SSIS Deployment
- Deploy SSIS Projects
- Plan SSIS Package Execution
Lab: Deploying and Configuring SSIS Packages
CONSUMING DATA IN A DATA WAREHOUSE
- Understand Basic Concepts of Business Intelligence, Reporting and Data Analysis
- Analyze Data with Azure SQL Data Warehouse
Lab: Using Business Intelligence Tools
