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