S Data Integration and ETL with Oracle Warehouse Builder Ed 2

• Objectives I-2
• Course Objectives I-3
• Course Agenda: Day 1 I-4
• Course Agenda: Day 2 I-5
• Course Agenda: Day 3 I-6
• Appendixes I-7
• Questions About You I-8
• Summary I-9
• Objectives
• Lesson Agenda
• What Is Oracle Warehouse Builder?
• Producing Quality Information
• Managing the Full Life Cycle of Metadata and Data
• Basic Process Flow of Design and Deployment
• Oracle Warehouse Builder Licensing
• Connectivity Options
• BI Environment from an OWB Perspective
• Full Support for SOA
• Lesson Agenda
• Installing Oracle Warehouse Builder 11.2
• Simpler OWB 11.2 Installation
• OWBSYS Schema
• OWB 11.2 Server Installation
• OWB 11.2 Stand-Alone Installation
• Using OWB 11.2 with Database 10g R2
• Script for Creating OWBSYS in Database 10g R2
• Making OWB Oracle Home Known to Database 10g R2
• Unlock OWBSYS and OWBSYS_AUDIT
• Quiz
• Lesson Agenda
• Warehouse Builder Workspace
• Workspace Installation
• Workspace Owners and Users: Job Functions
• Starting the Repository Assistant
• Using the Repository Assistant to Manage Workspaces
• Dropping a Workspace
• OWB Product Components
• Lesson Agenda
• Supported Operating Systems
• Oracle Database Support: Source, Target, Repository
• Installing Optional Components
• Simplified Installation of Oracle Workflow
• Lesson Agenda
• Classroom Configuration
• Oracle Warehouse Builder Documentation and Resources
• Quiz
• Summary
• Practice 1-1Overview: Creating aWorkspace, WorkspaceOwner, and aWorkspace User
• Objectives
• Lesson Agenda
• Logging In to OWB Design Center
• OWB 11.2 Uses the Oracle IDE Interface
• Design Center: Start Page
• Design Center: Dockable Panels
• Projects Navigator, Locations Navigator, and Globals Panel
• Structure Panel
• Structure Panel Versus Object Editor
• Property Inspector
• Graphical Navigator
• Advanced Find
• Help Menu and the OWB Help Center
• Managing Deployment with the Control Center Manager
• Quiz
• Lesson Agenda
• Projects
• Objects in a Project
• Objects Within a Project
• Objects Within an Oracle Module
• Objects Within a Module
• Organizing Metadata Using Foldering
• Lesson Agenda
• Locations, Connectors, and Control Centers
• Globals Navigator Objects
• Lesson Agenda
• Creating a Project
• Deleting a Project
• Lesson Agenda
• Setting Project Preferences
• Recent Logon Drop-Down List
• Enabling Recent Logon List
• Scripting in Design Center
• Quiz
• Summary
• Practice 2-1 Overview: Examining the Design Center Interface
• Objectives
• Lesson Agenda
• Warehouse Builder Development Cycle
• Architecture for Design, Deployment, Execution
• OWB Topology with CCA on Oracle Target Server
• Configurations, Control Centers, Locations
• Lesson Agenda
• Locations
• Creating Locations
• Metadata and Data Locations
• Registering Locations
• Unregistering Locations
• Alternatives for Unregistering Locations
• Connectors
• Quiz
• Control Centers
• Control Center and Locations
• Configurations and Control Centers
• Lesson Agenda
• Creating Target Schemas
• Registering DB User as an OWB User
• Using the Security Node to Register an OWB User
• Roles and Privileges of Warehouse Builder Users
• Registering an Oracle Workflow User
• Course: Predefined and New Objects
• Import the CLASS_PROJECT1.mdl File
• Quiz
• Summary
• Practice 3-1 Overview: Setting Up a Pre-Populated Project
• Objectives
• Lesson Agenda
• Data Warehouse Implementation: Typical Steps
• Metadata to Data
• Difference Between Obtaining Relational and Flat File Source Metadata
• Source Module
• Lesson Agenda
• Data Sources
• Creating a Flat File Source Module
• Create Module Wizard: Connection Information
• Metadata to Data
• Importing Source Metadata
• Selecting the Flat File for Sampling
• View the Sample File
• Flat File Sample Wizard: Welcome
• Describing the File
• Sampling Simple Delimited File
• Setting Field Properties
• Flat File Sample Wizard: Summary
• Advanced: Record Organization
• Advanced: Selecting the File Format
• Advanced: File Layout
• Advanced: Field Properties
• Quiz
• Lesson Agenda
• Data Sources
• Creating an Oracle Module
• Create Module Wizard: Name and Description
• Create Module Wizard: Connection Information
• Editing the Oracle Database Location
• Finishing Module Creation and Proceeding to Import
• Import Metadata Wizard: Filter Information
• Selecting the Tables for Import
• Completing the Metadata Import
• Viewing Data
• Quiz
• Summary
• Practice 4-1 Overview: Defining and Importing Flat Files and a Relational Source
• Objectives
• Lesson Agenda
• Purpose of a Staging Area
• Data Staging Area
• Data Warehouse Implementation: Typical Steps
• Metadata to Data
• Defining Staging Area in OWB
• Lesson Agenda
• OWB Mapping
• Building a Mapping
• Creating a New Mapping
• Mapping Editor Canvas
• Tools to Manage the Mapping Editor Canvas Operators
• Grouping and Ungrouping
• Spotlighting in the Mapping Editor
• Practice 5-1: Working in the Mapping Editor
• Lesson Agenda
• Creating Mappings for Flat File Sources
• What Are External Tables?
• Creating an External Table
• Create External Table: File Selection
• Create External Table: Locations
• Configuring External Tables
• Practice 5-2: Creating a Staging Module and an External Table
• Including the External Table in a Mapping
• Creating a Staging Table
• Create and Bind
• Adding an Attribute
• Levels of Synchronizing Changes
• Synchronizing: Outbound and Inbound
• Synchronize All in a Mapping
• Quiz
• Lesson Agenda
• Using a Filter Operator
• Using the Expression Builder to Define a Filter Condition
• Using a Data Generator
• Lesson Agenda
• Mapping Source Columns to the Target
• Using the Automapper in the Mapping Editor
• Lesson Agenda
• Expression Operator
• Copying Operators Across Mappings
• Expression Editing and Copying
• Quiz
• Lesson Agenda
• Setting Loading Type
• Target Load Ordering
• Overriding the Default Load Order
• Lesson Agenda
• Validating the Mapping
• Generating the Mapping
• Quiz
• Summary
• Practice 5-3 Overview: Defining ETL Mappings for Staging Data
• Objectives
• Lesson Agenda
• Component Palette
• Using a Joiner
• Setting the Join Condition
• Setting the Joiner Input Role
• Lookup Operator
• Lookup Condition
• Handling Multiple Match Rows
• Using the Subquery Filter Operator
• Sequence Operator
• Set Operation Operator
• Using the Splitter Operator for Multitable Insert
• Quiz
• Pivot and Unpivot Operators
• Wizard-Based Pivot Definition
• Resulting Pivot Code and Mapping
• Aggregator Operator
• Using a Constant Operator
• Using the Expression Builder to Define a Constant Operator
• Using the Transformation Operator
• Transformation Library
• Transformation Details
• Pre/Post Processing Operators
• Pre/Post Mapping Operator: Example
• Lesson Agenda
• Deploying Database Objects and Mappings
• Deploying in Projects Navigator Panel
• Executing a Mapping
• Quiz
• Summary

