دوره آموزش Data warehouse Fundamentals Oracle

Introduction

o Objectives

o Questions about You

o Lesson Agenda

o Course Objectives

o Prerequisites and Suggested Prerequisites

o Suggested Course Schedule

o Lesson Agenda

o Some of the Sample Schemas Used in the Course

o Sales History (sh) Schema

o Class Account Information

o Appendixes in the Course

o SQL Environments Available in the Course

o Entering SQL Statements Using Oracle SQL*Plus

o What Is Oracle SQL Developer?

o Creating Schema Objects

o Data Warehousing Tools Used in this Course

o Lesson Agenda

o Oracle 11g Useful Documentation

o Continuing Your Education

o Oracle Technology Network (OTN)

o Oracle by Example (OBE)

o Oracle Warehouse Builder OBEs

o Analytic Workspace Manager (AWM) OBEs

o Oracle Application Express (APEX) OBEsTechnical Support Summary

o Summary

Data Warehousing, Business Intelligence, OLAP, and Data Mining

o Objectives

o Lesson Agenda

o Evolution of BI

o Early Management Information Systems

o Analyzing Data from Operational Systems

o Why OLTP Is Not Suitable for Analytical Reporting

o Data Extract Processing

o Issues with Data Extract Programs

o Productivity Issues with Extract Processing

o Data Quality Issues with Extract Processing

o Data Warehousing and Business Intelligence

o Technological Solutions for Data Warehousing

o Advantages of Warehouse Processing Environments

o Business Intelligence (BI): Definition and Purpose

o What Is Business Intelligence?

o The User Dilemma

o Problem: Multivendor, Uninterested Environment

o The Optimal Information Platform for Business Intelligence

o The Optimal Information Platform

o Success Factors for a Dynamic Business Environment

o Business Drivers for Data Warehouses

o Business Intelligence: Requirements

o Lesson Agenda

o OLAP: Overview

o Typical Example of an OLAP Query

o The Dimensional Model

o Measures

o Measure Types

o Dimensions

o Example of Dimensions in a Report

o Sharing Dimensions

o Hierarchy

o Hierarchy: Example

o Level

o Hierarchy Types

o Attributes

o Attributes and Levels: Examples

o Dimensional Model Summarized

o Physical Layers of a Data Warehouse

o Managing the Data Warehouse

o Oracle OLAP: Part of an Integrated DW Platform

o What Is Oracle OLAP?

o Oracle OLAP Option: 11g Highlights

o ROLAP Versus MOLAP

o Oracle Data Mining: An Overview

o Oracle Data Mining: Interfaces

o Summary

Defining Data Warehouse Concepts and Terminology

o Objectives

o Lesson Agenda

o Data Warehouse: Definition

o Data Warehouse Properties

o Subject Oriented

o Integrated

o Time Variant

o Nonvolatile

o Changing Warehouse Data

o Data Warehouse Versus OLTP

o Enterprise-Wide Data Warehouse

o Data Warehouses Versus Data Marts

o Dependent Data Mart

o Independent Data Mart

o Typical Data Warehouse Components

o Lesson Agenda

o Warehouse Development Approaches

o “Big Bang” Approach

o Top-Down Approach

o Bottom-Up Approach

o Incremental Approach to Warehouse Development

o Data Warehousing Process Components

o Methodology

o Architecture

o Extraction, Transformation, and Loading (ETL)

o Implementation

o Operation and Support

o Phases of the Incremental Approach

o Strategy Phase Deliverables

o Introducing the Case Study: Roy Independent School District (RISD)

o Summary

Business, Logical, Dimensional, and Physical Modeling

o Objectives

o Lesson Agenda

o Data Warehouse Modeling Issues

o Data Warehouse: Design Phases

o Phase 1: Defining the Business Model

o Defining the Business Model: Performing Strategic Analysis

o Defining the Business Model: Creating the Business Model

o Business Requirements Drive the Design Process

o Using a Business Process Matrix

o Identifying Business Measures and Dimensions

o Determining Granularity

o Identifying Business Definitions and Rules: Example

o Documenting Metadata

o Business Metadata Elements

o Metadata Documentation Approaches

o Phase 2: Designing the Logical Model

o Lesson Agenda

o Phase 3: Defining the Dimensional Model

o Data Warehouse Schemas

o Star Schema Model

o Star Dimensional Modeling

o Advantages of Using a Star Dimensional Model

o Snowflake Schema Model

