AUDIENCE:   This course is for Oracle Data Warehouse architects and DBAs. PREREQUISITES:   Delegates are expected to have a knowledge of Data Warehouse terms, concepts and architecture. They should be conversant with terms and concepts as these relate to a data warehouse using both star and snowflake schemas. And the delegate should understand the implication of such terms as cubes, dimensions, attributes, joins, hierarchies, measures, etc. DURATION:   5 days. Hands on. OBJECTIVES:   This course is suitable for Oracle release levels 9i & 10G. Upon completion of this presentation, the participant should be able to monitor and tune large data warehouses in a BI (Business Intelligence) or DS (Decision Support) environment. After presenting entity relationship (ER) and dimensional modelling (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-ormore star schemas. Hands-on exercises where best practices and performance issues are discussed 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). COURSE CONTENT:   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 Definition Types Levels Level relationships Workshop, case study Oracle Data Warehouse Design Dimensional Modelling Design (4) – Integrity Constraints Scope and purpose Unique NOT NULL FOREIGN KEY Enforced vs. not-enforced Workshop, case study Dimensional Modelling 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 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 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 Oracle Data Warehouse Design 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 & 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 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 Initialisation parameters MT07/01