Practice 6-1 Overview: Using the Joiner, Sequence, and the Transformation Operator in a

• Mapping
• Practice 6-2 Overview: Using the Subquery and the Lookup Operator
• Objectives
• Lesson Agenda
• Integrating Data Quality into ETL
• Name and Address Data Cleansing
• Name and Address Server
• Name and Address Software Providers
• Reviewing MAP_NA Mapping
• Examine the SOURCE Table Data
• Settings in the Name and Address Operator
• Deploying and Executing the Mapping
• CLEANSED Name and Address Data
• Quiz
• Lesson Agenda
• Consolidating Data Using the Match Merge Operator
• Match Merge Operator
• Match and Merge Rules
• Using the Match Merge Operator in a Mapping
• Defining the Match Bins
• Defining the Match Rules
• Defining an Active Custom Rule
• Defining the Merge Rules
• Target Tables
• Merged Output
• Quiz
• Summary
• Practice 7-1 Overview: Cleansing and Match-Merging Name and Address Data
• Objectives
• Lesson Agenda
• Data Warehouse Implementation: Typical Steps
• Process Flow Concepts
• Creating a Process Flow Module
• Creating an Oracle Workflow Location
• Creating a Process Flow Package
• Creating a Process Flow
• Lesson Agenda
• Types of Activities
• Adding a Fork Activity
• Adding a Mapping Activity
• Adding an And Activity
• Adding End Activities
• Lesson Agenda
• Creating Transitions Between Activities
• Transition Conditions
• Lesson Agenda
• Manual Activity
• SQLPLUS Activity
• Email Activity
• Some More Activities
• Lesson Agenda
• Validating the Process Flow
• Generating the Process Flow Package
• Quiz
• Summary
• Practice 8-1 Overview: Creating a Simple Process Flow
• Objectives
• Lesson Agenda
• Data Warehouse Implementation: Typical Steps
• Logical Versus Physical
• Physical Implementation
• Configuring for Physical Implementation
• Setting Object Configuration
• Deployment Concepts
• Registering Locations
• Unregistering Locations
• Configurations and Control Centers
• Active Configuration
• Control Center and Locations
• Quiz
• Lesson Agenda
• Invoking the Control Center Manager
• Control Center Manager Components
• Control Center Manager Interface
• Various Deploy Actions
• Deploy Options and Preferences
• Deployment Preferences
• Control Center Jobs
• Job Details Window
• OWB Automatically Creates a Connector
• Quiz
• Lesson Agenda
• Data Warehouse Implementation: Typical Steps
• Executing Mappings
• Viewing Data
• Practice 9-1 Overview: Deploying, Loading, and Viewing Data
• Lesson Agenda
• Repository Browser
• Common Repository Browser Tasks
• Starting OWB Browser Listener
• Starting the Repository Browser
• Logging In to the Repository Browser
• Browsing Design Center Reports
• Browsing Control Center Reports
• Types of Control Center Reports
• Object Summary Report
• Deployment Report
• Execution Summary Report
• Execution Report
• Quiz
• Summary
• Practice 9-2 Overview: Viewing Design and Control Center Reports
• Objectives
• Lesson Agenda
• Mapping Debugger: Overview
• Mapping Debugger
• Lesson Agenda
• Warning: Not All Test Data Found
• Test Data Tab Shows Tables Not Found
• Operator Debug Binding Dialog Box
• Direct Access to Database Entity
• Define the Other Source Table
• Replace Target with a Debugging Table
• Create a Copy of the Target Table
• Reinitialize the Debug Session
• Reinitializing the Session
• Set Breakpoints and Watch Points
• Watch Panel For Non-Data-Based Operators
• Quiz
• Summary
• Practice 10-1 Overview: Using the Mapping Debugger
• Objectives
• Lesson Agenda
• Devise a Performance Strategy Early
• Performance Tuning at Various Levels
• ETL Design: Mappings
• Performance-Related Parameters in ETL Design
• Pros and Cons of Operating Modes
• Configuring Mappings for Operating Modes
• DML Error Logging
• Commit Control
• Setting Default Audit Level
• Additional Run-Time Parameters for Mappings
• Enable Partition Exchange Loading (PEL) for Targets
• Best Practices Tips: ETL Design Factors That Impact Performance
• Quiz
• Performance-Related Parameters in Schema Design
• Benefits of Using Schema Design Capabilities in Warehouse Builder Design Center
• Indexing
• Configuring Properties of Indexes
• Index Performance Considerations: Drop Indexes Before the Load Process
• Index Performance Considerations: Drop Indexes Before the Load Process (Example
• Mapping)
• Constraints Management
• Configuring Constraints Individually
• Defining Partitions in Warehouse Builder
• Parallelism
• Enable Parallel DML
• Setting Tablespace Properties
• Minimum Error Logging
• Gathering Statistics
• Analyze Table Statements Property
• Gathering Schema Statistics
• Quiz
• Summary
• Practice 11-1Overview: Configuring Performance Parameters for Mappings and Tables
• Objectives
• Lesson Agenda
• Metadata Loader Utilities (MDL)
• Uses of Metadata Loader Utilities
• Exporting Objects
• Importing Objects
• Accessing the MDL Utilities
• Metadata Loader Log File
• Importing Metadata from Previous Versions
• Upgrading the Repository
• Lesson Agenda
• Manage Life-Cycle Changes
• Managing Metadata Changes by Using Snapshots
• Creating Metadata Snapshot
• Using Change Manager
• Exporting and Importing Snapshots
• Quiz
• Lesson Agenda
• Version Management of Design Objects
• Version Management: Using Generation Code Comments
• Version Management: Using Annotations in Export/Import
• Version Management: Using Collections and Snapshots to Version-Control
• Quiz
• Practice 12-1 Overview: Managing Metadata Changes, Code, and MDL File Versions
• Lesson Agenda
• Warehouse Builder Users
• Warehouse Builder User Considerations
• Graphical UI for Security Management
• Workspace Users Roles and Privileges
• EVERYONE Role
• ADMINISTRATOR Role
• Object-Level Security
• Access Privilege Policies
• Setting Security Parameters
• Quiz
• Summary
• Practice 12-2 Overview: Managing Security
• Objectives
• Lesson Agenda
• Business Justification: Tools Integration
• Integrating with OBI EE
• Integration with OBI SE (Discoverer)
• Lesson Agenda
• Transfer BI Metadata to OBI EE Server
• Setting Up the UDML File Location
• Creating a New Catalog Folder
• Deriving the BI Metadata (OBI EE)
• Deriving the BI Metadata
• Deploying the BI Module
• Converting the UDML File for OBI EE
• Oracle BI Admin and Answers Tool
• Quiz
• Summary

