ORACLE DATA WAREHOUSE DESIGN

Course:  OWHDWD
Duration:  5 Days
Level:  I
Course Summary

After presenting entity relationship (ER) and dimensional modeling (DM) as competing alternatives, the presentation will focus on the use of DM techniques when developing and implementing a very large data warehouse. Using real-world business scenarios, SALES, for example, the instructor will coach the participants from logical through physical design of a data warehouse involving at least five dimensions and one-or-more star schemas.

Hands-on exercises include:

  • 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 create materialized views (summary tables)
« Hide The Details
Topics Covered In This Course

ORACLE ARCHITECTURAL OVERVIEW

  • Processes
  • Memory
  • Directory
  • Dictionary and catalog
  • Connectivity
  • Replication
  • Partitioning
  • Database
  • Real Application Cluster (RAC)

LOGICAL DESIGN CONCEPTS

  • Why data modeling
  • Requirements analysis
  • Normalization vs. denormalization
  • Entity relationship modeling
  • Dimensional modeling
  • 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 MODELING DESIGN (3) – HIERARCHIES

  • Definition
  • Types
  • Levels
  • Level relationships
  • Workshop, case study

DIMENSIONAL MODELING DESIGN (4) – INTEGRITY CONSTRAINTS

  • Scope and purpose
  • Unique
  • NOT NULL
  • FOREIGN KEY
  • Enforced vs. not-enforced
  • Workshop, case study

DIMENSIONAL MODELING DESIGN (5) – SCHEMA DESIGN

  • Star or snowflake
  • Data warehouse or data mart
  • Naming conventions
  • Maintenance requirements
  • Workshop, case study

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
  • Initialization 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
  • Workshop, case study

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
  • Best practices
  • Workshop, case study

PHYSICAL DESIGN (4) – PARTITIONING

  • Definition
  • Types
  • When to consider
  • Table compression
  • Partition pruning
  • Join techniques
  • Range partitioning
  • Index partitioning
  • Best practices

PHYSICAL DESIGN (5) – INDEXES

  • Bitmap indexes
  • B-tree indexes
  • Compression
  • Global vs. local indexes
  • Best practices
  • Workshop, case study

PHYSICAL DESIGN (6) – INTEGRITY CONSTRAINTS

  • Rationale
  • Constraint states
  • Unique constraints
  • Foreign key constraints
  • Enforced vs. not-enforced constraints
  • Materialized views considerations
  • Query rewrite considerations
  • Best practices
  • Workshop, case study

PHYSICAL DESIGN (7) – CREATE DIMENSIONS

  • Dimension hierarchical specification
  • Integrity constraints
  • Dimension validation
  • Dimension maintenance (e.g., ALTER)
  • Best practices
  • Workshop, case study

PHYSICAL DESIGN (8) – MATERIALIZED VIEW CREATION AND 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
  • Query rewrite 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/Analytics
  • Oracle Reports
  • OLAP and data mining

INTRODUCTION TO ORACLE SQL ADVISOR

  • Use
  • Tuning materialized views

DW PERFORMANCE CONSIDERATIONS

  • Query rewrite
  • Schema modeling
  • Aggregation
  • SQL modeling
  • EXPLAIN
  • I/O design
  • Parallelism
  • Initialization parameters
What You Can Expect

Upon completion of this course, the participant should be able to monitor and tune large data warehouses in a BI (Business Intelligence) or DS (Decision Support) environment.

Who Should Take This Course

Data Warehouse architects and DBAs.

Recommended Prerequisites

Knowledge of data warehousing terms and concepts.

Training Style

Lecture and hands-on.

« Hide The Details
Related Courses
Code Course Title Duration Level
OWHETL
ORACLE DATA WAREHOUSE DESIGN - ETL
3 Days
I
Details
DWHTCA
Data Warehousing Terms, Concepts and Architecture
1 Day
I
Details
DWHFUN
Data Warehousing Fundamentals
3 Days
I
Details
ORDWA
Oracle Database 11g: Administer a Data Warehouse
5 Days
II
Details
DWMODEL
Dimensional Modeling for Data Warehouse Projects
5 Days
II
Details

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]