Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Introduction to Data Warehousing
Rating: 4.4 out of 5(37 ratings)
113 students

Introduction to Data Warehousing

An introduction to data warehouses, using SQL Server 2019, SSMS, Visual Studio & SSIS. Covers topics in the 70-463 exam.
Created byMaria Hamilton
Last updated 10/2021
English

What you'll learn

  • Define and describe common data warehousing concepts
  • Install SQL Server, SSMS and Visual Studio with the SSIS package
  • Create an ETL package in SSIS
  • Integrate data from text files and databases

Course content

3 sections • 27 lectures • 1h 48m total length

  • Introduction0:50

    Explore the fundamentals of data warehousing, including why businesses need a data warehouse, oltp vs olap, etl, dimensional modeling with facts and dimensions, star and snowflake schemas, and architecture.

  • What is a Data Warehouse?2:49

    Identify what a data warehouse is, its role in consolidating data from multiple sources through ETL, and its use for reports, dashboards, and decision making.

  • Why do we need a Data Warehouse?3:08

    Discover how a data warehouse consolidates and summarizes data from multiple systems to enable consistent reporting across departments. Use sliced, fact-based insights to support executive decisions.

  • What does a Data Warehouse look like?2:16

    Demonstrate how dashboards and data warehouses consolidate time-based sales, costs, margins, and customer data to reveal what’s happening in a business.

  • The Ideal Data Warehouse1:37

    Identify the ideal data warehouse as easily accessible, fast, consistent in language across teams, flexible to change, secure, and foundational for decision making, with data populated overnight or within day.

  • The Difference Between OLTP and OLAP2:22

    Compare online transactional processing (OLTP) and online analytical processing (OLAP), showing OLTP handles current, high-volume transactions in transactional databases, while OLAP powers historical data analysis in data warehouses.

  • What is Extract, Transform and Load (ETL)?3:03

    Learn how extract, transform and load consolidate data from diverse sources—databases, legacy systems, cloud, on premises servers, and mobile devices—into a centralized data warehouse, ensuring consistency and accuracy.

  • What is Dimensional Modeling?4:08

    Dimensional modeling optimizes data warehouse structures by organizing data into facts, dimensions, and attributes in a star schema, enabling fast, understandable analysis of products, locations, time, sales amounts, and profits.

  • What are Fact Tables?3:44

    Fact tables store additive measurements such as sales, profit, and quantity, with one row per measurement event identified by a composite key and linked to dimensions via foreign keys.

  • What are Dimension Tables?2:44

    Provide the context for business processes with dimension tables linked to a fact table, featuring product key details and primary/foreign key relationships.

  • The Star and Snowflake Schema1:08

    The star schema centers a sales fact with dimensions such as store, employee, customer, product, and date. The snowflake schema normalizes these dimensions into additional tables, depending on business needs.

Requirements

  • A Windows computer or virtual machine with at least 2GB of RAM and about 10GB of hard drive space.

Description

This course is an overview of basic data warehousing concepts, a guide for installing software and a step-by-step tutorial on using the software. This course is divided into three main sections:

Data Warehousing Basics

We begin with a conversation about data warehousing concepts, including the following:

  • What is a data warehouse?

  • Why do we need a data warehouse?

  • What is Extract, Transform and Load (ETL)?

  • The difference between OLAP and OLTP databases

  • The Star and Snowflake schemas

  • Fact and Dimension tables

These concepts can be difficult to understand, so I explain them using real conversation and examples. We have this conversation first so that you are familiar with the ideas as they are discussed in the later sections of the course.

Data Warehousing Software Installation

If you want to become good at data warehousing, you need to use the software. In this section I start by talking with you about the software and explain how the different pieces work together. Next is a step-by-step walkthrough of installing SQL Server Developer, SQL Server Management Studio (SSMS) and Visual Studio Community with the SQL Server Integration Services (SSIS) package. The versions of software we download are all free for you to use.

SSIS Tutorial: Create a Project and Basic Package with SSIS

The last section of the course is a step-by-step tutorial on using the ETL tool SSIS. The tutorial is broken down into nine steps:

  • Step 1: Create a new integration services project

  • Step 2: Add and configure a flat file connection manager

  • Step 3: Add and configure an OLE DB connection manager

  • Step 4: Add a data flow task to the package

  • Step 5: Add and configure the flat file source

  • Step 6: Add and configure the lookup transformations

  • Step 7: Add and configure the OLE DB destination

  • Step 8: Make the Lesson 1 package easier to understand

  • Step 9: Test the Lesson 1 package

Before we being the tutorial, I discuss the steps in detail. I explain the source data, how the data will be transformed and how we will get the data to it's destination database.

Who this course is for:

  • People taking a college course in data warehousing or data integration
  • People who need to understand data warehousing, SQL Server or SSIS for their job

Report abuse