Optimizing a Data Warehouse on the Microsoft SQL Server Platform
Once a data warehouse is initially created, performance and maintainability come into question. In this course, you'll explore SQL Server and the tools and resources needed to efficiently optimize a data warehouse.
What you'll learn
The resources offered by Microsoft SQL Server are geared towards architects, developers, and administrators in the database realm. In this course, Optimizing a Data Warehouse on the Microsoft SQL Server Platform, you'll delve into the tools and resources available in SQL Server for optimizing a data warehouse. First, you'll explore the options for indexing in a data warehouse. Next, you'll learn how to use Integration Services (SSIS) to import source data into your destination. Then, you'll discover what the database engine includes, such as, table partitioning, various indexes like clustered and columnstore, and bulk insert. Finally, you'll explore documentation options that includes data flows complementing a data dictionary. By the end of this course, you'll have the necessary knowledge needed to effectively optimize your data warehouse on the Microsoft SQL Server platform.
Table of contents
- Table Partitioning in SQL Server 5m
- Demo: Table Partitioning in SQL Server 3m
- Implementing Partitioning on a Fact Table for Faster Querying 5m
- Demo: Implementing Partitioning on a Fact Table for Faster Querying 3m
- Implementing Partition Switching on a Fact Table for Faster Loading 2m
- Demo: Implementing Partition Switching on a Fact Table for Faster Loading 4m
- Using Integration Services for Loading a Fact Table 3m
- Demo: Using Integration Services for Loading a Fact Table 6m
- Preparing Data and Tables for Bulk Insert 4m
- Demo: Database Recovery Modes 3m
- Summary 1m
- Indexing in a Data Warehouse 9m
- Demo: Indexing in a Data Warehouse 8m
- Building a Clustered Index on the Date Key of a Fact Table 4m
- Demo: Building a Clustered Index on the Date Key of a Fact Table 3m
- Creating and Working with Columnstore Indexes 3m
- Demo: Creating and Working with Columnstore Indexes 2m
- Maintenance Plans for Indexes 3m
- Demo: Maintenance Plans for Indexes 2m
- Summary 1m
- Creating an SSIS Package Template for Reuse 2m
- Demo: Creating an SSIS Package Template for Reuse 5m
- Creating a SSIS Solution and Project Template for Reuse 2m
- Demo: Creating a SSIS Solution and Project Template for Reuse 6m
- Creating Reusable Units of SSIS Package Workflow 1m
- Demo: Creating Reusable Units of SSIS Package Workflow 3m
- Implementing Parallelism for Faster Loading in SSIS 3m
- Demo: Implementing Parallelism for Faster Loading in SSIS 4m
- Summary 1m
- Introduction to Master Data Services 1m
- Creating Projects with Master Data Services 3m
- Demo: Creating Projects with Master Data Services 5m
- Using Master Data Services for Common Data 3m
- Demo: Using Master Data Services for Common Data 5m
- Implementing a Master Data Services Model 2m
- Demo: Implementing a Master Data Services Model 5m
- Using Excel, SSMS, and SSDT for Managing MDM Data 2m
- Demo: Using Views in SSIS and Staging Data with SSMS 5m
- Security and Sharing 5m
- Documenting a Data Warehouse 1m
- Building a Data Dictionary through Data Profiling 2m
- Demo: Building a Data Dictionary through Data Profiling 5m
- Automating DDL SQL Production 1m
- Demo: Automating DDL for a New Database 3m
- Demo: Automating DDL for a Change 5m
- Document Data Flows 1m
- Demo: Document Data Flows 2m
- Summary 1m
About the author
Thomas LeBlanc (Microsoft Data Platform MVP) is a Data Enthusiast in Baton Rouge, La. He started his IT career as a COBOL programmer in 1989 while at LSU. Since then he has spent years as a developer, DBA and Data Warehousing/Business Intelligence Architect. Community involve includes the local SQL Server and Analytics User Groups as well as SQLSaturday. Speaking engagements have been a blessing at SQL Pass Summit, Live 360, VSLive and IT Dev Connections.