Data Warehouse - The Ultimate Guide

Master Data Warehousing, Dimensional Modeling & ETL process

What you'll learn
Architect & implement a professional data warehouse end-to-end
You will learn the principles of Data Warehouse Design
You will master ETL process in both theory & practise
You will implement in a case study your own data warehouse & ETL process
You will learn the modern architecture of a Data Warehouse
Dimensional Modeling in a professional way
Requirements
Basic SQL is helpful but absolutely not necessary
Laptop or PC
Description
Master Data Warehousing, Dimensional Modeling & ETL processDo you want to learn how to implement a data warehouse in a modern way?This is the only course you need to master architecting and implementing a data warehouse end-to-end!Data Modeling and data warehousing is one of the most important skills in Business Intelligence & Data Engineering!This is the most comprehensive & most modern course you can find on data warehousing.Here is why:Most comprehenisve course with 9 hours video lecturesLearn from a real expert - crystal clear & straight-forwardMaster theory & practice - hands-on demonstrations, assignments & quizzesWe will implement a complete data warehouse - end-to-endUnderstand everything step by step from the absolute basics to the advanced topicsLearn the practical steps and the important theory to upskill your careerThis course will take you all the way to being able to architect and implement a data warehouse in a company in a professional manner.Here is what you'll learn:Data Warehouse BasicsData Warehouse architectureData Warehouse infrastructureData ModelingSetting up an ETL process Dimensional Modeling: Facts & DimensionsImplementing a comeplete data warehouse hands-onSlowly Changing DimensionsUnderstanding ETL toolsELT vs. ETLAdvanced topics like: Columnar storage, OLAP Cubes, In-memory databases, massive parallel processing & cloud data warehousesOptimizing a data warehouse using indexes (B-tree indexes & Bitmap indexes)Practically using and connecting a data warehouseBy the end of this course you will be able to design & build a complete data warehouse from the ground up. You will have the knowledge, the practical skills and the confidence to implement a modern data warehouse professionally.Everything you need to be a highly proficient data architect, data engineer, data analyst or Business Intelligence expert!Join now to get instant & lifetime access - of course backed by the no-questions-asked 30 days money back guarantee!

Overview

Section 1: Intro

Lecture 1 Welcome!

Lecture 2 How this course works

Lecture 3 What do you learn in this course?

Lecture 4 Course slides

Section 2: Data Warehouse Basics

Lecture 5 Why a data warehouse?

Lecture 6 What is a data warehouse?

Lecture 7 What is Business Intelligence?

Lecture 8 Data Lake or Data Warehouse?

Lecture 9 Demos & Hands-on

Lecture 10 Setting up Pentaho (ETL tool)

Lecture 11 Setting up PostgreSQL (Database system)

Section 3: Data Warehouse Architecture

Lecture 12 3 Layers of a Data Warehouse

Lecture 13 Staging area

Lecture 14 Demo: Setting up the staging area

Lecture 15 Data Marts

Lecture 16 Relational databases

Lecture 17 In-Memory databases

Lecture 18 Cubes

Lecture 19 Operational Data Storage

Lecture 20 Summary

Section 4: Dimensional Modeling

Lecture 21 What is dimensional modeling?

Lecture 22 Why dimensional modeling?

Lecture 23 Facts

Lecture 24 Dimensions

Lecture 25 Star schema

Lecture 26 Snowflake schema

Lecture 27 Demo: Product & Category dimension (snowflaked)

Section 5: Facts

Lecture 28 Additivity

Lecture 29 Nulls in facts

Lecture 30 Year-to-Date facts

Lecture 31 Types of fact tables

Lecture 32 Transactional fact tables

Lecture 33 Periodic fact tables

Lecture 34 Accumulating snapshots

Lecture 35 Comparing fact table types

Lecture 36 Factless fact tables

Lecture 37 Steps in designing fact tables

Lecture 38 Surrogate Keys

Lecture 39 Case Study: The Project

Lecture 40 Case Study: Identify the business process

Lecture 41 Case Study: Define the grain

Lecture 42 Case Study: Identify the dimensions

Lecture 43 Case Study: Identify the facts

Section 6: Dimensions

Lecture 44 Dimension tables

Lecture 45 Date dimensions

Lecture 46 Nulls in dimensions

Lecture 47 Hierarchies in dimensions

Lecture 48 Conformed dimensions

Lecture 49 Degenerate dimensions

Lecture 50 Junk dimension

Lecture 51 Role-playing dimension

Lecture 52 Case Study: Date dimension

Section 7: Slowly Changing Dimensions

Lecture 53 What are slowly changing dimensions?

Lecture 54 Type 0 - Original

Lecture 55 Type 1 - Overwrite

Lecture 56 Type 2 - Additional row

Lecture 57 Administrating Type 2 dimensions

Lecture 58 Mixing Type 1 & Type 2

Lecture 59 Type 3 - Additional attribute

Section 8: ETL process

Lecture 60 Understanding the ETL process

Lecture 61 Extract

Lecture 62 Initial Load

Lecture 63 Delta Load

Lecture 64 Load Workflow

Lecture 65 Demo: Quick Intro to Pentaho

Lecture 66 Demo: Setting up tables in SQL

Lecture 67 Demo: Initial Load example

Lecture 68 Demo: Delta Load example

Lecture 69 Transforming data

Lecture 70 Basic Transformations

Lecture 71 Advanced Transformations

Lecture 72 Demo: Planning next steps

Lecture 73 Demo: Table setup & Complete Staging

Lecture 74 Demo: Transform

Lecture 75 Demo: Load & Validate results

Lecture 76 Scheduling jobs

Section 9: ETL tools

Lecture 77 ETL tools

Lecture 78 Choosing the right ETL tool

Section 10: Case Study: Creating a Data Warehouse

Lecture 79 Plan of attack

Lecture 80 Source data & table design

Lecture 81 Setting up the tables in database

Lecture 82 Staging: Sales Fact

Lecture 83 Staging job & fixing problems

Lecture 84 Load Payment Dimension

Lecture 85 Transform & Load Sales Fact

Lecture 86 Transform & Load job

Lecture 87 Final ETL job & Incremental Load

Section 11: ETL vs. ELT

Lecture 88 What is an ELT?

Lecture 89 ETL vs. ETL

Section 12: Using a Data Warehouse

Lecture 90 What are the common use cases?

Lecture 91 Connecting the DWH to Power BI

Section 13: Optimizing a Data Warehouse

Lecture 92 Using indexes

Lecture 93 B-tree indexes

Lecture 94 Bitmap indexes

Lecture 95 Guidelines for indexes

Lecture 96 Demo: Setting indexes

Section 14: The Modern Data Warehouses

Lecture 97 Cloud vs. on-premise

Lecture 98 Benefits cloud vs on-premise

Lecture 99 Massive parallel processing

Lecture 100 Columnar storage

Section 15: Bonus

Lecture 101 Bonus lecture

Data Analyst that want to upskill and learn how to build a data warehouse,Data Engineers that want to learn about data warehousing and data modeling,People that want to become a data architect, BI consultant, data engineer or data analyst,Data professionals that want to upskill in Business Intelligence & Data Modeling