Oracle Data Warehousing

Duration: 
5 days

Overview

Practical exercises are included throughout the course, these cover the following topics:

  • Design and creation of dimensions
  • Design and creation of star schemas
  • Design and creation of hierarchies
  • Design and creation of indexes
  • Design and creation of integrity constraints
  • Extract, Transform and Load (ETL) options
  • Parallelism
  • Design and creation of materialized views (summary tables)

Audience

This course is for Oracle Data Warehouse architects and DBAs.

This course is suitable for Oracle release levels 9i and 10G.

Prerequisites

Delegates are expected to have a knowledge of Data Warehouse terms, concepts and architecture.

They should understand the implication of such terms as cubes, dimensions, attributes, joins, hierarchies, measures, etc...

This course is available on site only. Please call for details.

Course Outline

  • Oracle Architectural Overview
  • Processes
  • Memory
  • Directory
  • Dictionary and catalog
  • Connectivity
  • Replication
  • Partitioning
  • Database
  • Real Application Cluster (RAC)
  • Logical Design Concepts
  • Why data modelling
  • Requirements analysis
  • Normalization vs. denormalization
  • Entity relationship modelling
  • Dimensional modelling
  • OLAP vs. OLTP
  • Star vs. snowflake schemas
  • Metadata considerations
  • Data marts
  • Workshop
  • Dimensional Modelling Design (1) – Initial Steps
  • How to establish business requirements
  • How to choose a business process (e.g., sales)
  • How to determine the business process grain (i.e., level of detail for fact table)
  • How to choose dimensions (e.g., time)
  • How to identify measurement (numeric facts) to populate the fact table
  • Dimensional Modelling Design (2) – Fact Table
  • Definition
  • Granularity selection
  • Measurements
  • Additive vs. non-additive measures
  • Foreign keys
  • Joins with dimension tables
  • Staging
  • Workshop, case study
  • Dimensional Modelling Design (3) – Hierarchies
  • Types
  • Levels
  • Level relationships
  • Oracle Data Warehouse Design
  • Dimensional Modelling Design (4) – Integrity Constraints
  • Scope and purpose
  • Unique
  • NOT NULL
  • FOREIGN KEY
  • Enforced vs. not-enforced
  • Dimensional Modelling Design (5) – Schema Design
  • Star or snowflake
  • Data warehouse or data mart
  • Naming conventions
  • Maintenance requirements
  • Physical Design (1) – Large Data Warehouse Considerations
  • The environment (e.g., machine configuration)
  • Disk layout and placement (e.g., RAID)
  • Table sizes (e.g., maximum size for materialized view)
  • Database partition (e.g., how many?)
  • Partition key considerations
  • Initialisation parameters
  • Buffer pools
  • Data warehouse loads (e.g., parallelism options)
  • Physical Design (2) – Objects
  • Table spaces
  • Tables (partitioned vs. non-partitioned)
  • Index options
  • Integrity constraints
  • Materialized views (i.e., summary tables)
  • Creation of dimensions
  • Creation of hierarchies
  • I/O design considerations (e.g., striping and redundancy)
  • Best practices
  • Physical Design (3) – Parallelism
  • Definitions
  • When to consider (e.g., bulk loads, summaries)
  • How to enable parallelism
  • Hardware requirements
  • Query parallelism
  • Partitioned and non-partitioned tables
  • Data manipulation
  • Types of parallelism (e.g., DML, DDL)
  • How parallelism works
  • Restrictions
  • Physical Design (4) – Partitioning
  • When to consider
  • Table compression
  • Partition pruning
  • Join techniques
  • Range partitioning
  • Index partitioning
  • Physical Design (5) – Indexes
  • Bitmap indexes
  • B-tree indexes
  • Compression
  • Global vs. local indexes
  • Physical Design (6) – Integrity Constraints
  • Rationale
  • Constraint states
  • Unique constraints
  • Foreign key constraints
  • Enforced vs. not-enforced constraints
  • Materialized views considerations
  • Query rewrite considerations
  • Physical Design (7) – Create Dimensions
  • Dimension hierarchical specification
  • Dimension validation
  • Dimension maintenance (e.g., ALTER)
  • Physical Design (8) – Materialized View Creation & Maintenance
  • Use cases
  • Materialized view types
  • How to create
  • How to refresh
  • How to partition
  • How to tune
  • Logs (e.g., staging options)
  • Security considerations
  • Physical Design (9) – ETL
  • Options
  • Extraction options
  • Transformation options
  • Loading options
  • Change data capture and publishing
  • Introduction to Oracle Data Warehousing Tools
  • Oracle Warehouse Builder
  • Oracle Discoverer
  • Oracle Reports
  • OLAP and data mining
  • Introduction to Oracle SQL Advisor
  • Use
  • Tuning materialized views
  • DW Performance Considerations
  • Query rewrite
  • Schema modelling
  • Aggregation
  • SQL modelling
  • EXPLAIN
  • I/O design
  • Parallelism

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Onsite
Onsite5 4000 $4000 2019-03-22