Leveraging DB2 Data Warehouse Edition for Business Intelligence

Book description

In this IBM Redbooks publication we describe and discuss DB2 Data Warehouse Edition (DWE) Version 9.1, a comprehensive platform offering with functionality to build a business intelligence infrastructure for analytics and Web-based applications, and best practices for deployment. DB2 DWE integrates core components for data warehouse construction and administration, data mining, OLAP, and InLine Analytics and reporting. It extends the DB2 data warehouse with design-side tooling and runtime infrastructure for OLAP, data mining, InLine Analytics, and intra-warehouse data movement and transformation, on a common platform based on DB2 and WebSphere. The platform pillars are based on the technology of DB2, Rational Data Architect (for physical data modeling only), the SQL Warehousing Tool, Intelligent Miner, DB2 Cube Views, and Alphablox. DWE includes an Eclipse-based design environment, DWE Design Studio, that integrates the DWE products (with the exception of Alphablox and Query Patroller) with a common framework and user interface. The new SQL Warehousing Tool enables visual design of intra-warehouse, table-to-table data flows and control flows using generated SQL. DB2 Alphablox is the tool for developing custom applications with embedded analytics-based visual components. DWE enables faster time-to-value for enterprise analytics, while limiting the number of vendors, tools, skill sets and licenses required.

