Optimizing a Data Warehouse on the Microsoft SQL Server Platform from Pluralsight

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.

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.

What's inside

Syllabus

Course Overview

Managing Data Warehouse with Best Practices

Partitioning Data Warehouse Fact Tables

Indexing Data Warehouse Tables

Extracting, Transforming, and Loading (ETL) a Data Warehouse

Cleansing Data in a Data Warehouse

Centralizing Data with Master Data Management Processes

Documenting a Data Warehouse

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Explores performance and maintainability of SQL Server data warehouses

Provides hands-on labs and interactive materials

Taught by Thomas LeBlanc

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Optimizing a Data Warehouse on the Microsoft SQL Server Platform with these activities:

Read 'Data Warehousing: Concepts, Technologies, and Applications'

Show steps

Reading this comprehensive text on data warehousing will provide a solid theoretical foundation for the concepts you'll encounter in this course.

View Corporate Information Factory on Amazon

Show steps

Review key chapters on data warehouse architecture, data modeling, and optimization techniques.
Use the book as a reference guide throughout the course to reinforce your understanding.

Learn Advanced Indexing Techniques

Show steps

Following tutorials on advanced SQL Server indexing techniques will broaden your understanding of how to efficiently optimize a data warehouse.

Show steps

Identify tutorials covering advanced indexing techniques.
Follow the tutorials and practice implementing the techniques.
Experiment with different indexing strategies to observe their impact on data warehouse performance.

Data Warehouse Optimization Discussion

Show steps

Engaging in discussions with peers about data warehouse optimization techniques will enhance your understanding and critical thinking skills.

Show steps

Join online forums or communities focused on data warehouse optimization.
Participate in discussions, asking questions and sharing insights.
Collaborate with peers on projects or case studies to apply optimization techniques.

Five other activities

Expand to see all activities and additional details

Show all eight activities

Explore Advanced SSIS Features

Show steps

Delving into advanced SSIS features through tutorials will equip you with deeper knowledge of efficient data loading and transformation techniques.

Browse courses on SSIS

Show steps

Find tutorials covering advanced SSIS concepts, such as data flows and transformations.
Follow the tutorials and implement the techniques in your own data warehouse environment.

Practice Table Partitioning and Columnstore Indexes

Show steps

Performing repeated exercises on table partitioning and columnstore indexes will enhance your proficiency in optimizing data warehouse performance.

Show steps

Create sample data warehouses with different partitioning and indexing schemes.
Run performance tests to compare and analyze the impact of different techniques.
Adjust partitioning and indexing strategies to achieve optimal performance.

Design and Implement a Data Dictionary

Show steps

Creating a comprehensive data dictionary will reinforce your understanding of the data warehouse structure and its components.

Show steps

Gather metadata and analyze the data warehouse structure.
Define data elements, attributes, and relationships.
Create a documentation framework using a tool or template.
Populate the data dictionary with accurate and complete information.

Compile Resources on Data Warehouse Best Practices

Show steps

Gathering and organizing resources on data warehouse best practices will provide a valuable reference for ongoing optimization efforts.

Show steps

Search and collect articles, whitepapers, and presentations on data warehouse best practices.
Create a central repository for these resources, such as a shared drive or document library.
Review and update the compilation regularly to ensure it remains current.

Contribute to an Open-Source Data Warehousing Project

Show steps

Participating in an open-source data warehousing project will extend your knowledge beyond the classroom and contribute to the community.

Show steps

Identify open-source data warehousing projects, such as Apache Hive or Presto.
Review the project documentation and identify areas where you can contribute.
Submit code changes, bug fixes, or documentation improvements to the project.

Career center

Learners who complete Optimizing a Data Warehouse on the Microsoft SQL Server Platform will develop knowledge and skills that may be useful to these careers:

Data Warehouse Developer

A Data Warehouse Developer is responsible for developing and maintaining data warehouses. This course will help you develop the skills needed to become a successful Data Warehouse Developer. You will learn how to use SQL Server to create and manage data warehouses, how to use Integration Services (SSIS) to import source data into your destination, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Warehouse Developer

SQL Server Data Warehouse Architect

A SQL Server Data Warehouse Architect designs, builds, and maintains data warehouses that are used by businesses to analyze data and make informed decisions. This course will help you develop the skills needed to become a successful SQL Server Data Warehouse Architect. You will learn how to use SQL Server to create and manage data warehouses, how to use Integration Services (SSIS) to import source data into your destination, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for SQL Server Data Warehouse Architect

Data Warehouse Administrator

A Data Warehouse Administrator is responsible for managing data warehouses. This course will help you develop the skills needed to become a successful Data Warehouse Administrator. You will learn how to use SQL Server to create and manage data warehouses, how to use Integration Services (SSIS) to import source data into your destination, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Warehouse Administrator

Data Analyst

A Data Analyst is responsible for analyzing data to identify trends and patterns. This course will help you develop the skills needed to become a successful Data Analyst. You will learn how to use SQL Server to query data, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Analyst

Database Administrator

A Database Administrator is responsible for managing databases. This course will help you develop the skills needed to become a successful Database Administrator. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Database Administrator

Data Scientist

A Data Scientist is responsible for using data to solve business problems. This course will help you develop the skills needed to become a successful Data Scientist. You will learn how to use SQL Server to query data, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Scientist

Business Intelligence Analyst

A Business Intelligence Analyst is responsible for using data to help businesses make better decisions. This course will help you develop the skills needed to become a successful Business Intelligence Analyst. You will learn how to use SQL Server to query data, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Business Intelligence Analyst

BI Developer

A BI Developer is responsible for developing and maintaining business intelligence systems. BI Developer is short for Business Intelligence Developer. This course will help you develop the skills needed to become a successful BI Developer. You will learn how to use SQL Server to query data, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for BI Developer

ETL Developer

A ETL Developer is responsible for developing and maintaining ETL systems. ETL Developer is short for Extract, Transform, and Load Developer. This course will help you develop the skills needed to become a successful ETL Developer. You will learn how to use SQL Server to query data, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for ETL Developer

DBA

A DBA is responsible for administering databases. DBA is short for Database Administrator. This course will help you develop the skills needed to become a successful DBA. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for DBA

Software Engineer

A Software Engineer is responsible for designing, developing, and maintaining software systems. This course will help you develop the skills needed to become a successful Software Engineer. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Software Engineer

Data Architect

A Data Architect is responsible for designing and managing data systems. This course will help you develop the skills needed to become a successful Data Architect. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Architect

Data Engineer

A Data Engineer is responsible for building and maintaining data systems. This course will help you develop the skills needed to become a successful Data Engineer. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Data Engineer

Database Designer

A Database Designer is responsible for designing and developing databases. This course will help you develop the skills needed to become a successful Database Designer. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Database Designer

Systems Analyst

A Systems Analyst is responsible for analyzing and designing computer systems. This course will help you develop the skills needed to become a successful Systems Analyst. You will learn how to use SQL Server to create and manage databases, how to use Integration Services (SSIS) to import data from different sources, and how to use the database engine to optimize data warehouse performance.

See salaries and explore the career path for Systems Analyst

Optimizing a Data Warehouse on the Microsoft SQL Server Platform

Here's a deal for you

What's inside

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses