Oracle® Data Warehousing and Business Intelligence Solutions

Oracle® Data Warehousing and Business Intelligence Solutions

Released January 2007
Publisher(s): Wiley
ISBN: 9780471919216

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Up-to-date, comprehensive coverage of the Oracle database and business intelligence tools

Written by a team of Oracle insiders, this authoritative book provides you with the most current coverage of the Oracle data warehousing platform as well as the full suite of business intelligence tools. You'll learn how to leverage Oracle features and how those features can be used to provide solutions to a variety of needs and demands. Plus, you'll get valuable tips and insight based on the authors' real-world experiences and their own implementations. Avoid many common pitfalls while learning best practices for:

  • Leveraging Oracle technologies to design, build, and manage data warehouses

  • Integrating specific database and business intelligence solutions from other vendors

  • Using the new suite of Oracle business intelligence tools to analyze data for marketing, sales, and more

  • Handling typical data warehouse performance challenges

  • Uncovering initiatives by your business community, security business sponsorship, project staffing, and managing risk

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. How This Book Is Organized
      1. Part I: Oracle Business Intelligence Defined
        1. Chapter 1: Oracle Business Intelligence
        2. Chapter 2: Oracle's Transactional Business Intelligence
        3. Chapter 3: Introduction to Oracle Data Warehousing
        4. Chapter 4: Choosing a Platform
      2. Part II: Custom-Built Data Warehousing Solutions
        1. Chapter 5: Designing for Usability
        2. Chapter 6: Business Intelligence Tools
        3. Chapter 7: Data Loading
        4. Chapter 8: Managing the Oracle Data Warehouse
        5. Chapter 9: Data Warehouse Performance Tuning and Monitoring
      3. Part III: Best Practices
        1. Chapter 10: Scoping the Effort and an Approach for Success
        2. Chapter 11: Understanding Business Needs
        3. Chapter 12: Justifying Projects and Claiming Success
    3. Illustrations in the Text
    4. From Here
  6. I. Oracle Business Intelligence Defined
    1. 1. Oracle Business Intelligence
      1. 1.1. Business Intelligence and Transactional Applications
        1. 1.1.1. Daily Business Intelligence
        2. 1.1.2. Balanced Scorecard
        3. 1.1.3. Enterprise Planning and Budgeting
        4. 1.1.4. Activity-Based Management
      2. 1.2. Oracle Integration Components Enabling Business Intelligence
        1. 1.2.1. Data Hubs
        2. 1.2.2. Business Activity Monitoring
        3. 1.2.3. BPEL Process Manager
        4. 1.2.4. Enterprise Messaging Service
      3. 1.3. Custom Data Warehouse Solutions
        1. 1.3.1. The Role of the Oracle Database
        2. 1.3.2. Oracle Warehouse Builder
        3. 1.3.3. Oracle Business Intelligence Standard Edition
          1. 1.3.3.1. Discoverer
          2. 1.3.3.2. Reports
        4. 1.3.4. Oracle Business Intelligence Enterprise Edition
        5. 1.3.5. BI (XML) Publisher
        6. 1.3.6. Oracle Portal
        7. 1.3.7. Spreadsheet Add-ins
        8. 1.3.8. Building Custom Business Intelligence Applications
      4. 1.4. Emerging Trends
    2. 2. Oracle's Transactional Business Intelligence
      1. 2.1. Transactional Business Intelligence
        1. 2.1.1. Business Terminology
          1. 2.1.1.1. Corporate Performance Management
          2. 2.1.1.2. Key Performance Indicators
          3. 2.1.1.3. Dashboard
      2. 2.2. Oracle's Daily Business Intelligence
        1. 2.2.1. How DBI Works
        2. 2.2.2. Varieties of DBI
          1. 2.2.2.1. Daily Business Intelligence for Compliance
          2. 2.2.2.2. Daily Business Intelligence for Customer Support
          3. 2.2.2.3. Daily Business Intelligence for Depot Repair
          4. 2.2.2.4. Daily Business Intelligence for Field Service
          5. 2.2.2.5. Daily Business Intelligence for Financials
          6. 2.2.2.6. Daily Business Intelligence for Human Resources
          7. 2.2.2.7. Daily Business Intelligence for Interaction Center
          8. 2.2.2.8. Daily Business Intelligence for iStore
          9. 2.2.2.9. Daily Business Intelligence for Marketing (DBI-M)
          10. 2.2.2.10. Daily Business Intelligence for Maintenance
          11. 2.2.2.11. Daily Business Intelligence for Procurement
          12. 2.2.2.12. Daily Business Intelligence for Product Lifecycle Management
          13. 2.2.2.13. Daily Business Intelligence for Projects
          14. 2.2.2.14. Daily Business Intelligence for Quoting
          15. 2.2.2.15. Daily Business Intelligence for Sales
          16. 2.2.2.16. Daily Business Intelligence for Service Contracts
          17. 2.2.2.17. Daily Business Intelligence for Supply Chain
      3. 2.3. Balanced Scorecards
        1. 2.3.1. Oracle Balanced Scorecard Structure
        2. 2.3.2. OBSC Architecture
        3. 2.3.3. Creating an Oracle Balanced Scorecard
      4. 2.4. Data Hubs
        1. 2.4.1. The Oracle Customer Data Hub
        2. 2.4.2. How Data Hubs Work
        3. 2.4.3. Other Oracle Data Hubs
      5. 2.5. Is Transactional Business Intelligence Enough?
    3. 3. Introduction to Oracle Data Warehousing
      1. 3.1. Oracle Data Warehousing Basics
        1. 3.1.1. Oracle Database Analysis and Schema Considerations
          1. 3.1.1.1. Star Joins
          2. 3.1.1.2. Materialized Views
          3. 3.1.1.3. Bit-map Indexes
          4. 3.1.1.4. SQL Analytics
          5. 3.1.1.5. OLAP Option
          6. 3.1.1.6. Data Mining Option
          7. 3.1.1.7. Storage and Retrieval of Multiple Datatypes including XML
        2. 3.1.2. Managing an Oracle-based Data Warehouse
          1. 3.1.2.1. Enterprise Manager and Grid Control
          2. 3.1.2.2. Database Self-Tuning and Management
          3. 3.1.2.3. Highly Available Database
          4. 3.1.2.4. Oracle Database Partitioning Option
          5. 3.1.2.5. Security
      2. 3.2. Where to Start?
        1. 3.2.1. Oracle/PeopleSoft EPM
        2. 3.2.2. Oracle/Siebel Business Analytics Applications
        3. 3.2.3. Choosing Completely Custom
    4. 4. Choosing a Platform
      1. 4.1. Scaling Up Platforms Versus Scaling Out
        1. 4.1.1. Hardware Platforms
        2. 4.1.2. Cost Considerations
        3. 4.1.3. Availability Considerations
          1. 4.1.3.1. Fault Tolerant Disk Configurations
          2. 4.1.3.2. System Downtime and Fault Tolerance
        4. 4.1.4. Manageability Considerations
      2. 4.2. Sizing the Platform
        1. 4.2.1. Information Needed for Warehouse Hardware Sizing
          1. 4.2.1.1. Extraction, Transformation, and Loading
          2. 4.2.1.2. Reference Configurations
        2. 4.2.2. Benchmarking
          1. 4.2.2.1. Benchmark Configuration Considerations
          2. 4.2.2.2. Unsuccessful Benchmarks
        3. 4.2.3. Sizing Hardware for Business Intelligence Tools
      3. 4.3. Your Strategy
  7. II. Custom-Built Data Warehousing Solutions
    1. 5. Designing for Usability
      1. 5.1. Approaches for Design
        1. 5.1.1. Key Design Considerations
          1. 5.1.1.1. User Community
          2. 5.1.1.2. Data Volumes and Granularity
          3. 5.1.1.3. Design and Volatility
          4. 5.1.1.4. User Inquiries and Analysis
          5. 5.1.1.5. Data Architecture
          6. 5.1.1.6. Performance and Service Levels
        2. 5.1.2. Features for Design – Enhancing Performance
          1. 5.1.2.1. Partitioning Approach
          2. 5.1.2.2. Indexing Approach
            1. 5.1.2.2.1. B-Tree Indexes
            2. 5.1.2.2.2. Bitmap Indexes
            3. 5.1.2.2.3. Bitmap Join Indexes
          3. 5.1.2.3. Parallelism Approach
            1. 5.1.2.3.1. Parallel Data Loading
            2. 5.1.2.3.2. Parallel DDL
            3. 5.1.2.3.3. Parallel DML
            4. 5.1.2.3.4. Parallel Query
          4. 5.1.2.4. Summarization Approach and Query Optimization
        3. 5.1.3. Business Scenario
        4. 5.1.4. Normalized Design
          1. 5.1.4.1. Business Scenario: Normalized Design
        5. 5.1.5. Dimensional Design
          1. 5.1.5.1. Business Scenario: Dimensional Design
        6. 5.1.6. Hybrid Design
          1. 5.1.6.1. Business Scenario: Hybrid Design
        7. 5.1.7. Online Analytical Processing Design
          1. 5.1.7.1. Business Scenario: Online Analytical Processing Design
        8. 5.1.8. Other Considerations
          1. 5.1.8.1. Business Scenario: Partitioning
          2. 5.1.8.2. Business Scenario: Indexing
          3. 5.1.8.3. Business Scenario: Summarization
      2. 5.2. Selecting the Best Approach
    2. 6. Business Intelligence Tools
      1. 6.1. Oracle Portal and Portal Products
        1. 6.1.1. Using Oracle Portal
        2. 6.1.2. Building and Deploying Oracle Portal and Portlets
          1. 6.1.2.1. Transactional Business Intelligence Portlets
          2. 6.1.2.2. Portlets for Business Process Flows
      2. 6.2. Reporting
        1. 6.2.1. BI/XML Publisher
        2. 6.2.2. Oracle Reports
        3. 6.2.3. Oracle BI Reporting Workbench (Actuate)
      3. 6.3. Ad hoc Query and Analysis
        1. 6.3.1. Business Intelligence Enterprise Edition
          1. 6.3.1.1. Using Oracle BI EE and Oracle Answers
          2. 6.3.1.2. Administering Oracle BI EE
        2. 6.3.2. Discoverer and Business Intelligence Standard Edition
          1. 6.3.2.1. Using Discoverer in Oracle BI SE
          2. 6.3.2.2. Administering Discoverer in Oracle BI SE
        3. 6.3.3. Oracle Spreadsheet Add-ins
          1. 6.3.3.1. Oracle BI Spreadsheet Add-in
          2. 6.3.3.2. Predictive Analytics Add-in
      4. 6.4. Building Business Intelligence Applications
        1. 6.4.1. JDeveloper and BI Beans
        2. 6.4.2. Data Miner
      5. 6.5. Other Tools
    3. 7. Data Loading
      1. 7.1. Oracle Database Data Loading Features
        1. 7.1.1. Embedded ETL in the Oracle Database
        2. 7.1.2. SQL*Loader
        3. 7.1.3. Change Data Capture
        4. 7.1.4. Transportable Tablespaces
        5. 7.1.5. Data Pump
      2. 7.2. Oracle Warehouse Builder
        1. 7.2.1. OWB Packaging
        2. 7.2.2. Typical Steps when using OWB
        3. 7.2.3. ETL Design in OWB
        4. 7.2.4. OWB and Dimensional Models
        5. 7.2.5. The OWB Process Editor
      3. 7.3. Balancing Data Loading Choices
    4. 8. Managing the Oracle Data Warehouse
      1. 8.1. Oracle Enterprise Manager Grid Control
        1. 8.1.1. Database Performance Monitoring
        2. 8.1.2. Database Administration
          1. 8.1.2.1. Creating and Managing Tables and Storage
          2. 8.1.2.2. Creating and Managing Materialized Views
          3. 8.1.2.3. Creating and Managing OLAP Cubes
          4. 8.1.2.4. Managing Partitioning and Database Manageability Impact
          5. 8.1.2.5. Managing XML Database in Oracle
          6. 8.1.2.6. Managing Users and Security
          7. 8.1.2.7. Managing Users and Resource Utilization
        3. 8.1.3. Database Maintenance
          1. 8.1.3.1. Backups and High Availability
          2. 8.1.3.2. Data Movement Maintenance
          3. 8.1.3.3. Software Deployment
        4. 8.1.4. Database Topology
      2. 8.2. Management and Management Options
    5. 9. Data Warehouse Performance Tuning and Monitoring
      1. 9.1. Understanding Performance Challenges
        1. 9.1.1. Who Is Responsible?
        2. 9.1.2. Causes of Poor Performance
          1. 9.1.2.1. Educating the Business
            1. 9.1.2.1.1. Business Processes
            2. 9.1.2.1.2. Data
          2. 9.1.2.2. Hardware
            1. 9.1.2.2.1. Processing Capacity
            2. 9.1.2.2.2. Memory
            3. 9.1.2.2.3. Network Infrastructure
            4. 9.1.2.2.4. Storage Infrastructure
          3. 9.1.2.3. Software
            1. 9.1.2.3.1. Database
            2. 9.1.2.3.2. Business Intelligence Tools and Application Software
          4. 9.1.2.4. Application/Database Design
      2. 9.2. Successful Approaches to Performance Tuning
        1. 9.2.1. Critical Tasks for Performance Tuning Lifecycle
          1. 9.2.1.1. Definition Phase
            1. 9.2.1.1.1. Task: Define Architecture
          2. 9.2.1.2. Requirements Modeling Phase
            1. 9.2.1.2.1. Task: Capacity Plan
            2. 9.2.1.2.2. Task: Define Architecture – (Refined)
            3. 9.2.1.2.3. Task: Others to Consider
          3. 9.2.1.3. Construction Phase
            1. 9.2.1.3.1. Task: Physical Database Design
            2. 9.2.1.3.2. Task: Performance Test Plan
            3. 9.2.1.3.3. Task: Capacity Plan – (Refined)
            4. 9.2.1.3.4. Task: Others to Consider
          4. 9.2.1.4. Production Phase
            1. 9.2.1.4.1. Task: Performance Test Execution
            2. 9.2.1.4.2. Task: Scalability Testing
            3. 9.2.1.4.3. Task: Physical Database Design – (Final)
            4. 9.2.1.4.4. Task: Others to Consider
        2. 9.2.2. Hardware Configuration
          1. 9.2.2.1. Validate Theoretical Throughput
          2. 9.2.2.2. Additional Infrastructure to Validate
            1. 9.2.2.2.1. Server Memory
            2. 9.2.2.2.2. Storage Subsystem
        3. 9.2.3. Software Configuration
          1. 9.2.3.1. Instance Configuration
          2. 9.2.3.2. Automatic Workload Repository
          3. 9.2.3.3. Automatic Database Diagnostic Monitor
          4. 9.2.3.4. Advisory Framework
            1. 9.2.3.4.1. SQL Tuning Advisor
            2. 9.2.3.4.2. SQL Access Advisor
            3. 9.2.3.4.3. Memory Advisor
            4. 9.2.3.4.4. Segment Advisor
            5. 9.2.3.4.5. Other Advisors
        4. 9.2.4. Database Application Design
      3. 9.3. Business Scenario: Tuning Our Sample Solution
        1. 9.3.1. Where To Start
        2. 9.3.2. Enterprise Manager Advisory Framework
          1. 9.3.2.1. Analyze the Environment
          2. 9.3.2.2. Analyze the Application
      4. 9.4. Approaches for Success
  8. III. Best Practices
    1. 10. Scoping the Effort and an Approach for Success
      1. 10.1. Uncovering Key Business Initiatives
        1. 10.1.1. Where to Start
        2. 10.1.2. Information Sources
          1. 10.1.2.1. Public-Facing Sources of Information
          2. 10.1.2.2. Company and Organizational Internal Sources of Information
        3. 10.1.3. What is Important
      2. 10.2. Accountability and Securing Business Sponsorship
        1. 10.2.1. Establish a Steering Committee
        2. 10.2.2. Project Review Board
      3. 10.3. Endorsing a Methodology
        1. 10.3.1. Choices of Methodologies
        2. 10.3.2. A Business-focused Approach
          1. 10.3.2.1. Moscow List for Prioritization
          2. 10.3.2.2. Time box functionality
          3. 10.3.2.3. Standard Deliverables with Supporting Repository
          4. 10.3.2.4. Selecting a Project Duration
      4. 10.4. Staffing the Project
        1. 10.4.1. Organization Structure
          1. 10.4.1.1. Project Management/Portfolio Team
          2. 10.4.1.2. Business/Information Analyst
          3. 10.4.1.3. Data Acquisition Team
          4. 10.4.1.4. Reporting and Analytics Team
          5. 10.4.1.5. Infrastructure and Systems Team
          6. 10.4.1.6. Data Warehouse Architecture Team
        2. 10.4.2. End-User Experience
        3. 10.4.3. Engaging the Business: Education and Training
      5. 10.5. Managing Risk
        1. 10.5.1. Communication — Managing Expectations
        2. 10.5.2. Contingency Allocation
        3. 10.5.3. Financial Risk
        4. 10.5.4. Technology Risk
      6. 10.6. No Place to Shortcut
    2. 11. Understanding Business Needs
      1. 11.1. How Bad Deployment Choices Impact the Business
        1. 11.1.1. Independent Data Marts
        2. 11.1.2. Limited and Inflexible Reporting
        3. 11.1.3. Sources of Information Limited to Internal Data
        4. 11.1.4. Limited Data History
        5. 11.1.5. Lack of Current High-Quality Data
        6. 11.1.6. Limited Growth Flexibility
      2. 11.2. Project Drivers and Business Types
        1. 11.2.1. Financial Companies
          1. 11.2.1.1. Banks and Financial Advisors
          2. 11.2.1.2. Insurance Companies
        2. 11.2.2. Healthcare
          1. 11.2.2.1. Healthcare Providers
          2. 11.2.2.2. Healthcare Payers
        3. 11.2.3. Manufacturing
        4. 11.2.4. Media and Entertainment
        5. 11.2.5. Retail
        6. 11.2.6. Telecommunications
        7. 11.2.7. Other Business Types: Transportation and Utilities
        8. 11.2.8. Educational Institutions
          1. 11.2.8.1. Elementary and Secondary Schools
          2. 11.2.8.2. Higher Education
        9. 11.2.9. Government Agencies
          1. 11.2.9.1. Defense, Military, and Intelligence Agencies
          2. 11.2.9.2. Civilian Agencies, State, and Local Government
      3. 11.3. Developing Scope and Gaining Business Support
    3. 12. Justifying Projects and Claiming Success
      1. 12.1. Concept to Project
        1. 12.1.1. Business Constraints
        2. 12.1.2. Where to Start Justification
      2. 12.2. Measuring Value
        1. 12.2.1. Common Metrics to Measure
          1. 12.2.1.1. Elements of Cost
            1. 12.2.1.1.1. Hardware
            2. 12.2.1.1.2. Software
            3. 12.2.1.1.3. Storage
            4. 12.2.1.1.4. Human Resources
          2. 12.2.1.2. Elements of Value
            1. 12.2.1.2.1. Resource Reallocation
            2. 12.2.1.2.2. Productivity Enhancements
            3. 12.2.1.2.3. Cost Avoidance
            4. 12.2.1.2.4. Revenue Preservation and Enhancement
            5. 12.2.1.2.5. Competitive Advantage
            6. 12.2.1.2.6. Market Presence
        2. 12.2.2. Common Budgeting Techniques
          1. 12.2.2.1. Average Rate of Return
          2. 12.2.2.2. Payback Period
          3. 12.2.2.3. Net Present Value
          4. 12.2.2.4. Internal Rate of Return
          5. 12.2.2.5. Profitability Index
          6. 12.2.2.6. What's the Best Solution?
      3. 12.3. Total Cost of Ownership
        1. 12.3.1. Modeling Total Cost of Ownership
          1. 12.3.1.1. Capital Expenditures
          2. 12.3.1.2. Expensed Purchases
          3. 12.3.1.3. Resources
          4. 12.3.1.4. Analysis Duration
          5. 12.3.1.5. TCO Example
      4. 12.4. Return on Investment
        1. 12.4.1. Modeling Return on Investment
          1. 12.4.1.1. Business Benefits
          2. 12.4.1.2. ROI Example
      5. 12.5. Claiming Success

Product information

  • Title: Oracle® Data Warehousing and Business Intelligence Solutions
  • Author(s):
  • Release date: January 2007
  • Publisher(s): Wiley
  • ISBN: 9780471919216