Oracle Physical Data Warehouse Design and Implementation

Course:  OWPDP
Duration:  4 Days
Level:  I
Course Summary

This is a hands-on course that covers the three stages of data warehouse physical design implementation: staging, ETL (extraction, transformation, and loading), and presentation. After a brief discussion of multi-dimensional design this course will focus on the tasks and Oracle tools used to physically implement an Oracle data warehouse and present the data. The course will introduce the student to the following:

  • Oracle database constructs: tablespaces, schemas, indexes, materialized views, and privileges.
  • Oracle database performance parameters.
  • Oracle partitioning and parallel operations
  • Oracle OLAP dimensions, cubes, and hierarchies.
  • Oracle ETL/DML tools: Insert All, Merge, Cubes, Rollup, and Grouping Sets.
  • ETL strategies
  • Oracle analytic functions
  • Oracle products such as SQL Developer, SQL Developer Data Modeler, Discoverer, Oracle Warehouse Builder, and Oracle Data Integrator.

At the end of this class the student will have implemented and populated a customer star schema consisting of 500,000 rows. The student will create the database, set parameters, resize important tablespaces, create the staging and presentation schemas, create staging external tables, create and execute the ETL, index the star schema tables, and analyze the presentation schema using Discoverer.

« Hide The Details
Topics Covered In This Course

Review of Data Warehousing Terms and Concepts

  • The data warehouse environment
  • What is a data warehouse?
  • What is a data mart?
  • What is business intelligence (BI)?
  • How do OLTP and OLAP differ
  • What is data mining?
  • Operational vs. historic data
  • What is a star schema?
  • What is a snowflake schema?
  • Normalization vs. denormalization
  • What are h ierarchies?
  • What is dimensional modeling?
  • What is the Data Warehouse Bus Architecture (DWB)?
  • What are surrogate keys?
  • What is extract, transform, and load (ETL)?
  • What are slowly changing dimensions (SCD)?
  • What is metadata?
  • What are materialized views (MV's)?
  • How does logical design differ from physical design?

Oracle Database Architecture – A Summary

  • Memory structures
  • Logical storage structures
  • Processes
  • SQL tools and extensions
  • Oracle RAC clusters

Logical/Physical Design Process

  • Data warehouse database objects
  • Setting up schemas and privileges
  • Hardware and I/O considerations

Index Design Options

  • Why use indexes?
  • Composite indexes
  • Types of indexes
  • B-tree index
  • Bitmap indexes
  • Determining bitmap index candidates
  • Bitmap join index
  • Tuning star queries
  • Using star transformation
  • Star transformation restrictions
  • Function based indexes
  • Reverse key indexes
  • Descending indexes
  • Invisible indexes (11g)
  • Index monitoring
  • Foreign key indexing
  • Why indexes may not be used

DW Partitions, parallel operation, and compression

  • Compression options
  • Partition options
  • Dropping partitions
  • Truncating partitioned tables
  • Rebuilding index partitions
  • Splitting partitions
  • Parallelism options
  • Parallel query
  • Parallel DML
  • Parallel DDL
  • Parallel data loading
  • Recovery
  • Partition pruning
  • Merging partitions
  • Partition-wise joins
  • Local vs global partitioned indexes
  • Local partitioned indexes
  • Global range partitioned indexes
  • Global hash partitioned indexes
  • Global non-partitioned indexes
  • Guidelines for using partitioned indexes in OLTP applications
  • Guidelines for using partitioned indexes in data warehousing and DSS applications
  • Partitioned indexes on composite partitions


  • Join types
  • Nested loop join
  • Hash join
  • Sort merge join
  • Driving tables
  • Caching tables

Constraints, referential integrity, sequences, and synonyms

  • Maintaining the integrity of the database
  • The Check constraint
  • The Default option
  • The Not Null constraint
  • The Unique Constraint
  • The Primary Key constraint
  • The Foreign Key constraint
  • Modifying constraints
  • When to use constraints
  • Synonyms
  • Sequences

Materialized views

  • When to user materialized views
  • Types of materialized views
  • Materialized views with aggregates
  • Materialized views containing only joins
  • Nested materialized views
  • Build methods
  • Enable query rewrite
  • Query rewrite restrictions
  • General query rewrite restrictions
  • Refresh modes and options
  • General restrictions on fast refresh
  • Restrictions on fast refresh on materialized views with joins only
  • Restrictions on fast refresh on materialized views with aggregates
  • Restrictions on fast refresh on materialized views with the Union All operator
  • Timing the refresh
  • The Dbms_mview package
  • The Dbms_refresh package

Dimensions, levels and hierarchies

  • Multidimensional structures
  • Cubes
  • Measures
  • Joins
  • Dimensions
  • Levels
  • Hierarchies
  • Value based hierarchy
  • Skip level hierarchy
  • Ragged hierarchy
  • Working with hierarchies – parents and children
  • Dimensional query conditions
  • Oracle dimensions
  • Creating dimensions and cubes
  • Oracle OLAP option

Extraction, transformation, and loading

  • The ETL process
  • Extraction types
  • Data integration techniques
  • Data cleansing
  • Data cleansing methodology
  • Surrogate keys
  • The Insert-All statement
  • The Merge statement

Oracle Text

  • Oracle Text
  • Oracle Text indexes
  • Context index
  • Ctxcat index

Analytic functions, cube, rollup, grouping sets, and Oracle data warehouse tools

  • Rank() and over
  • Dense_rank()
  • Handling Nulls
  • Top N and Bottom N queries
  • The partition option
  • Percentage ranks
  • The Percent_rank() function
  • The Cume_dis() function
  • The Ntile() function
  • The Row_number() function
  • Windowing
  • The Cumulative Aggregate function
  • Moving averages
  • Moving averages with a partitioned outer join
  • Centered moving averages
  • The Ratio_to_report function
  • The Lag and Lead functions
  • Statistical functions
  • Linear regression functions
  • Predicting the future with linear regression
  • Grouping sets
  • The Rollup option
  • The Cube option

Oracle data warehouse performance considerations

  • DML error logging
  • Pipeline functions, external tables, and ETL

Oracle Discoverer

  • Product overview
  • Creating a business area using the Administrative edition
  • Join tables
  • Creating hierarchies
  • Creating lists
  • Creating a work sheet with the Desktop edition
  • Analyzing a star schema
What You Can Expect

Upon completion of this course, the participant should be able to translate logical data warehouse design specifications into an Oracle physical design model; and in turn, create the necessary scripts (e.g., DDL) to install a large scale partitioned Data Warehouse, using, amongst other things, bitmap indexes.

Who Should Take This Course

Database administrators, data administrators, developers, report writers, and business users.

« Hide The Details
Related Courses
Code Course Title Duration Level
Oracle OLTP and OLAP Database Design
5 Days
Oracle Data Integrator 11g
4 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Reports and Dashboards
4 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Boot Camp
5 Days
Oracle Database 11g: Administer a Data Warehouse
5 Days
Dimensional Modeling for Data Warehouse Projects
5 Days
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories
5 Days

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]