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) AUDIENCE:   Data Warehouse architects and DBAs. PREREQUISITES:   Knowledge of data warehousing terms and concepts. DURATION:   5 days. OBJECTIVES:   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. COURSE CONTENT:   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 GOW/08