Oracle Database 19c: Data Warehousing Concepts LVC

Course Overview

• Objectives
• Lesson Agenda
• Course Objectives
• Prerequisites and Suggested Prerequisites
• Lesson Agenda
• Course Roadmap
• Lesson Agenda
• Use Case: NuMart Electronics
• Course Persona: Amy and John
• Lesson Agenda
• Course Environment
• Sample Database Schemas Used in the Course
• The HR Schema – Entity Relationship Diagram
• Sales History (SH) Schema
• Analytic Views (AV) Schema
• Class Account Information
• SQL Environments Available in the Course
• Entering SQL Statements by Using Oracle SQL*Plus
• What Is Oracle SQL Developer?
• Lesson Agenda
• Oracle Database 19c: Data Warehousing Documentation
• Resources to Bookmark
• Summary
• Practices

Evolution of Information Management and Data Warehousing

• John Begins His Training
• Objectives
• Lesson Agenda
• Evolution of Information Management Systems
• Executive Information Systems (EIS)
• Decision Support Systems (DSS)
• Challenges with Analyzing Data from Operational Systems
• OLTP Systems Versus Analytical Reporting
• Data Extract Processing
• Issues with Data Extract Programs
• Data Quality Issues with Extract Processing
• Lesson Agenda
• Data Warehousing and Business Intelligence
• Technological Advances That Make Data Warehousing Implementable
• Advantages of Warehouse Processing Environments
• Business Intelligence (BI): Definition and Purpose
• Business Drivers for DW & BI
• Capabilities of Business Intelligence
• Business Intelligence: Requirements
• Lesson Agenda
• What Other Data Can be Analyzed
• Characteristics of Big Data
• Challenges with Big Data
• Data Lakes Versus Data Warehouses
• Lesson Agenda
• Modern Data Warehouse
• Summary
• Practices

Overview of Data Warehouse and Multidimensional Model Concepts

• Data Warehousing Process Flow
• In Focus: Multidimensional Model
• Objectives
• Lesson Agenda
• Data Warehouse: Definition
• Data Warehouse Characteristics
• Use Case 1:
• Subject Oriented
• Use Case 2:
• Integrated
• Use Case 3:
• Time Variant
• Use Case 4:
• Nonvolatile
• Changing Warehouse Data
• Data Warehouse Versus OLTP
• Quiz
• Lesson Agenda
• Data Warehouse Architectures
• Basic Data Warehouse
• Characteristics of Enterprisewide Data Warehouse
• Data Warehouse Architecture with Staging Area
• Data Warehouse Architecture with Staging Area and Data Marts
• Data Warehouses Versus Data Marts
• Types of Data Marts
• Dependent Data Mart
• Independent Data Mart
• Quiz
• Lesson Agenda
• Data Warehouse Development Approaches
• “Big Bang” Approach
• Incremental Approach to Warehouse Development
• Phases of Incremental Approach
• Top-Down Approach
• Bottom-Up Approach
• Lesson Agenda
• #1: Methodology
• #2: Architecture
• #3: Extraction, Transformation, and Loading (ETL)
• #4: Implementation
• #5: Operation and Support
• Lesson Agenda
• A Typical Data Warehouse Query
• Breaking It Down Further . . .
• Multidimensional Model
• Star Schema Model
• Facts and Dimensions
• Multidimensional Model: Example Schema
• What Are Analytic Views?
• Analytic Views: Use Case
• A Query’s Tasks to Answer This Question
• Components of Analytic Views
• Hierarchy
• Analytic View: Example
• Analytic View Performance
• Summary
• Practices

About Business, Logical, Dimensional, and Physical Models

• John Learns About Data Warehouse Models
• In Focus: Data Presentation
• Objectives
• Lesson Agenda
• Data Warehouse Modeling
• Designing a Data Warehouse
• Data Warehouse Design Phases
• Lesson Agenda
• #1: Defining the Business Model
• Defining the Business Model: Performing Strategic Analysis
• Defining the Business Model: Creating the Business Model
• Business Requirements Drive the Design Process
• Using a Business Process Matrix
• Identifying Business Measures and Dimensions
• Quiz
• Determining Granularity
• Identifying Business Definitions and Rules
• Defining the Business Model: Documenting Warehouse Metadata
• Business Metadata Elements
• Metadata Documentation Approaches
• Lesson Agenda
• #2: Designing the Logical Model
• Entity Relationship Model (E-R Model)
• Defining Relationship Between Entities in E-R Diagram
• Lesson Agenda
• #3: Defining the Dimensional Model
• Dimensional Modeling Tasks
• Task 1: Identify the Fact Tables
• Fact Table Characteristics
• Fact Table Measures
• Base and Derived Measures
• Identifying Base and Derived Measures
• Task 2: Identify the Dimension Tables
• Dimension Table Characteristics
• Translating Business Dimensions into Dimension Tables
• Slowly Changing Dimensions
• Slowly Changing Dimension (SCD): An Example
• Primary Key and Foreign Key
• Composite Key and Surrogate Key
• Task 3: Link Fact and Dimension Tables
• Star Schema Model
• Star Dimensional Modeling
• Factless Fact Tables
• Advantages of Using a Star Dimensional Model
• Snowflake Schema Model
• Challenges with Snowflake Dimensional Model
• Third Normal Form (3NF)
• Task 4: Model the Time Dimension
• Modeling the Time Dimension
• Identifying Hierarchies for Dimensions
• Using Hierarchies to Drill on Data and Aggregate Data
• Using Logical Data Modeling Tools
• Lesson Agenda
• #4: Defining the Physical Model
• Physical Modeling Tasks
• Translating a Dimensional Model to a Physical Model
• Architecture Requirements
• Storage and Performance Considerations
• Summary
• Practices

Introduction to Extracting, Transforming, Loading Data

• In Focus: Data Enrichment
• Objectives
• Lesson Agenda
• Extraction, Transformation, and Loading (ETL) Process
• ETL: NuMart Electronics Use Case
• Data Transformation Location
• Extraction, Loading, and Transformation (E-LT)
• ETL Versus E-LT
• Extraction, Transformation, Loading: Tasks and Importance
• Lesson Agenda
• Data Extraction
• Factors Involved in Data Extraction
• Lesson Agenda
• Data Transformation
• Factors Involved in Data Transformation
• Lesson Agenda
• Data Loading
• Initial Load and Refresh
• Factors Involved in Data Loading
• Summary
• Practices

Introduction to Data Warehousing Platforms and Tools

• Objectives
• Lesson Agenda
• Data Warehouse Platforms
• Lesson Agenda
• Data Warehousing: Oracle On-Premises Tools
• Data Enrichment Layer: Oracle On-Premises Tools
• Model Enrichment Layer: Oracle On-Premises Tools
• Business Enrichment Layer: Oracle On-Premises Tools
• Lesson Agenda
• Data Warehousing: Oracle Cloud Services
• Data Enrichment Layer: Oracle Cloud Service
• Model Enrichment and Business Enrichment Layers: Oracle Cloud Service
• Summary
• Practices