Implementing a Data Warehouse with SQL Server 2022

Course details

Data warehouses solve fundamentally different problems than a traditional transactional database, and SQL Server Data Warehouses provide a different model for storing valuable data. In this course, database expert Adam Wilbert guides you through the process of developing data warehouses in SQL Server 2022 to provide a robust, trustworthy platform to serve all your business intelligence reporting and analysis workloads. Explore data warehouse foundations, then get started creating a data warehouse in SQL Server. Learn about columnstore indexes, as well as how to automate the tasks associated with integrating multiple data sources together into a warehouse. Find out how to implement an Azure Synapse Analytics Dedicated SQL Pool and enforce data quality using Data Quality Services. Discover ways to use Master Data Services, plus learn how to consume data from your warehouse with business intelligence applications.

Concepts

Introduction

Storing information in a data warehouse
What you should know
Set up the example databases

Data Warehouse Foundations

Data warehouse core concepts
Transactional DBs vs. data warehouses
Dimensions and facts
Star and snowflake schemas
Hardware and infrastructure

Create a Data Warehouse

Create a data warehouse in SQL Server
Design dimension tables
Design fact tables
Create an indexed view

Columnstore Indexes

Advantages of columnstore indexes
Memory-optimized columnstore table
Rebuild columnstore indexes

Extract, Transform, and Load

What are ETL and SSIS
Understand data flow
Establish control flow

Implement an Azure Synapse Analytics Dedicated SQL Pool

Hosting a DW in the cloud
Create a dedicated SQL Pool
Reserve resources with workload isolation groups
Classify workloads
Load data into a staging table
Pause and remove resources in Azure

Enforce Data Quality

SQL Server Data Quality Services
Cleanse data with DQS
Create a custom knowledge base

Master Data Services

Introduction to Master Data Services
Install MDS and IIS
Configure Master Data Services
Deploy a sample MDS model
Install the MDS Excel add-in
Update master data in Excel

Consume Data from the Warehouse

Business intelligence applications

Conclusion

Next steps