Data Warehouse Concepts: Basic to Advanced concepts

Data Warehouse Concepts: Learn the in BI/Data Warehouse/BIG DATA Concepts from scratch and become an expert.

Created by Inf Sid, Last Updated 23-Oct-2019, Language:English

What Will I Get ?

Brief about the Data Warehouse and how the concept came into existence
Business Intelligence Concepts
Data Warehouse Architectures
ODS - Operational Data Store
OLAP - Online Analytical Processing
Data Marts
Metadata
Data Modeling
Entity Relationship Model
Dimensional Model
Data Integration & ETL
ETL vs ELT
ETL - Extraction Transformation & Loading
Typical Roles in a Data Warehouse Project
DW/BI/ETL Implementation Approach

Requirements

Basic understanding of the IT (Software) industry
Familiar with the basic concept of Database/RDBMS.

Description

In this course, you will learn all the concepts and terminologies related to the Data Warehouse , such as the OLTP, OLAP, Dimensions, Facts and much more, along with other concepts related to it such as what is meant by Start Schema, Snow flake Schema, other options available and their differences. It also explains how the data is managed with in the Data Warehouse and explains the process of reading and writing data onto the Warehouse. Later in the course you would also learn the basics of Data Modelling and how to start with it logically and physically. You would also learn all the concepts related to Facts, Dimensions, Aggregations and commonly used techniques of ETL.

Upon completion of this course, you would have a clear idea about, all the concepts related to the Data Warehouse, that should be sufficient to help you start off with the next step of becoming an ETL developer or Administering the Data warehouse environment with the help of various tools.

All the Best and Happy Learning !

Course Content

Brief about the Data warehouse

4 Lectures 00:21:22
- Is Data Warehouse still relevant in the age of Big Data?
  
  Preview
  
  00:04:25
- Why do we need a Data Warehouse?
  
  Preview
  
  00:05:26
- What is a Data Warehouse?
  
  Preview
  
  00:05:48
- Characteristics of a Data Warehouse
  
  Preview
  
  00:05:43
Business Intelligence

4 Lectures 00:23:37
- What is Business Intelligence?
  
  00:05:37
- Business Intelligence -Extended Explanation
  
  00:03:34
- Uses of Business Intelligence
  
  00:08:02
- Tools used for (in) Business Intelligence
  
  00:06:24
Data Warehouse Architectures

8 Lectures 00:31:18
- Enterprise Architecture or Centralized Architecture
  
  00:04:08
- Federated Architecture
  
  00:02:49
- Multi-Tired Architecture
  
  00:03:13
- Components of a Data Warehouse
  
  00:03:57
- Purpose of a Staging Area in Data Warehouse Architecture - Part 1
  
  00:04:49
- Purpose of a Staging Area in Data Warehouse Architecture - Part 2
  
  00:03:41
- Advantages Of Traditional Warehouse
  
  00:02:33
- Limitations of Traditional Data Warehouses
  
  00:06:08
ODS - Operational Data Store

4 Lectures 00:16:37
- What is ODS?
  
  00:02:26
- Define ODS
  
  00:07:40
- Features and Benefits of ODS
  
  00:02:24
- Differences between ODS,DWH, OLTP, OLAP, DSS
  
  00:04:07
OLAP

7 Lectures 00:28:51
- OLAP Overview
  
  00:05:54
- OLTP Vs OLAP - Part 1
  
  00:04:05
- OLTP Vs OLAP - Part 2
  
  00:05:31
- OLAP Architecture - MOLAP
  
  00:05:56
- ROLAP
  
  00:03:35
- HOLAP
  
  00:02:20
- DOLAP
  
  00:01:30
Data Mart

6 Lectures 00:13:52
- What is a Data Mart?
  
  00:01:40
- Fundamental Difference between DWH and DM
  
  00:00:40
- Advantages of a Data Mart
  
  00:02:46
- Characteristics of a Data Mart
  
  00:03:37
- Disadvantages of a Data Mart
  
  00:03:01
- Mistakes and MisConceptions of a Data Mart
  
  00:02:08
Metadata

6 Lectures 00:19:30
- Overview of Metadata
  
  00:01:50
- Benefits of Metadata
  
  00:01:47
- Types of Metadata
  
  00:05:38
- Projects on Metadata
  
  00:05:28
- Best Practices for Metadata Setup
  
  00:01:36
- Summary
  
  00:03:11
Data Modeling

2 Lectures 00:05:53
- What is Data Modeling?
  
  00:02:11
- Data Modeling Techniques
  
  00:03:42
Entity Relational Data Model

14 Lectures 00:35:45
- ER - (Entity Relation) Data Model
  
  00:03:37
- ER Data Model - What is Entity?
  
  00:02:01
- ER Data Model - Types of Entities - Part 1
  
  00:03:56
- ER Data Model - Types of Entities - Part 2
  
  00:01:49
- ER Data Model - Attributes
  
  00:01:54
- ER Data Model - Types of Attributes
  
  00:03:59
- ER Data Model - Entity-Set and Keys
  
  00:02:42