o Third Normal Form (3NF)

o Fact Table: Characteristics

o More on Fact less Fact Tables

o Identifying Base and Derived Measures

o Fact Table Measures

o Dimension Table: Characteristics

o Translating Business Dimensions into Dimension Tables

o Slowly Changing Dimensions

o Slowly Changing Dimension (SCD): An Example

o Types of Database Keys

o Using Time in the Data Warehouse

o Time Dimension

o Identifying Hierarchies for Dimensions

o Using Hierarchies to Drill on Data and Aggregate Data

o Using Data-Modeling Tools

o Phase 4: Defining the Physical Model

o Translating a Dimensional Model to a Physical Model

o Architectural Requirements

o Making the Right Choice

o Storage and Performance Considerations

o Summary

Database Sizing, Storage, Performance, and Security Considerations

o Objectives

o Lesson Agenda

o Sizing the Database and Other Storage Requirements

o Estimating the Database Size

o Validating Database Size Assumptions

o Testing Load Sampling

o Oracle Database Architectural Advantages

o Lesson Agenda

o Why Data Partitioning Is Needed

o Data Partitioning

o Benefits of Partitioning

o Oracle’s Partitioning Strategies

o Partition Performance Benefits: Partition Pruning

o Indexing

o B-Tree Index

o Bitmap Indexes

o Bitmap Index: Example

o Comparing B-Tree and Bitmap Indexes

o Other Index Properties

o Optimizing Star Queries: Tuning Star Queries

o Optimizing Star Queries: Star Transformation

o Star Query: Example

o Lesson Agenda

o Parallelism

o Degree of Parallelism (DOP)

o Operations That Can Be Parallelized

o Parallel Execution Server Pool

o PARALLEL Clause: Examples

o Using Summary Data

o HP Oracle Exadata Storage Server Hardware

o Exadata Storage Deployment

o Exadata Key Benefits for Data Warehousing

o Security in Data Warehouses

o Oracle’s Strategy for Data Warehouse Security

o Oracle-Supplied Technology and Tools for Implementing VPD

o Summary

The ETL Process: Extracting Data

o Objectives

o Lesson Agenda

o Extraction, Transformation, and Loading (ETL) Process

o ETL: Tasks, Importance, and Cost

o Extracting Data

o Examining Data Sources

o Production Data

o Archive Data

o Internal Data

o External Data

o Mapping Data

o Lesson Agenda

o Extraction Methods

o Change Data Capture Mechanism in Oracle Database

o Extraction Techniques

o Designing Extraction Processes

o Maintaining Extraction Metadata

o Possible ETL Failures

o Maintaining ETL Quality

o Oracle’s ETL Tool: Oracle Warehouse Builder

o Oracle-Supported Features for ETL

o Oracle’s Solution for ETL: Oracle Streams, Replication, and Message Queuing

o Summary

The ETL Process: Transforming Data

o Objectives

o Lesson Agenda

o Transformation

o Remote Staging Model

o On-Site Staging Model

o Data Anomalies

o Transformation Routines

o Transforming Data: Problems and Solutions

o Multipart Keys Problem

o Multiple Local Standards Problem

o Multiple Files Problem

o Missing Values Problem

o Duplicate Values Problem

o Element Names Problem

o Element Meanings Problem

o Input Formats Problem

o Referential Integrity Constraints Problem

o Name and Address Problem

o Name-and-Address Processing in Oracle Warehouse Builder

o Quality Data: Importance and Benefits

o Quality: Standards and Improvements

o Data Quality Guidelines

o Data Quality: Solutions and Management

o Lesson Agenda

o Transformation Techniques: Merging Data

o Transformation Techniques: Adding a Date Stamp

o Transformation Techniques: Adding Keys to Data

o Summarizing Data

o Maintaining Transformation Metadata

o Data Ownership and Responsibilities

o Transformation Timing and Location

o Choosing a Transformation Point

o Monitoring and Tracking

o Designing Transformation Processes

o Transformation Tools

o Data Transformation

o Multistage Data Transformation

o Pipelined Data Transformation

o Oracle’s Enhanced Features for Transformation

o Application of the MERGE Statement in Data Warehousing: Example

o Multitable INSERT Statements

o Advantages of Multitable INSERTs

o Oracle’s Enhanced Features for Transformation

o Summary

The ETL Process: Loading Data

o Objectives

o Lesson Agenda

o Loading Data into the Warehouse

