Implementing a SQL Data Warehouse (20767)
Cours disponible en français ou en anglais - Training available in French or in English
Duration: 5 jours
Objectives:
This five-day instructor-led course provides students with the knowledge and skills to provision a Microsoft SQL Server 2016 database. The course covers SQL Server 2016 provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.
Course details :
Module 1: Introduction to Data Warehousing
This module describes data warehouse concepts and architecture consideration.
Lessons
Module 2: Planning Data Warehouse Infrastructure
This module describes the main hardware considerations for building a data warehouse.
Lessons
Module 3: Designing and Implementing a Data Warehouse
This module describes how you go about designing and implementing a schema for a data warehouse.
Lessons
Module 4: Columnstore Indexes
This module introduces Columnstore Indexes.
Lessons
Module 5: Implementing an Azure SQL Data Warehouse
This module describes Azure SQL Data Warehouses and how to implement them.
Lessons
Module 6: Creating an ETL Solution
At the end of this module you will be able to implement data flow in a SSIS package.
Lessons
Module 7: Implementing Control Flow in an SSIS Package
This module describes implementing control flow in an SSIS package.
Lessons
Module 8: Debugging and Troubleshooting SSIS Packages
This module describes how to debug and troubleshoot SSIS packages.
Lessons
Module 9: Implementing a Data Extraction Solution
This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.
Lessons
Module 10: Enforcing Data Quality
This module describes how to implement data cleansing by using Microsoft Data Quality services.
Lessons
Module 11: Using Master Data Services
This module describes how to implement master data services to enforce data integrity at source.
Lessons
Module 12: Extending SQL Server Integration Services (SSIS)
This module describes how to extend SSIS with custom scripts and components.
Lessons
Module 13: Deploying and Configuring SSIS Packages
This module describes how to deploy and configure SSIS packages.
Lessons
Module 14: Consuming Data in a Data Warehouse
This module describes how to debug and troubleshoot SSIS packages.
Lessons
Requirements :
In addition to their professional experience, students who attend this training should already have the following technical knowledge:
Cours disponible en français ou en anglais - Training available in French or in English
Duration: 5 jours
Objectives:
This five-day instructor-led course provides students with the knowledge and skills to provision a Microsoft SQL Server 2016 database. The course covers SQL Server 2016 provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.
Course details :
Module 1: Introduction to Data Warehousing
This module describes data warehouse concepts and architecture consideration.
Lessons
- Overview of Data Warehousing
- Considerations for a Data Warehouse Solution
Module 2: Planning Data Warehouse Infrastructure
This module describes the main hardware considerations for building a data warehouse.
Lessons
- Considerations for data warehouse infrastructure.
- Planning data warehouse hardware.
Module 3: Designing and Implementing a Data Warehouse
This module describes how you go about designing and implementing a schema for a data warehouse.
Lessons
- Designing dimension tables
- Designing fact tables
- Physical Design for a Data Warehouse
Module 4: Columnstore Indexes
This module introduces Columnstore Indexes.
Lessons
- Introduction to Columnstore Indexes
- Creating Columnstore Indexes
- Working with Columnstore Indexes
Module 5: Implementing an Azure SQL Data Warehouse
This module describes Azure SQL Data Warehouses and how to implement them.
Lessons
- Advantages of Azure SQL Data Warehouse
- Implementing an Azure SQL Data Warehouse
- Developing an Azure SQL Data Warehouse
- Migrating to an Azure SQ Data Warehouse
- Copying data with the Azure data factory
Module 6: Creating an ETL Solution
At the end of this module you will be able to implement data flow in a SSIS package.
Lessons
- Introduction to ETL with SSIS
- Exploring Source Data
- Implementing Data Flow
Module 7: Implementing Control Flow in an SSIS Package
This module describes implementing control flow in an SSIS package.
Lessons
- Introduction to Control Flow
- Creating Dynamic Packages
- Using Containers
- Managing consistency.
Module 8: Debugging and Troubleshooting SSIS Packages
This module describes how to debug and troubleshoot SSIS packages.
Lessons
- Debugging an SSIS Package
- Logging SSIS Package Events
- Handling Errors in an SSIS Package
Module 9: Implementing a Data Extraction Solution
This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.
Lessons
- Introduction to Incremental ETL
- Extracting Modified Data
- Loading modified data
- Temporal Tables
Module 10: Enforcing Data Quality
This module describes how to implement data cleansing by using Microsoft Data Quality services.
Lessons
- Introduction to Data Quality
- Using Data Quality Services to Cleanse Data
- Using Data Quality Services to Match Data
Module 11: Using Master Data Services
This module describes how to implement master data services to enforce data integrity at source.
Lessons
- Introduction to Master Data Services
- Implementing a Master Data Services Model
- Hierarchies and collections
- Creating a Master Data Hub
Module 12: Extending SQL Server Integration Services (SSIS)
This module describes how to extend SSIS with custom scripts and components.
Lessons
- Using scripting in SSIS
- Using custom components in SSIS
Module 13: Deploying and Configuring SSIS Packages
This module describes how to deploy and configure SSIS packages.
Lessons
- Overview of SSIS Deployment
- Deploying SSIS Projects
- Planning SSIS Package Execution
Module 14: Consuming Data in a Data Warehouse
This module describes how to debug and troubleshoot SSIS packages.
Lessons
- Introduction to Business Intelligence
- An Introduction to Data Analysis
- Introduction to reporting
- Analyzing Data with Azure SQL Data Warehouse
Requirements :
In addition to their professional experience, students who attend this training should already have the following technical knowledge:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
- Some experience with database design.