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