Descrizione del corso
This course provides an in-depth overview of Oracle Database Warehousing Administration, covering key concepts such as Data Warehousing, OLAP, ETL processes, and Data Mining. Participants will explore data warehouse components, dimensional modeling, and Oracle tools for ETL, performance optimization, and security. The course also focuses on warehouse development, data extraction and loading techniques, and managing data with materialized views. By the end, participants will be prepared to design, manage, and optimize Oracle data warehousing solutions.
Programma
Data Warehousing, Business Intelligence, OLAP, and Data Mining
- Data Warehouse Definition and Properties
- Data Warehouses, Business Intelligence, Data Marts, and OLTP
- Typical Data Warehouse Components
- Warehouse Development Approaches
- Extraction, Transformation, and Loading (ETL)
- The Dimensional Model and Oracle OLAP
- Oracle Data Mining
Defining Data Warehouse Concepts and Terminology
- Data Warehouse Definition and Properties
- Data Warehouse Versus OLTP
- Data Warehouses Versus Data Marts
Typical Data Warehouse Components
- Warehouse Development Approaches
- Data Warehousing Process Components
- Strategy Phase Deliverables
Business, Logical, Dimensional, and Physical Modeling
- Data Warehouse Modeling Issues
- Defining the Logical Model
- Defining the Dimensional Model
- Defining the Physical Model: Star, Snowflake, and Third Normal Form
- Fact and Dimension Tables Characteristics
- Translating Business Dimensions into Dimension Tables
- Translating Dimensional Model to Physical Model
- Database Sizing, Storage, Performance, and Security Considerations
- Database Sizing and Estimating and Validating the Database Size
Oracle Database Architectural Advantages
- Data Partitioning
- Indexing
- Optimizing Star Queries: Tuning Star Queries
- Parallelism
Security in Data Warehouses
The ETL Process: Extracting Data
- Extraction, Transformation, and Loading (ETL) Process
- ETL: Tasks, Importance, and Cost
- Extracting Data and Examining Data Sources
- Mapping Data
- Logical and Physical Extraction Methods
- Extraction Techniques and Maintaining Extraction Metadata
- Oracle’s ETL Tools: Oracle Warehouse Builder, SQL*Loader, and Data Pump
The ETL Process: Transforming Data
- Remote and Onsite Staging Models
- Transformation Techniques and Tools
- The ETL Process: Loading Data
- Loading Data into the Warehouse
- Transportation Using Flat Files, Distributed Systems, and Transportable Tablespaces
- Data Refresh Models: Extract Processing Environment
- Data Granularity
- Loading Techniques Provided by Oracle
- Postprocessing of Loaded Data
- Indexing and Sorting Data and Verifying Data Integrity
- Refreshing the Warehouse Data
- Developing a Refresh Strategy for Capturing Changed Data
- Capturing Changed Data for Refresh
- Time- and Date-Stamping, Database triggers, and Database Logs
- Applying the Changes to Data
Materialized Views
- Using Summaries to Improve Performance
- Using Materialized Views for Summary Management
- Types of Materialized Views
- Build Modes and Refresh Modes
- Query Rewrite: Overview
- Examining Metadata: ETL Metadata
- Extraction, Transformation, and Loading Metadata
Defining Metadata Goals and Intended Usage
- Identifying Target Metadata Users and Choosing Metadata Tools and Techniques
- Integrating Multiple Sets of Metadata
