Duration:

5 Days

Audience:

Employees of federal, state and local governments; and businesses working with the government.

Course Description:

In the “Implementing a SQL Data Warehouse course”, you’ll learn how to provision a Microsoft SQL Server database both on-premises and in Azure.

Learning Objectives:

Are you a BI professional ready to take your career to the next level? After finishing this course, you will have the skills needed to install and provision a data warehouse platform, migrate from an existing install and much more.

This course empowers you to:

  • Describe the main hardware required to build a data warehouse
  • Execute logical and physical design for a data warehouse
  • Create columnstore indexes and dynamic packages that include variables and parameters
  • Implement an Azure SQL Data Warehouse
  • Represent the key features of SSIS
  • Apply control flow by using tasks and precedence constraints
  • Debug and deploy SSIS projects
  • Understand the considerations to implement an ETL solution
  • Enable Data Quality Services and a Master Data Services model
  • Interpret how to use custom components to extend SSIS
  • Define common BI scenarios

Course Outline

1 – INTRODUCTION TO DATA WAREHOUSING
  • Gain an Introduction to Data Warehousing
  • Consider Factors Involved in a Data Warehouse Solution
  • Lab: Exploring a Data Warehouse Solution
2 – PLANNING DATA WAREHOUSE INFRASTRUCTURE
  • Review Considerations for Building a Data Warehouse
  • Plan Data Warehouse Hardware
  • Lab: Planning Data Warehouse Infrastructure
3 – DESIGNING AND IMPLEMENTING A DATA WAREHOUSE
  • Design Dimension Tables and Fact Tables
  • Determine Physical Design for a Data Warehouse
  • Lab: Implementing a Data Warehouse Schema
4 – COLUMNSTORE INDEXES
  • Review Columnstore Indexes
  • Create and Work with Columnstore Indexes
  • Lab: Using Columnstore Indexes
5 – IMPLEMENTING AN AZURE SQL DATA WAREHOUSE
  • Understand the Advantages of Azure SQL Data Warehouse
  • Implement an Azure SQL Data Warehouse
  • Develop an Azure SQL Data Warehouse
  • Migrate to an Azure SQL Data Warehouse
  • Copy Data with the Azure Data Factory
  • Lab: Implementing an Azure SQL Data Warehouse
6 – CREATING AN ETL SOLUTION
  • Gain an Introduction to ETL with SSIS
  • Explore Source Data
  • Implement Data Flow
  • Lab: Implementing Data Flow in an SSIS Package
7 – IMPLEMENTING CONTROL FLOW IN AN SSIS PACKAGE
  • Introduce Control Flow
  • Create Dynamic Packages
  • Adopt Containers
  • Lab: Implementing Control Flow in an SSIS Package
  • Lab: Using Transactions and Checkpoints
8 – DEBUGGING & TROUBLESHOOTING SSIS PACKAGES
  • Debug an SSIS Package
  • Log SSIS Package Events
  • Handle Errors in an SSIS Package
  • Lab: Debugging and Troubleshooting an SSIS Package
9 – IMPLEMENTING A DATA EXTRACTION SOLUTION
  • Navigate Incremental ETL
  • Extract and Load Modified Data
  • Manage Temporal Tables
  • Lab: Extracting Modified Data
  • Lab: Loading Incremental Changes
10 – ENFORCING DATA QUALITY
  • Apply Data Quality Services to Cleanse Data
  • Use Data Quality Services to Match Data
  • Lab: Cleansing Data
  • Lab: De-Duplicating Data
11 – USING MASTER DATA SERVICES
  • Become Familiar with Master Data Services
  • Execute a Master Data Services Model
  • Create a Master Data Hub
  • Lab: Implementing Master Data Services
12 – EXTENDING SQL SERVER INTEGRATION SERVICES (SSIS)
  • Utilize Custom Components in SSIS
  • Perform Scripting in SSIS
  • Lab: Using Scripts and Custom Components
13 – DEPLOYING AND CONFIGURING SSIS PACKAGES
  • Review SSIS Deployment
  • Deploy SSIS Projects
  • Plan SSIS Package Execution
  • Lab: Deploying and Configuring SSIS Packages
14 – CONSUMING DATA IN A DATA WAREHOUSE
  • Understand Basic Concepts of Business Intelligence, Reporting and Data Analysis
  • Analyze Data with Azure SQL Data Warehouse
  • Lab: Using Business Intelligence Tools