20767 Implementing a SQL Data Warehouse2021-10-22T13:40:29-04:00

Summary

20767 Implementing a SQL Data Warehouse

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.

  • Dates: Nov 29, 2021- Nov 30, 2021- Dec 1, 2021- Dec 2, 2021- Dec 3, 2021
  • Times: 9:00 am - 5:00 pm
  • Location: Virtual
  • Seats: 10
  • Price: $2975

Introduction

Who Should Attend
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.

Course Objectives
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
– Implement 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

Outline

  • INTRODUCTION TO DATA WAREHOUSING

    • Overview of Data Warehousing
    • Considerations for a Data Warehouse Solution
  • PLANNING DATA WAREHOUSE INFRASTRUCTURE

    • Considerations for data warehouse infrastructure
    • Planning data warehouse hardware
  • DESIGNING AND IMPLEMENTING A DATA WAREHOUSE

    • Data warehouse design overview
    • Designing dimension tables
    • Designing fact tables
    • 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
    • Copying data with the Azure data factory
  • 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
    • Managing consistency
  • DEBUGGING AND TROUBLESHOOTING SSIS PACKAGES

    • Debugging an SSIS Package
    • Logging SSIS Package Events
    • Handling Errors in an SSIS Package
  • IMPLEMENTING A DATA EXTRACTION SOLUTION

    • Introduction to Incremental ETL
    • Extracting Modified Data
    • Loading 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

    • Introduction to Master Data Services
    • Implementing a Master Data Services Model
    • Hierarchies and collections
    • Creating a Master Data Hub
  • EXTENDING SQL SERVER INTEGRATION SERVICES (SSIS)

    • Using scripting in SSIS
    • Using custom components 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
    • An Introduction to Data Analysis
    • Introduction to reporting
    • Analyzing Data with Azure SQL Data Warehouse