M20767 Implementing a SQL Data Warehouse
This 5-day instructor led course provides students how to create a data warehouse, how to implement extract, transform, and load (ETL) with SQL Server Integration Services (SSIS), and how to validate and cleanse data with Data Quality Services (DQS) and Master Data Services.
Accredited course for Continuing Education of Pedagogical Staff
Course length: 5
List price: 35 000 CZK (without VAT)
This course has no dates set. If you are interested in setting a new one, please contact
PDF to download
Expand allCollapse all
Students will be able to
- Describe the key elements of a data warehousing solution.
- Describe the main hardware considerations for building a data warehouse.
- Implement a logical design for a data warehouse.
- Implement a physical design for a data warehouse.
- Create columnstore indexes.
- Implementing an Azure SQL Data Warehouse.
- Describe the key features of SSIS.
- Implement a data flow by using SSIS.
- Implement control flow by using tasks and precedence constraints.
- Create dynamic packages that include variables and parameters.
- Debug SSIS packages.
- Describe the considerations for implement an ETL solution.
- Implement Data Quality Services.
- Implement a Master Data Services model.
- Describe how you can use custom components to extend SSIS.
- Deploy SSIS projects.
- Describe BI and common BI scenarios.
- Basic knowledge of SQL Server and databases.
- Basic knowledge of the Microsoft Windows operating system.
This course is intended for
- This course is intended for end users, Database Administrators, Database Developers, and BI professionals.
All participants will get original Microsoft student materials.
Classrooms are equipped with high-performance computers with Internet access and the possibility of wireless connection.
Module 1: Introduction to Data Warehousing
- Lesson 1: Overview of Data Warehousing
- Lesson 2: Considerations for a Data Warehouse Solution
- Lab: Exploring a Data Warehousing Solution
Module 2: Planning Data Warehouse Infrastructure
- Lesson 1: Considerations for Data Warehouse Infrastructure
- Lesson 2: Planning Data Warehouse Hardware
- Lab: Planning Data Warehouse Infrastructure
Module 3: Designing and Implementing a Data Warehouse
- Lesson 1: Data Warehouse Design Overview
- Lesson 2: Designing Dimension Tables
- Lesson 3: Designing Fact Tables
- Lesson 4: Physical Design for a Data Warehouse
- Lab: Implementing a Data Warehouse
Module 4: Columnstore Indexes
- Lesson 1: Introduction to Columnstore Indexes
- Lesson 2: Creating Columnstore Indexes
- Lesson 3: Working with Columnstore Indexes
- Lab: Using Columnstore Indexes
Module 5: Implementing an Azure SQL Data Warehouse
- Lesson 1: Advantages of Azure SQL Data Warehouse
- Lesson 2: Implementing an Azure SQL Data Warehouse Database
- Lesson 3: Developing an Azure SQL Data Warehouse
- Lesson 4: Migrating to an Azure SQL Data Warehouse
- Lesson 5: Copying Data with the Azure Data Factory
- Lab: Implement an Azure SQL Data Warehouse
Module 6: Creating an ETL Solution
- Lesson 1: Introduction to ETL with SSIS
- Lesson 2: Exploring Source Data
- Lesson 3: Implementing Data Flow
- Lab: Implementing Data Flow in an SSIS Package
Module 7: Implementing Control Flow in an SSIS Package
- Lesson 1: Introduction to Control Flow
- Lesson 2: Creating Dynamic Packages
- Lesson 3: Using Containers
- Lab A: Implementing Control Flow in an SSIS Package
- Lesson 4: Managing Consistency
- Lab B: Using Transactions and Checkpoints
Module 8: Debugging and Troubleshooting SSIS Packages
- Lesson 1: Debugging an SSIS Package
- Lesson 2: Logging SSIS Package Events
- Lesson 3: Handling Errors in an SSIS Package
- Lab: Debugging and Troubleshooting an SSIS Package
Module 9: Implementing a Data Extraction Solution
- Lesson 1: Introduction to Incremental ETL
- Lesson 2: Extracting Modified Data
- Lab A: Extracting Modified Data
- Lesson 3: Loading Modified Data
- Lesson 4: Temporal Tables
- Lab B: Loading a Data Warehouse
Module 10: Enforcing Data Quality
- Lesson 1: Introduction to Data Quality
- Lesson 2: Using Data Quality Services to Cleanse Data
- Lab A: Cleansing Data
- Lesson 3: Using Data Quality Services to Match Data
- Lab B: Deduplicating Data
Module 11: Master Data Services
- Lesson 1: Introduction to Master Data Services
- Lesson 2: Implementing a Master Data Services Model
- Lesson 3: Hierarchies and Collections
- Lesson 4: Creating a Master Data Hub
- Lab: Implementing Master Data Services Model
Module 12: Extending SQL Server Integration Services
- Lesson 1: Using Scripts in SSIS
- Lesson 2: Using Custom Components in SSIS
- Lab: Using Custom Scripts
Module 13: Deploying and Configuring SSIS Packages
- Lesson 1: Overview of SSIS Development
- Lesson 2: Deploying SSIS Projects
- Lesson 3: Planning SSIS Package Execution
- Lab: Deploying and Configuring SSIS Packages
Module 14: Consuming Data in a Data Warehouse
- Lesson 1: Introduction to Business Intelligence
- Lesson 2: Introduction to Data Analysis
- Lesson 3: Introduction to Reporting
- Lesson 4: Analyzing Data with Azure SQL Data Warehouse
- Lab: Using a Data Warehouse