- ER Data Model - Identifier
  
  00:01:53
- ER Data Model - Relationship
  
  00:01:15
- ER Data Model - Notation
  
  00:02:34
- ER Data Model - Logical Data Model
  
  00:01:30
- ER Data Model - Moving from Logical Data Model to Physical Data Model
  
  00:02:14
- ER Data Model - Differences between CDM, LDM and PDM
  
  00:03:06
- ER Data Model - Disadvantages
  
  00:03:15
Dimensional Model

21 Lectures 01:24:27
- What is Dimension Modelling?
  
  00:04:38
- Benefits of Dimensional Modelling
  
  00:01:52
- What is a Dimension?
  
  00:02:36
- What is a Fact?
  
  00:02:00
- Additive Facts
  
  00:01:45
- Semi Additive Facts
  
  00:02:23
- Non-Additive Facts
  
  00:01:26
- FactLess Facts
  
  00:02:26
- What is a Surrogate key?
  
  00:03:45
- Star Schema
  
  00:04:54
- SnowFlake Schema
  
  00:03:22
- Galaxy Schema or Fact Constellation Schema
  
  00:02:20
- Differences between Star Schema and SnowFlake Schema?
  
  00:04:55
- Conformed Dimension
  
  00:06:17
- Junk Dimension
  
  00:03:12
- Degenerate Dimension
  
  00:03:36
- Slowly Changing Dimensions - Intro and Example Creation
  
  00:05:35
- Slowly Changing Dimensions - Type 1, 2 and 3
  
  00:12:14
- Slowly Changing Dimensions - Summary
  
  00:03:05
- Step by Step approach to set up the Dimensional Model using a retail case study
  
  00:06:44
- ER Model Vs Dimensional Model
  
  00:05:22
DWH Indexes

4 Lectures 00:10:59
- What is an Index?
  
  00:02:04
- Bitmap Index
  
  00:03:46
- B-Tree index
  
  00:01:49
- Bitmap Index Vs B Tree Index
  
  00:03:20
Data Integration and ETL

3 Lectures 00:13:20
- What is Data Integration?
  
  00:06:49
- What is ETL?
  
  00:03:49
- Common Questions and Summary
  
  00:02:42
ETL Vs ELT

3 Lectures 00:13:45
- ETL - Explained
  
  00:06:03
- ELT - Explained
  
  00:05:24
- ETL Vs ELT
  
  00:02:18
ETL - Extraction Transformation & Loading

3 Lectures 00:12:48
- Build Vs Buy
  
  00:05:10
- ETL Tools for Data Warehouses
  
  00:01:56
- Extraction Methods in Data Warehouses
  
  00:05:42
Typical Roles In DWH Project

13 Lectures 00:44:18
- Project Sponsor
  
  00:03:24
- Project Manager
  
  00:01:46
- Functional Analyst or Business Analyst
  
  00:02:53
- SME - Subject Matter Expert
  
  00:04:17
- DW BI Architect
  
  00:03:07
- Data Modeler
  
  00:08:59
- DWH Tech Admin
  
  00:01:20
- ETL Developers
  
  00:01:56
- BI OLAP Developers
  
  00:01:29
- ETL Testers/QA Group
  
  00:01:58
- DB UNIX Network Admins
  
  00:00:56
- Data Architect, Data Warehouse Architect, BI Architect and Solution Architect
  
  00:09:57
- Final Note about the Roles
  
  00:02:16
DW/BI/ETL Implementation Approach

18 Lectures 00:39:48
- Different phases in DW/BI/ETL Implementation Approach
  
  00:01:51
- Knowledge Capture Sessions
  
  00:03:34
- Requirements
  
  00:07:21
- Architecture phases
  
  00:04:48
- Data Model/Database
  
  00:01:35
- ETL Phase
  
  00:02:43
- Data Access Phase
  
  00:02:10
- Data Access Types - Selection
  
  00:01:37
- Data Access Types - Drilling Down
  
  00:00:58
- Data Access Types - Exception Reporting
  
  00:00:36
- Data Access Types - Calculations
  
  00:01:26
- Data Access Types - Graphics and Visualization
  
  00:00:58
- Data Access Types -Data Entry Options
  
  00:02:04
- Data Access Types - Customization
  
  00:01:00
- Data Access Types - WebBased Reporting
  
  00:00:56
- Data Access Types - BroadCasting
  
  00:01:04
- Deploy
  
  00:01:42
- Iterative Approach
  
  00:03:25

Inf Sid

Data Management Consultant

Business Intelligence Consultant and Trainer with 16+ years of extensive work experience on various client engagements. Delivered many large data warehousing projects and trained numerous professionals on business intelligence technologies. Extensively worked on all facets of data warehousing including requirement gathering, gap analysis, database design, data integration, data modeling, enterprise reporting, data analytics, data quality, data visualization, OLAP. Has worked on broad range of business verticals and hold exceptional expertise on various ETL tools like Informatica Powercenter, SSIS, ODI and IDQ, Data Virtualization, DVO, MDM.