Implementing a Data Warehouse SQL Server 2019

Course details

Dimensional models like data warehouses can provide a more accessible and consistent form of data storage than relational databases. You can consolidate data from multiple sources into a single repository for business intelligence, analysis, and reporting. This course explains how to create a long-term data storage solution using local SQL Server instances and Azure SQL Data Warehouse. Instructor Adam Wilbert shows how to build a data warehouse from the ground up, starting with the tables and views; establish control flow; enforce data quality; and use your data in services such as SQL Server Reporting Services and Power BI. By the end of the course, you will be able to implement a robust, custom solution to serve all your organization’s business intelligence, reporting, and analysis needs.

Learning objectives
Transactional databases vs. data warehouses
Star and snowflake schemas
Creating a data warehouse
Designing tables and views
Rebuilding columnstore indexes
Creating an Azure SQL Data Warehouse
Establishing control flow beyond ETL
Enforcing data quality
Configuring Master Data Services
Consuming data from the warehouse in BI services

Concepts

0. Introduction

01 - Store information in a data warehouse
02 - What you should know
03 - Set up the example databases

1. Data Warehouse Foundations

04 - Data warehouse core concepts
05 - Transactional databases vs. data warehouses
06 - Dimensions and facts
07 - Star and snowflake schemas
08 - Hardware and infrastructure

2. Create a Data Warehouse

09 - Create a data warehouse in SQL Server
10 - Design dimension tables
11 - Design fact tables
12 - Create an indexed view

3. Columnstore Indexes

13 - Advantages of columnstore indexes
14 - Memory-optimized columnstore table
15 - Rebuild columnstore indexes

4. Implement an Azure SQL Data Warehouse

16 - Hosting a data warehouse in the cloud
17 - Create an Azure SQL Data Warehouse project
18 - Develop tables in Azure SQL Data Warehouse
19 - The Data Warehouse Migration Utility
20 - Migrate a data warehouse to Azure
21 - Pause and remove an Azure data warehouse

5. Extract, Transform, and Load (ETL)

22 - What is ETL and SQL Server Integration Services (SSIS)
23 - Understand data flow
24 - Establish control flow

6. Enforce Data Quality

25 - SQL Server Data Quality Services (DQS)
26 - Cleanse data with DQS
27 - Create a custom knowledge base

7. Master Data Services

28 - Introduction to Master Data Services (MDS)
29 - Install MDS and IIS
30 - Configure Master Data Services
31 - Deploy a sample MDS model
32 - Install the MDS Excel add-in
33 - Update master data in Excel

8. Consume Data from the Warehouse

34 - Business intelligence applications

Conclusion

35 - Next steps