Practice 13-1 Overview: Derive, Deploy, and Transfer Business Intelligence Metadata into OBI

• EE Server
• Practice 13-2Overview: Invoking theDemo titled “ReportingUsing theOBI EE Answers”
• E
• Objectives B-2
• Perception: Power and Complexity of OWB Challenges New Users B-3
• Requirement: Harnessing OWB Power and Complexity for New Users B-4

Solution: OWB “Experts” Give Directed Guidance and Knowledge Management B-5

• Major Benefits of Experts B-6
• Profile of Designers and Users of Experts B-7
• Framework of Experts B-8
• Task Sequence: No User Complexity B-9
• Creating an Expert B-10
• Defining an Expert B-11
• Expert Editor B-12
• Defining the Tasks B-13
• Starting an Expert B-14
• Creating Your Own Custom Dialog B-15
• Use Cases B-16
• Scenario: ROLAP to MOLAP in Five Easy Steps B-17
• ROLAP to MOLAP in Five Easy Steps B-18
• Scenario: Expert for Creating External Table B-19
• Summary B-20
• Objectives C-2
• Mandatory Reading C-3
• Collecting Information Before Contacting Oracle Support C-4
• Sequence Used by Oracle Support Representatives to Process Calls C-6
• Timing Your Service Request C-7
• Activating Debugging and Logging for Full Java Debug Trace C-8
• Activating Debugging and Tracing C-9
• Activating Debugging and Logging C-10
• Activating Tracing C-11
• Using the service_doctor.sql Script C-12

