Implementing a SQL Data Warehouse
- Codice corso: 20767C
- Durata corso: 5gg
Introduzione
This five-day instructor-led course provides students with the knowledge and skills to provision a Microsoft SQL Server database. The course covers SQL Server provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.
The primary audience for this course are 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.
Obiettivi del corso
After completing this course, 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
Prerequisiti
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 relational databases.
Some experience with database design.
Struttura del Corso
MODULE 1: Introduction to Data Warehousing
This module describes data warehouse concepts and architecture consideration.
Lessons
After completing this module, you will be able to:
Describe the key elements of a data warehousing solution
Describe the key considerations for a data warehousing solution
Exploring data sources
Exploring an ETL process
Exploring a data warehouse
Lab : Exploring a Data Warehouse Solution
MODULE 2: Planning Data Warehouse Infrastructure
This module describes the main hardware considerations for building a data warehouse.
Lessons
After completing this module, you will be able to:
Describe the main hardware considerations for building a data warehouse
Explain how to use reference architectures and data warehouse appliances to create a data warehouse
Lab : Planning Data Warehouse Infrastructure
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
After completing this module, you will be able to:
Implement a logical design for a Data Warehouse
Designing dimension tables
Designing fact tables
Implement a Physical Design for a Data Warehouse
Lab : Implementing a Data Warehouse Schema
MODULE 4: Columnstore Indexes
This module introduces Columnstore Indexes.
Lessons
After completing this module, you will be able to:
Create a Columnstore index on the FactProductInventory table
Create a Columnstore index on the FactInternetSales table
Create a memory optimized Columnstore table
Work with Columnstore Indexes
Lab : Using Columnstore Indexes
MODULE 5: Implementing an Azure SQL Data Warehouse
This module describes Azure SQL Data Warehouses and how to implement them.
Lessons
After completing this module, you will be able to:
Describe the advantages of Azure SQL Data Warehouse
Implement an Azure SQL Data Warehouse
Develop an Azure SQL Data Warehouse
Migrate to an Azure SQ Data Warehouse
Copy data with the Azure data factory
Plan for migrating to Azure SQL Data Warehouse
Lab : Implementing an Azure SQL Data Warehouse
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
After completing this module, you will be able to:
Describe ETL with SSIS
Explore Source Data
Transferring data by using a data row task
Using transformation components in a data row
Implement a Data Flow
Lab : Implementing Data Flow in an SSIS Package
MODULE 7: Implementing Control Flow in an SSIS Package
This module describes implementing control flow in an SSIS package.
Lessons
After completing this module, you will be able to:
Describe Control Flow
Create Dynamic Packages
Use Containers
Managing consistency
Using tasks and precedence in a control flow
Using variables and parameters
Using containers
Using transactions
Using checkpoints
Lab : Implementing Control Flow in an SSIS Package
Lab : Using Transactions and Checkpoints
MODULE 8: Debugging and Troubleshooting SSIS Packages
This module describes how to debug and troubleshoot SSIS packages.
Lessons
After completing this module, you will be able to:
Debug an SSIS package
Log SSIS package events
Implementing an event handler
Handle errors in an SSIS package
Lab : Debugging and Troubleshooting 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
After completing this module, you will be able to:
Describe incremental ETL
Extract modified data
Load modified data
Describe temporal tables
Using a datetime column to incrementally extract data
Using change data capture
Using the CDC control task
Using change tracking
Loading data from CDC output tables
Using a lookup transformation to insert or update dimension data
Implementing a slowly changing dimension
Using the merge statement
Lab : Extracting Modified Data
Lab : Loading a data warehouse
MODULE 10: Enforcing Data Quality
This module describes how to implement data cleansing by using Microsoft Data Quality services.
Lessons
After completing this module, you will be able to:
Describe Data Quality Services
Cleanse data using data Quality Services
Match data using data Quality Services
De-duplicate data using data Quality Services
Create a DQS knowledge base
Use a DQS project to cleanse data
Use DQS in an SSIS package
Creating a matching policy
Using a DS project to match data
Lab : Cleansing Data
Lab : De-duplicating Data
MODULE 11: Using Master Data Services
This module describes how to implement master data services to enforce data integrity at source.
Lessons
After completing this module, you will be able to:
Describe the key concepts of Master Data Services
Implement a Master Data Service model
Manage Master Data
Create a Master Data Hub
Use the master data services add-in for Excel
Enforce business rules
Load data into a model
Consume master Data Service data
Lab : Implementing Master Data Services
MODULE 12: Extending SQL Server Integration Services (SSIS)
This module describes how to extend SSIS with custom scripts and components.
Lessons
After completing this module, you will be able to:
Use custom components in SSIS
Use scripting in SSIS
Lab : Using scripts
MODULE 13: Deploying and Configuring SSIS Packages
This module describes how to deploy and configure SSIS packages.
Lessons
After completing this module, you will be able to:
Describe an SSIS deployment
Deploy an SSIS package
Create an SSIS catalog
Deploy an SSIS project
Create environments for an SSIS solution
Run an SSIS package in SQL server management studio
Schedule SSIS packages with SQL server agent
Plan SSIS package execution
Lab : Deploying and Configuring SSIS Packages
MODULE 14: Consuming Data in a Data Warehouse
This module describes how to debug and troubleshoot SSIS packages.
Lessons
After completing this module, you will be able to:
Describe at a high level business intelligence
Show an understanding of reporting
Show an understanding of data analysis
Analyze data with Azure SQL data warehouse
Explore a reporting services report
Explore a PowerPivot workbook
Explore a power view report
Lab : Using a data warehouse