Table of contents

  1. Notices
    1. Trademarks
  2. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  3. Chapter 1: Information warehousing for business insight
    1. Current business challenges
    2. Information as a service
    3. Embedding analytics into business processes
    4. From data warehouse to information warehouse
    5. An integrated business intelligence framework
    6. The DB2 Business Intelligence solution framework
  4. Chapter 2: DB2 DWE - A technical overview
    1. DWE architecture
      1. DWE Design Studio (1/2)
      2. DWE Design Studio (2/2)
      3. InLine Analytics using DWE Alphablox
      4. DWE production environment
      5. DB2 Query Patroller
    2. DWE topology
  5. Chapter 3: The DB2 DWE Design Studio
    1. The Eclipse platform
    2. Introduction to DB2 DWE Design Studio
      1. The workspace
      2. Projects and the local file system
      3. The Welcome page
    3. The DB2 DWE Design Studio Workbench
      1. Perspectives
      2. Editors
      3. Views (1/2)
      4. Views (2/2)
      5. Common tasks (1/2)
      6. Common tasks (2/2)
      7. Team component
  6. Chapter 4: Developing the physical data model
    1. Physical data model
    2. Creating the physical data model
      1. From a template
      2. From an existing database
      3. From DDL
      4. From the Database Explorer
    3. Working with diagrams
      1. Creating a diagram
      2. Using the Diagram Editor
    4. Editing physical data models
      1. Using the Data Project Explorer
    5. Model analysis
    6. Deploying the data model
      1. Using Design Studio to deploy a physical data model
      2. Using the Admin Console to deploy a physical model
    7. Maintaining model accuracy
      1. Comparing objects within the physical data model
      2. Visualizing differences between objects
      3. Synchronization of differences
      4. Impact analysis
    8. Summary
  7. Chapter 5: Enabling OLAP in DB2 UDB
    1. Implementing OLAP
      1. OLAP architectures
      2. DB2 UDB and OLAP
    2. DWE OLAP
      1. DWE OLAP metadata
      2. Materialized Query Tables (MQTs)
      3. Optimization Advisor
    3. Modeling OLAP with OLAP Center
      1. Migrating from OLAP Center to Design Studio
    4. Modeling OLAP with DWE Design Studio
      1. Database Explorer
      2. Properties view
      3. Data Project Explorer view
      4. Creating the CVSAMPLE sample database
      5. Preparing a DB2 UDB database for DWE OLAP
      6. Creating a new data project
      7. Creating a new physical data model
      8. Quick Start Wizard
      9. Defining cube models
      10. Defining the fact table and measures
      11. Defining dimensions
      12. Defining joins
      13. Defining attributes
      14. Defining levels
      15. Defining hierarchies
      16. Defining cubes
      17. Analyze OLAP objects for validity
    5. Deploying objects to DB2 UDB databases
      1. Generate DDL
      2. Compare and Sync Editor and Delta DDL
      3. DWE OLAP Optimization Advisor (1/2)
      4. DWE OLAP Optimization Advisor (2/2)
    6. DWE OLAP optimization cycle
    7. Metadata exchange
      1. Import
      2. Export
  8. Chapter 6: Data movement and transformation
    1. DWE SQL Warehousing Tool (SQW)
      1. SQW overview
      2. Architecture
      3. SQW warehouse application life cycle
      4. Source, target, and execution databases
      5. Setting up a data warehouse project
    2. Developing data flows
      1. Defining a data flow
      2. Data flow editor
      3. Data flow operators (1/9)
      4. Data flow operators (2/9)
      5. Data flow operators (3/9)
      6. Data flow operators (4/9)
      7. Data flow operators (5/9)
      8. Data flow operators (6/9)
      9. Data flow operators (7/9)
      10. Data flow operators (8/9)
      11. Data flow operators (9/9)
      12. Subflows
      13. Validation and code generation
      14. Testing and debugging a data flow
      15. A complete data flow
    3. Developing control flows
      1. Defining a control flow
      2. Control flow editor
      3. Control flow operators
      4. Validation and code generation
      5. Testing and debugging a control flow
    4. Variables in data flows and control flows
    5. Preparing for deployment
      1. Defining warehouse applications
      2. Defining application profiles
      3. Generating code and packaging for deployment
    6. Integrating with IBM WebSphere DataStage
      1. Overview of IBM WebSphere DataStage
      2. Key differences between SQW and DataStage
      3. Integrating DataStage and SQW
      4. Conclusion
  9. Chapter 7: DWE and data mining
    1. Data mining overview
      1. Discovery data mining
      2. Predictive data mining
    2. The data-mining process
      1. Understanding the business problem
      2. Understanding the data model
      3. Identifying the data mining approach
      4. Extracting and preparing the data
      5. Building the data mining model
      6. Interpreting and evaluating the data mining results
      7. Deploying the data mining results
    3. Embedded data mining
    4. DWE data-mining components
      1. Modeling
      2. Visualization
      3. Scoring
    5. Data mining in the DWE Design Studio
      1. Mining flows
      2. Database enablement
      3. Data exploration (1/2)
      4. Data exploration (2/2)
      5. Data mining operators (1/3)
      6. Data mining operators (2/3)
      7. Data mining operators (3/3)
      8. Building a mining flow (1/3)
      9. Building a mining flow (2/3)
      10. Building a mining flow (3/3)
      11. Validating and executing a mining flow
      12. Visualizing models and test results (1/4)
      13. Visualizing models and test results (2/4)
      14. Visualizing models and test results (3/4)
      15. Visualizing models and test results (4/4)
      16. Scoring with other PMML models
      17. Managing data mining models
  10. Chapter 8: InLine Analytic Applications
    1. DB2 Alphablox
      1. The architecture
      2. Enabled applications (1/2)
      3. Enabled applications (2/2)
      4. Deploying DB2 Alphablox
      5. Services
      6. Blox server/client structure
    2. Integration with DWE OLAP
      1. Metadata bridge
      2. DB2 Alphablox Relational Cubing Engine
      3. Application performance
    3. Developing an application
      1. Creating a data source
      2. DB2 Alphablox Cube
      3. Developing JSP code for the DB2 Alphablox application (1/2)
      4. Developing JSP code for the DB2 Alphablox application (2/2)
    4. Integration with DWE Mining
    5. Integration with portal applications
  11. Chapter 9: Deploying and managing DWE solutions
    1. The DWE Administration Console
      1. Functionality provided
      2. Architecture
      3. Deploying in a runtime environment
      4. Security considerations
      5. General administration tasks
      6. Locating and using diagnostics
    2. Deploying the physical data model
      1. Deployment using the DWE Design Studio
      2. Deployment using the DWE Administration Console
      3. Deployment using native DB2 functionality
    3. DWE SQL Warehousing
      1. An overview of the SQL Warehousing components
      2. Runtime architecture of DWE SQL Warehousing (1/3)
      3. Runtime architecture of DWE SQL Warehousing (2/3)
      4. Runtime architecture of DWE SQL Warehousing (3/3)
      5. Managing warehouse resources
      6. Deploying and managing warehouse applications (1/2)
      7. Deploying and managing warehouse applications (2/2)
      8. Warehouse processes (1/4)
      9. Warehouse processes (2/4)
      10. Warehouse processes (3/4)
      11. Warehouse processes (4/4)
      12. Warehousing diagnostics (1/2)
      13. Warehousing diagnostics (2/2)
    4. Managing the OLAP functionality
      1. Creating the OLAP environment
      2. Optimizing the OLAP environment
      3. Viewing the OLAP environment
    5. Data mining
      1. Data mining modules
      2. Deploying and managing the data mining models
      3. Caching the mining model for performance
    6. Managing DB2 Alphablox within DWE
      1. Defining DB2 Alphablox
      2. DB2 Alphablox Administration Console (1/2)
      3. DB2 Alphablox Administration Console (2/2)
  12. Chapter 10: DB2 UDB Data Warehouse features
    1. DB2 UDB ESE
    2. Architecture
    3. Balanced Configuration Unit
    4. Partitioning
      1. Hash partitioning (1/2)
      2. Hash partitioning (2/2)
      3. Multidimensional data clustering (MDC)
      4. Range partitioning
      5. Using all partitioning schemes together
      6. DB2 partitioning options summary
    5. DB2 performance features
      1. Materialized query tables (MQT)
      2. Replicated tables
    6. Compression
      1. Row compression
      2. Other forms of compression in DB2
    7. Self-tuning memory
    8. DB2 Design Advisor
    9. High availability
  13. Chapter 11: Managing SQL queries with DWE
    1. DB2 Query Patroller
      1. Query interception and management
      2. Query Patroller thresholds
      3. Using a query class
      4. Historical analysis
    2. Architecture and components
      1. The server
      2. Query Patroller Center
      3. Command-line support
      4. Components in a DWE environment
    3. Installing and configuring DB2 Query Patroller
      1. Installing the Query Patroller server
      2. Configuring the Query Patroller server after installation
      3. Installing the Query Patroller client
    4. Best practices for DB2 Query Patroller
      1. Initial configuration to capture the query workload
      2. Using the historical analysis data
      3. Additional best practices
    5. DB2 Query Patroller and the DB2 Governor
      1. DB2 Governor
      2. Differences between the Governor and Query Patroller
      3. Using the Governor and Query Patroller together
  14. Appendix A: IBM data warehousing - complementary software
    1. Data Modeling
    2. Application development
    3. Enterprise Extract Transform Load (ETL)
    4. Enterprise Information Integration (EII)
    5. Enterprise Application Integration (EAI)
    6. Data quality
    7. Database Tools
    8. Storage management
    9. Portal and application server
    10. Search tools
    11. Master data management solutions
  15. Appendix B: DWE Admin - problem determination example
  16. Glossary (1/2)
  17. Glossary (2/2)
  18. Abbreviations and acronyms
  19. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
    5. Help from IBM
  20. Index (1/3)
  21. Index (2/3)
  22. Index (3/3)
  23. Back cover

Product information

  • Title: Leveraging DB2 Data Warehouse Edition for Business Intelligence
  • Author(s): Chuck Ballard, Angus Beaton, David Chiou, Janardhan Chodagam, Meridee Lowry, Andrew Perkins, Richard T. Phillips, John Rollins
  • Release date: November 2006
  • Publisher(s): IBM Redbooks
  • ISBN: None