Using SQL*Plus Scripts to Test Availability of the Control Center Service C-13

• Killing a Job If a Mapping Hangs C-14
• Troubleshooting Heterogeneous Sources and Targets C-15
• Troubleshooting and Diagnosing Errors in Control Center Agent (CCA) C-16
• Encouraging Designers to Use the Mapping Debugger C-18
• Run-Time Views and Utilities C-19
• Public Run-Time Views C-20
• Public Run-Time Utilities C-21
• Online Warehouse Builder Resources C-25
• Summary C-26
• Preface
• I
• Introduction
• Objectives I-2
• Course Objectives I-3
• Agenda: Day 1 I-4
• Agenda: Day 2 I-5
• Appendices I-6
• Questions About You I-7
• Summary I-8
• 1 Administrative Tasks in Warehouse Builder
• Objectives
• Lesson Agenda
• Enterprise ETL License Extends Core In-Database ETL
• Scenario Requiring Multiple Configurations
• Loose Coupling of Metadatawith Physical Implementations
• Each Configuration Records the Physical Settings of Its Target Environment
• Multiple Named Configurations: Why and How
• Deploying a Selected Configuration
• Configurations, Control Centers, and Locations
• Creating Additional Configurations and Control Centers
• Setting the Active Configuration
• Configuring Objects Across All Configurations
• Cloning a Database to Create Identical Dev-QA-Prod Systems
• Practice 1-1 Overview: Using Multiple Named Configurations
• Lesson Agenda
• Configuration Templates
• Creating a New Configuration Template
• Associating a Configuration Template with a Configuration
• Overriding Default Configuration Settings
• Practice 1-2 Overview: Using Configuration Templates
• Lesson Agenda
• Control Center Service Failover on RAC
• RAC Deployment
• iii
• Supported and Unsupported RAC Features
• Steps for Setting Up OWB in a RAC Environment
• Lesson Agenda
• Scheduling Concepts
• Creating a Schedule Module
• Creating a Schedule
• Create Schedule Wizard: Name and Description
• Create Schedule Wizard: Start and End Time
• Create Schedule Wizard: Frequency and Repeat Interval
• Monthly Schedule
• Editing a Schedule
• Run One Week into the Next Quarter
• Associating an Executable Object with a Schedule
• Assigning a Schedule to an Executable Object
• Quiz
• Summary
• Practice 1-3 Overview: Creating a Schedule
• 2 Managing Metadata
• Objectives
• Lesson Agenda
• Managing Metadata by Using Lineage and Impact Analysis Diagrams
• Integrated Lineage and Impact Analysis
• Invoking Lineage and Impact Analysis
• Diagram-Based Analysis
• Lineage and Impact Analysis Within the Same Diagram
• Lineage Analysis
• Lesson Agenda
• Using the Change Propagation Dialog
• Lesson Agenda
• User-Defined Properties (UDPs)
• User-Defined Icons
• Creating Icon Sets
• Icon Set Usage
• User-Defined Objects
• UDOs Participate in Lineage and Impact Analysis
• Quiz
• Practice 2-1 Overview: Managing Metadata
• Lesson Agenda
• What Is Pluggable Mapping
• Using Pluggable Mappings
• iv
• Example of Pluggable Mapping
• Advantages of Pluggable Mappings
• Creating a Pluggable Mapping
• Provide the Pluggable Mapping Name
• Define the Input and Output Signature Groups
• Specify the Input Signature
• Specify the Output Signature
• The Pluggable Mapping Editor Opens
• Add the Source Tables
• Add a Joiner Operator to the Pluggable Mapping
• Define the Groups for the Joiner Operator
• Map the Inputs to the Joiner Operator
• Map the Joiner Outputs to the Output Signature
• Practice 2-2 Overview: Creating a Pluggable Mapping
• Lesson Agenda
• Advanced Activity Types in Process Flows
• Using Variables and Parameters in Process Flows
• Passing Data Between Activities by Assigning into Local Variables
• Child Process Using Input and Output Parameters
• New OWB 11g Release 2 Activity Types in Process Flows
• Creating Activity Templates
• Assign Activity
• For Loop Activity
• Route and While Loop Activities
• While Loop: Example
• Notification Activity
• Set Status Activity
• Data Auditor
• Native Relational Object Support
• Heterogeneous Predefined SQL Transformations
• Summary
• 3 Accessing Non-Oracle Sources
• Objectives
• Lesson Agenda
• Extensible Framework of OWB 11g Release 2
• What Is a Code Template?
• An Open Extensibility Framework
• Benefits of Extensible Code Templates
• What Is a Code Template Mapping?
• Typical Usage of Code Templates
• v
• Lesson Agenda
• Location of Seeded Code Templates
• Quiz
• Lesson Agenda
• Imported CT Opened in OWB CT Editor
• Practice 3-1 Overview: Examine Built-In and Imported Code Templates
• Lesson Agenda
• Creating New Code Templates
• Lesson Agenda
• What Is an Integration Platform
• Define New Integration Platforms in OWB
• Setting Up Platform Properties
• Creating Data Type Mappings
• Adding Connectivity
• Using Code Templates in Mappings
• Hybrid Design: Combine Template Extract with PL/SQL
• Quiz
• Summary
• 4 Designing Mappings with the Oracle Data Integration Enterprise Edition
• License
• Objectives
• Lesson Agenda
• Traditional Versus CT Mappings
• Integrate CTs into an OWB Mapping Framework
• Additional Tasks for Using CTs in Mappings
• Lesson Agenda
• Execution Units in a CT Mapping
• Execution View Versus Logical View
• Default Configuration and Its Execution View
• Alternate Configuration and Its Execution View
• Mechanics of Template-Based Mappings
• New Properties in the Location Dialog Box Support Code Template Mappings
• Location Information for CT-Based Mappings
• Staging Table Prefixes
• Create a List of Favorite Operators
• Quiz
• Lesson Agenda
• Convert a Classic Mapping to a CT Mapping That Utilizes Data Pump
• Copy and Paste a Classic Mapping to a Template Mapping Node
• Execution View of a Code Template Version of the Mapping
• vi
• Assign the USE_DATA_PUMP Code Template
• Lesson Agenda
• Reminder of CT-Based Mapping Architecture
• OWB Topology with CCA on Oracle Target Server
• Privileges for J2EE Users and Agents
• 1. Starting the Default Agent
• 2. Setting Up Credentials for Default Agent
• 3. Mapping Modules and Agent Locations
• Agents Need Information About Locations
• Installing the OWB Agent on Another System
• OWB Infrastructure with Agent on Oracle Target
• OWB Infrastructure with Agent on Remote Source
• Monitoring Deployments with HRAB Reports
• Quiz
• Summary
• Practice 4-1 Overview: Using Code Templates in Mappings
• 5 Right-Time Data Warehousing with OWB
• Objectives
• Lesson Agenda
• What Is Meant by Real-Time Data Warehousing
• What Refresh Frequency Does OWB Support?
• OWB Supports Multiple Loading Approaches
• Trickle Feed: Start/Stop
• Mapping Can Consume or Produce Data to And from Queues
• OWB Supports Two Types of Advanced Queues
• Streams Queues Can Stage Logical Change Records (LCR) or Non-LCR
• Messages
• Consume from a Queue
• Produce Information into a Queue
• Queue-to-Queue Propagation
• Lesson Agenda
• Building a Trickle Feed Mapping
• Building a Trickle Feed Mapping: Queue Operator Wizard
• Select the Message Queue as Driver
• Select Batch or Real-Time Source
• Select Message Type
• Add Object Type Expander for Payload
• Add the Mapping Target Table
• Execution Type Automatically Set to Trickle
• Define the Streams Administrator User
• vii
• Configure the Mapping Real-Time Parameters
• Practice 5-1 Overview: Building a Trickle Feed Mapping
• Lesson Agenda
• Change Data Capture Framework
• Simplified CDC Mapping Steps
• Choose How to Perform CDC
• Select Tables Upon Which to Perform CDC
• Start the CDC Capture
• Define Subscribers
• Define Mapping to Consume Changes
• Define Execution Units and Deploy
• Using Web Services to Administer CDC: Select Run-Time Services
• Using Web Services to Administer CDC: Edit the Web Service
• Quiz
• Summary
• Practice 5-2 Overview: Steps for Using CDC Code Templates for CDC
• 6 Defining Relational Models
• Objectives
• Lesson Agenda
• Lesson Scenario
• Classifying the Data
• Dimension Tables
• Determining Granularity
• Dimensional Design Using OWB
• Defining Dimensions
• Creating a Dimension by Using the Wizard
• Create Dimension Wizard: Name and Description
• Create Dimension Wizard: Storage Type
• Create Dimension Wizard: Dimension Attributes
• Create Dimension Wizard: Levels
• Create Dimension Wizard: Level Attributes
• Create Dimension Wizard: Data Policy
• Create Dimension Wizard: Pre Create Settings
• Create Dimension Wizard: Dimension Creation Progress
• Continuing with the Dimension Editor
• Configuring a Dimension
• Quiz
• Practice 6-1: Create a Dimension by Using the Wizard
• Lesson Agenda
• Creating or Editing a Dimension by Usingthe Dimension Editor
• viii
• Invoking the Dimension Editor
• Specifying Product Name and Roles
• Selecting the Relational Storage Type
• Defining Attributes
• Defining Levels and Assigning Level Attributes
• Defining a Hierarchy of Levels
• Specifying Slowly Changing Dimension Type
• Relational Implementation: Star
• Relational Implementation: Snowflake
• Binding
• Binding Dimension Attributes to the Implementation Table
• Unbinding
• Dimension Details: Data Viewer Tab
• Quiz
• Practice 6-2: Create a Dimensionby Using the Editor
• Lesson Agenda
• Creating a Time Dimension by Using the Create Time Dimension Wizard
• Create Time Dimension Wizard:Name and Description
• Specify Data Storage Type
• Specify the Range of Data to Be Stored
• Select Calendar Hierarchy and Hierarchy Levels
• Pre Create Settings Are Displayed
• Progress Panel Is Displayed
• Create Time Dimension Wizard: Summary
• Practice 6-3: Create a TIMES Dimension by Using the Create Time Dimension
• Wizard
• Lesson Agenda
• Defining a Cube by Using the Wizard
• Create Cube Wizard: Name and Description
• Select the Storage Type
• Specify the Dimensions of the Cube
• Define the Measures
• Create Cube Wizard: Summary
• Editing a Cube
• Assigning Roles
• Configuring Cubes
• Lesson Agenda
• Designing ETL Mappings
• Mapping to Load the PRODUCTS Dimension
• Mapping to Load the SALES Cube
• ix
• Generating the Mappings
• Summary
• Practice 6-4: Create a Cubeby Using the Editor
• 7 More Relational Dimensional Modeling
• Objectives
• Lesson Agenda
• Initial Versus Incremental Loads
• Two Categories of DW Updating: Data and Metadata
• Preserving History WhileUpdating Fact Data
• Two Ways to Refresh the Fact Table
• Capturing Changed Data for Refresh
• Wholesale Data Replacement
• Comparison of Database Instances
• Time and Date Stamping
• Database Triggers
• Using a Database Log
• Verdict
• Applying the Changes to Data
• Overwriting a Record
• Adding a New Record
• Adding a Current Field
• Maintaining History
• History Preserved
• Dimensions and Cubes Automatically Handle Update Via MERGE
• Three Refresh Scenarios for Refreshing Target Tables
• Target Uses a Sequence-Generated Key
• Change “Match by Constraint” to “No Constraints”
• Setting Attribute Propertiesfor Synthetic Keys
• Setting Loading Properties
• Update Operation Conditions
• Choosing the DML Load Type
• Specifying an Update Target Condition
• CDC Template Mappings: Another Method for Updating Changed Data
• Quiz
• Lesson Agenda
• The Challenge of Managing Orphans
• How OWB Manages Orphans
• Lesson Agenda
• ROLAP Implementation of Dimensional Objects
• Support for Cube-Organized Materialized Views
• x
• Configuring the Cube
• Storage Type: ROLAP with CUBE MVs
• Using Compressed Cube Technology with Sparse Dimensions
• View the Code After Deploying Dimensions and Cube
• Examining the Cube in Analytic Workspace Manager
• Use SQL Developer to Test Queries with and Without Query Rewrite
• Execution Plan Without Query Rewrite
• Execution Plan with Query Rewrite
• Lesson Agenda
• What Is a Slowly Changing Dimension?
• Types of Slowly Changing Dimensions
• Type 1 SCD: Does Not Store History
• Type 2 SCD: Preserves Complete History
• Type 3 SCD: Stores Onlythe Previous Value
• Creating a Type 2 SCD by Usingthe Dimension Editor
• Applying the Two Change-Tracking Attributes to the Lowest Level
• Creating a Type 2 SCD
• Binding the Attribute to Its Implementation Table
• Synchronize Mapping That Loads Type 2 SCD
• Creating a Type 2 SCD by Using the Wizard
• Dimension Operator in a Mapping
• Creating a Type 3 SCD
• Attributes in a Type 3 SCD
• Quiz
• Summary
• Practice 7-1: Creating a Type 2 Slowly Changing Dimension
• 8 Modeling Multidimensional OLAP Dimensions and Cubes
• Objectives
• Lesson Agenda
• What Is OLAP?
• Examining an OLAP Question
• Lesson Agenda
• Multidimensional Data Types
• Implementing a Dimensional Data Model with Multidimensional Data Types
• Dimensional Model
• Measures
• Measure Types
• Example of Measures in a Report
• Dimensions
• Example of Dimensions in a Report
• xi
• Quiz
• Hierarchies
• Hierarchy: Example
• Levels
• Types of Hierarchy
• Attributes
• Attributes: Examples
• Dimensional Model Summarized
• Lesson Agenda
• Analytic Workspace
• Analytic Workspace: Creation and Maintenance Privileges
• OLAP DML
• Lesson Agenda
• Tools to Build an Analytic Workspace
• Quiz
• Lesson Agenda
• Dimensional Modeling
• Enabling OLAP Solutions
• Storage Management
• Dimensional Modeling Using OWB
• Hierarchy Support: Value-Based
• Create a Value-Based Hierarchy
• Lesson Agenda
• Calculated Measures
• OWB Calculated Measures
• Generating Calculated Measures
• Sparsity
• Compress Cube
• Partition Cube
• Cost-Based Aggregation
• Level-Based Aggregation
• Lesson Agenda
• Differences Between OLAP and Relational Loading
• No Relational Tables to Bind
• Partially Predefined OLAP Module
• Examine the Predefined Dimensions and Mappings
• Define the Sales Cube
• Create an OLAP Target User
• Specify Whether to Create a Location
• Location Created
• Associate the Modul