MOC 20767 - Implementing a SQL Data Warehouse
Modalità di erogazione: Virtual classroom, Corsi in aula
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.
Presentazione del corso
Lo scopo di questo corso Microsoft MOC 20767 – Implementing a SQL Data Warehouse è che gli studenti apprendano come implementare una piattaforma di data warehouse per supportare una soluzione di Business Intelligence. Nel corso gli allievi imparano a creare un data warehouse con SQL Server 2016 e con Azure SQL Data Warehouse, a implementare ETL con SQL Server Integration Services e a validare e purificare i dati con SQL Server Data Quality Services e SQL Server Master Data Services.
Obiettivi
Al termine del corso gli allievi saranno in grado di:
- 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
Per partecipare con profitto a questo corso è necessario che gli allievi:
- almeno 2 anni di esperienza di lavoro con i database relazionali;
- capacità di disegnare un database normalizzato;
- capacità di creare tabelle e relazioni;
- capacità di scrivere query con il linguaggio Transact-SQL;
- capacità di scrivere semplici procedure di programmazione (ad esempio un ciclo);
- è inoltre preferibile conoscere alcuni concetti di business, quali il fatturato, la profittabilità, la gestione finanziaria.
Destinatari
Business Intelligence Developer.
Contenuti
Introduction to Data Warehousing
- Overview of Data Warehousing
- Considerations for a Data Warehouse Solution
Planning Data Warehouse Infrastructure
- Considerations for Building a Data Warehouse
- Data Warehouse Reference Architectures and Appliances
Designing and Implementing a Data Warehouse
- Logical Design for a Data Warehouse
- Physical Design for a Data Warehouse
Columnstore Indexes
- Introduction to Columnstore Indexes
- Creating Columnstore Indexes
- Working with Columnstore Indexes
Implementing an Azure SQL Data Warehouse
- 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
Creating an ETL Solution
- Introduction to ETL with SSIS
- Exploring Source Data
- Implementing Data Flow
Implementing Control Flow in an SSIS Package
- Introduction to Control Flow
- Creating Dynamic Packages
- Using Containers
Debugging and Troubleshooting SSIS Packages
- Debugging an SSIS Package
- Logging SSIS Package Events
- Handling Errors in an SSIS Package
Implementing an Incremental ETL Process
- Introduction to Incremental ETL
- Extracting Modified Data
- Temporal Tables
Enforcing Data Quality
- Introduction to Data Quality
- Using Data Quality Services to Cleanse Data
- Using Data Quality Services to Match Data
Using Master Data Services
- Master Data Services Concepts
- Implementing a Master Data Services Model
- Managing Master Data
- Creating a Master Data Hub
Extending SQL Server Integration Services (SSIS)
- Using Custom Components in SSIS
- Using Scripting in SSIS
Deploying and Configuring SSIS Packages
- Overview of SSIS Deployment
- Deploying SSIS Projects
- Planning SSIS Package Execution
Consuming Data in a Data Warehouse
- Introduction to Business Intelligence
- Introduction to Reporting
- An Introduction to Data Analysis
- Analyzing Data with Azure SQL Data Warehouse
Durata
35 ore