o Transportation in a Data Warehouse

o Transportable Tablespaces

o Initial Load and Refresh

o Data Refresh Models: Extract Processing Environment

o Data Refresh Models: Warehouse Processing Environment

o Building the Loading Process

o Data Granularity

o Loading Techniques

o Loading Technique Considerations

o Loading Techniques Provided by Oracle: SQL*Loader

o Loading Techniques Provided by Oracle

o Using External Tables

o Benefits of Using External Tables

o Creating External Tables

o Example of Defining External Tables

o Defining External Tables Using SQL*Loader

o Creating and Loading an External Table Using ORACLE_DATAPUMP: Example

o Lesson Agenda

o Postprocessing of Loaded Data

o Indexing and Sorting Data

o Unique Indexes

o Creating Derived Keys

o Summary Management

o Filtering Data

o Verifying Data Integrity

o Steps for Verifying Data Integrity

o Standard Quality Assurance Checks

o Summary

Refreshing the Warehouse Data
o Objectives

o Lesson Agenda

o Developing a Refresh Strategy for Capturing Changed Data

o User Requirements and Assistance

o Load Window Requirements

o Planning the Load Window

o Scheduling the Load Window

o Capturing Changed Data for Refresh

o Choosing a Method for Change Data Capture

o Wholesale Data Replacement

o Comparison of Database Instances

o Time- and Date-Stamping

o Database Triggers

o Using a Database Log

o Refresh Mechanisms in the Oracle Database

o Lesson Agenda

o Applying the Changes to Data

o Overwriting a Record

o Adding a New Record

o Adding a Current Field

o Limitations of Methods for Applying Changes

o Maintaining History: Techniques

o History Tables and One-to-Many Relationships Versioning

o Preserving Complete History

o Purging and Archiving Data

o Oracle-Supported Techniques for Purging Data

o Oracle-Supported Techniques for Archiving Data

o Final Tasks

o Publishing Data

o Summary

Materialized Views

o Objectives

o Lesson Agenda

o The Need for Summary Management

o Using Summaries to Improve Performance

o Summary Management

o Summary Navigation

o Managing Historical Summary Data in the Warehouse

o Summary Management Using the Oracle Database

o Using Materialized Views for Summary Management

o Using Summaries Without Materialized Views: Example

o Using Materialized Views for Summary Management: Example

o Determining Which Materialized View to Create

o Creating a Materialized View Using the CREATE SQL Statement: Example

o Available Refresh Modes When Creating a Materialized View

o Manual Refresh Using the DBMS_MVIEW Package Procedures

o Using the DBMS_MVIEW Package: The Available ON DEMAND Refresh Methods

o Refreshing at Scheduled Time: Using the START WITH and NEXT Clauses

o Query Rewrite Overview

o Cost-Based Query Rewrite Process

o Conditions Required for Oracle to Rewrite a Query

o Query Rewrite

o Lesson Agenda

o What Are Dimensions?

o A dimension is a structure that categorizes data to enable users to answer business questions.

o What Are Dimension Objects?

o Why Are Dimensions Important?

o Dimensions and Hierarchies

o Dimension Example

o Defining Dimensions and Hierarchies

o Dimensions with Multiple Hierarchies

o Summary

Leaving a Metadata Trail

o Objectives

o Defining Warehouse Metadata

o Metadata Users

o Types of Metadata

o Examining Metadata: ETL Metadata

o Extraction Metadata

o Transformation Metadata

o Loading Metadata

o Examining Metadata: End-User Metadata

o End-User Metadata: Context

o Historic Context of Data

o Types of Context

o Developing a Metadata Strategy

o Defining Metadata Goals and Intended Usage

o Identifying Target Metadata Users

o Choosing Metadata Tools and Techniques

o Choosing the Metadata Location

o Managing the Metadata

o Integrating Multiple Sets of Metadata

o Managing Changes to Metadata

o Additional Metadata Content and Considerations

o Common Warehouse Metamodel

o Oracle Warehouse Builder: Compliance with OMG-CWM

o Summary

Data Warehouse Implementation Considerations

o Objectives

o Project Management

o Requirements Specification or Definition

o Logical, Dimensional, and Physical Data Models

o Data Warehouse Architecture

o ETL Considerations

o Reporting Considerations

o Security Considerations

o Metadata Management

o Testing the Implementation

o Post-Implementation Change Management

o Some Useful Resources and White Papers

o Summary