Implementing a Data Warehouse with SQL Server 2022

Learn how to create a long-term data storage solution using local SQL Server instances and cloud-based Azure Dedicated SQL Pools.

Introduction

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

1. Data Warehouse Foundations

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

2. Create a Data Warehouse

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

3. Columnstore Indexes

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

4. Extract, Transform, and Load

• What are ETL and SSIS?
• Understand data flow
• Establish control flow

5. 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

6. Enforce Data Quality

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

7. 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

8. Consume Data from the Warehouse

• Business intelligence applications

Conclusion

• Next steps