DB2 Cube Views: A Primer

DB2 Cube Views: A Primer

by Corinne Baragoin, Geetha Balasubramaniam, Bhuvaneshwari Chandrasekharan, Landon DelSordo, Jan B. Lillelund, Julie Maw, Annie Neroda, Paulo Pereira, Jo A. Ramos
Released September 2003
Publisher(s): IBM Redbooks
ISBN: None

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

Business Intelligence and OLAP systems are no longer limited to the privileged few business analysts: they are being democratized by being shared with the rank and file employee demanding a Relational Database Management System (RDBMS) that is more OLAP-aware.

DB2 Cube Views and its cube model provide DB2 the ability to address multidimensional analysis and become an actor in the OLAP world.

This IBM Redbooks publication focuses on the innovative technical functionalities of IBM DB2 Cube Views V8.1 to store multidimensional metadata in DB2 catalog; to build automatically model-based summary tables to speed up query performance; and to provide an advanced API to allow other Business Intelligence partners’ tools to benefit from both metadata exchange and improved query performance.

This book positions the new functionalities and their benefits, so you can understand and evaluate their applicability in your own Business Intelligence and OLAP system environment. It provides information and examples to help you to get started planning and implementing the new functionalities.

Table of contents

  1. Figures (1/3)
  2. Figures (2/3)
  3. Figures (3/3)
  4. Tables
  5. Examples
  6. Notices
    1. Trademarks
  7. Preface
    1. The team that wrote this redbook
    2. Become a published author
    3. Comments welcome
  8. Part 1: Understand DB2 Cube Views
    1. Chapter 1: An OLAP-aware DB2
      1. Business Intelligence and OLAP introduction
        1. Online Analytical Processing
        2. Metadata
      2. DB2 UDB V8.1 becomes OLAP-aware
      3. Challenges faced by DBA’s in an OLAP environment
        1. Manage the flow of metadata
        2. Optimize and manage custom summary tables
        3. Optimize MOLAP database loading
        4. Enhance OLAP queries performance in the relational database
      4. How DB2 can help (1/2)
      5. How DB2 can help (2/2)
        1. Efficient multidimensional model: cube model
        2. Summary tables optimization: Optimization Advisor
        3. Interfaces
      6. Metadata bridges to back-end and front-end tools
    2. Chapter 2: DB2 Cube Views: scenarios and benefits
      1. What can DB2 Cube Views do for you? (1/2)
      2. What can DB2 Cube Views do for you? (2/2)
      3. Feeding metadata into DB2 Cube Views (1/2)
      4. Feeding metadata into DB2 Cube Views (2/2)
        1. Feeding DB2 Cube Views from back-end tools
        2. Feeding DB2 Cube Views from front-end tools
        3. Feeding DB2 Cube Views from scratch
      5. Feeding front-end tools from DB2 Cube Views (1/4)
      6. Feeding front-end tools from DB2 Cube Views (2/4)
      7. Feeding front-end tools from DB2 Cube Views (3/4)
      8. Feeding front-end tools from DB2 Cube Views (4/4)
        1. Supporting MOLAP tools with DB2 Cube Views
        2. Supporting ROLAP tools with DB2 Cube Views
        3. Supporting HOLAP tools with DB2 Cube Views
        4. Supporting bridgeless ROLAP tools with DB2 Cube Views
      9. Feeding Web services from DB2 Cube Views
        1. A scenario
        2. Flow and components
        3. Benefits
  9. Part 2: Build and optimize the DB2 Cube Model
    1. Chapter 3: Building a cube model in DB2
      1. What are the data schemas that can be modeled?
        1. Star schemas
        2. Snowflakes
        3. Star and snowflakes characteristics
      2. Cube model notion and terminology (1/3)
      3. Cube model notion and terminology (2/3)
      4. Cube model notion and terminology (3/3)
        1. Measures and facts
        2. Attributes
        3. Dimensions
        4. Hierarchies
        5. Attribute relationships
        6. Joins
        7. In a nutshell: cube model and cubes
      5. Building cube models using the OLAP Center (1/8)
      6. Building cube models using the OLAP Center (2/8)
      7. Building cube models using the OLAP Center (3/8)
      8. Building cube models using the OLAP Center (4/8)
      9. Building cube models using the OLAP Center (5/8)
      10. Building cube models using the OLAP Center (6/8)
      11. Building cube models using the OLAP Center (7/8)
      12. Building cube models using the OLAP Center (8/8)
        1. Planning for building a cube model
        2. Preparing the DB2 relational database for DB2 Cube Views
        3. Building the cube model by import
        4. Building a cube model with Quick Start wizard
        5. Creating a basic complete cube model from scratch
      13. Enhancing a cube model
        1. Based on end-user analytics requirements
        2. Based on Optimization Advisor and MQT usage
      14. Backup and recovery
      15. Summary
    2. Chapter 4: Using the cube model for summary tables optimization
      1. Summary tables and optimization requirements
      2. How cube model influences summary tables and query performance
      3. MQTs: a quick overview
        1. MQTs in general
        2. MQTs in DB2 Cube Views
      4. What you need to know before optimizing (1/4)
      5. What you need to know before optimizing (2/4)
      6. What you need to know before optimizing (3/4)
      7. What you need to know before optimizing (4/4)
        1. Get at least a cube model and one cube defined
        2. Define referential integrity or informational constraints
        3. Do you know or have an idea of the query type?
        4. Understand how Optimization Advisor uses cube model/cube
      8. Using the Optimization Advisor (1/4)
      9. Using the Optimization Advisor (2/4)
      10. Using the Optimization Advisor (3/4)
      11. Using the Optimization Advisor (4/4)
        1. How does the wizard work
        2. Check your cube model
        3. Run the Optimization Advisor
        4. Parameters for the Optimization Advisor
      12. Deploying Optimization Advisor MQTs (1/4)
      13. Deploying Optimization Advisor MQTs (2/4)
      14. Deploying Optimization Advisor MQTs (3/4)
      15. Deploying Optimization Advisor MQTs (4/4)
        1. What SQL statements are being run?
        2. Are the statements using the MQTs?
        3. How deep in the hierarchies do the MQTs go?
        4. Check the DB2 parameters
        5. Is the query optimization level correct?
      16. Optimization Advisor and cube model interactions (1/3)
      17. Optimization Advisor and cube model interactions (2/3)
      18. Optimization Advisor and cube model interactions (3/3)
        1. Optimization Advisor recommendations
        2. Query to the top of the cube
        3. Querying a bit further down the cube
        4. Moving towards the middle of the cube
        5. Visiting the bottom of the cube
      19. Performance considerations
      20. Further steps in MQT maintenance (1/4)
      21. Further steps in MQT maintenance (2/4)
      22. Further steps in MQT maintenance (3/4)
      23. Further steps in MQT maintenance (4/4)
        1. Refresh DEFERRED option
        2. Refresh IMMEDIATE option
        3. Refresh DEFERRED versus refresh IMMEDIATE
        4. INCREMENTAL refresh versus FULL refresh
        5. Implementation guidelines
        6. Limitations for INCREMENTAL refresh
      24. MQT tuning
      25. Configuration considerations
        1. Estimating memory required for MQTs
        2. Estimating space required for MQTs
      26. Conclusion
  10. Part 3: Access dimensional data in DB2
    1. Chapter 5: Metadata bridges overview
      1. A quick summary
    2. Chapter 6: Accessing DB2 dimensional data using Office Connect
      1. Product overview
      2. Architecture and components
      3. Accessing OLAP metadata and data in DB2 (1/2)
      4. Accessing OLAP metadata and data in DB2 (2/2)
        1. Prepare metadata
        2. Launch Excel and load Office Connect Add-in
        3. Connect to OLAP-aware database (data source) in DB2
        4. Import cube metadata
        5. Bind data to Excel worksheet
      5. OLAP style operations in Office Connect
      6. Saving and deleting reports
      7. Refreshing data
      8. Optimizing for better performance
        1. Enable SQLDebug trace in Office Connect
        2. Use DB2 Explain to check if SQL is routed to the MQT
        3. Scenario demonstrating benefit of optimization
    3. Chapter 7: Accessing dimensional data in DB2 using QMF for Windows
      1. QMF product overview
      2. Evolution of QMF to DB2 Cube Views support
      3. Components involved
      4. Using DB2 Cube Views in QMF for Windows (1/3)
      5. Using DB2 Cube Views in QMF for Windows (2/3)
      6. Using DB2 Cube Views in QMF for Windows (3/3)
        1. QMF for Windows OLAP Query wizard
        2. Multidimensional data modeling
        3. Object Explorer
        4. Layout Designer
        5. Query Results View
      7. OLAP report examples and benefits
        1. Who can use OLAP functionality?
        2. Before starting
        3. Sales analysis scenario
      8. Maintenance
        1. Invalidation of OLAP queries
        2. Performance issues
      9. Conclusion
    4. Chapter 8: Using Ascential MetaStage and the DB2 Cube Views MetaBroker
      1. Ascential MetaStage product overview (1/2)
      2. Ascential MetaStage product overview (2/2)
        1. Managing metadata with MetaStage
      3. Metadata flow scenarios with MetaStage (1/10)
      4. Metadata flow scenarios with MetaStage (2/10)
      5. Metadata flow scenarios with MetaStage (3/10)
      6. Metadata flow scenarios with MetaStage (4/10)
      7. Metadata flow scenarios with MetaStage (5/10)
      8. Metadata flow scenarios with MetaStage (6/10)
      9. Metadata flow scenarios with MetaStage (7/10)
      10. Metadata flow scenarios with MetaStage (8/10)
      11. Metadata flow scenarios with MetaStage (9/10)
      12. Metadata flow scenarios with MetaStage (10/10)
        1. Importing ERwin dimensional metadata into DB2 Cube Views
        2. Leveraging existing enterprise metadata with MetaStage
        3. Performing cross-tool impact analysis
        4. Performing data lineage and process analysis in MetaStage
      13. Conclusion: benefits
    5. Chapter 9: Meta Integration of DB2 Cube Views within the enterprise toolset
      1. Meta Integration Technology products overview
        1. Meta Integration Works (MIW)
        2. Meta Integration Repository (MIR)
        3. Meta Integration Model Bridge (MIMB)
      2. Architecture and components involved
      3. Metadata flow scenarios
      4. Metadata mapping and limitations considerations
        1. Forward engineering from a relational model to a cube model
        2. Reverse engineering of a cube model into a relational model
      5. Implementation steps scenario by scenario (1/15)
      6. Implementation steps scenario by scenario (2/15)
      7. Implementation steps scenario by scenario (3/15)
      8. Implementation steps scenario by scenario (4/15)
      9. Implementation steps scenario by scenario (5/15)
      10. Implementation steps scenario by scenario (6/15)
      11. Implementation steps scenario by scenario (7/15)
      12. Implementation steps scenario by scenario (8/15)
      13. Implementation steps scenario by scenario (9/15)
      14. Implementation steps scenario by scenario (10/15)
      15. Implementation steps scenario by scenario (11/15)
      16. Implementation steps scenario by scenario (12/15)
      17. Implementation steps scenario by scenario (13/15)
      18. Implementation steps scenario by scenario (14/15)
      19. Implementation steps scenario by scenario (15/15)
        1. Metadata integration of DB2 Cube Views with ERwin v4.x
        2. Metadata integration of DB2 Cube Views with ERwin v3.x
        3. Metadata integration of DB2 Cube Views with PowerDesigner
        4. Metadata integration of DB2 Cube Views with IBM Rational Rose
        5. Metadata integration of DB2 Cube Views with CWM and XMI
        6. Metadata integration of DB2 Cube Views with DB2 Warehouse Manager
        7. Metadata integration of DB2 Cube Views with Informatica
      20. Refresh considerations
      21. Conclusion: benefits
    6. Chapter 10: Accessing DB2 dimensional data using Integration Server Bridge
      1. DB2 OLAP Server and Integration Server bridge
        1. Integration Server
        2. Hybrid Analysis
        3. Integration Server Bridge
      2. Metadata flow scenarios
        1. DB2 OLAP Server and DB2 Cube Views not installed
        2. DB2 OLAP Server and IS installed, but not DB2 Cube Views
        3. DB2 OLAP Server installed, but not IS and DB2 Cube Views
        4. DB2 Cube Views installed, but not DB2 OLAP Server
      3. Implementation steps (1/5)
      4. Implementation steps (2/5)
      5. Implementation steps (3/5)
      6. Implementation steps (4/5)
      7. Implementation steps (5/5)
        1. Metadata flow from DB2 Cube Views to Integration Server
        2. Metadata flow from Integration Server to DB2 Cube Views
      8. Maintenance
      9. DB2 OLAP Server examples and benefits (1/7)
      10. DB2 OLAP Server examples and benefits (2/7)
      11. DB2 OLAP Server examples and benefits (3/7)
      12. DB2 OLAP Server examples and benefits (4/7)
      13. DB2 OLAP Server examples and benefits (5/7)
      14. DB2 OLAP Server examples and benefits (6/7)
      15. DB2 OLAP Server examples and benefits (7/7)
        1. Data load
        2. Hybrid Analysis
        3. Drill through reports
      16. Conclusions
    7. Chapter 11: Accessing DB2 dimensional data using Cognos
      1. The Cognos solution
        1. Cognos Business Intelligence
      2. Architecture and components involved
      3. Implementation steps (1/2)
      4. Implementation steps (2/2)
      5. Implementation considerations (1/5)
      6. Implementation considerations (2/5)
      7. Implementation considerations (3/5)
      8. Implementation considerations (4/5)
      9. Implementation considerations (5/5)
        1. Optimizing drill through
        2. Optimizing Impromptu reports
        3. Implementation considerations: mappings
        4. Enhancing the DB2 cube model
      10. Cube model refresh considerations
      11. Scenarios (1/4)
      12. Scenarios (2/4)
      13. Scenarios (3/4)
      14. Scenarios (4/4)
        1. Sales analysis scenario
        2. Financial analysis scenario
        3. Performance results with MQT
      15. Conclusion: benefits
    8. Chapter 12: Accessing DB2 dimensional data using BusinessObjects
      1. Business Objects product overview
        1. BusinessObjects Enterprise 6
      2. BusinessObjects Universal Metadata Bridge overview (1/3)
      3. BusinessObjects Universal Metadata Bridge overview (2/3)
      4. BusinessObjects Universal Metadata Bridge overview (3/3)
        1. Metadata mapping
        2. Complex measure mapping
        3. Data type conversion
      5. Implementation steps (1/3)
      6. Implementation steps (2/3)
      7. Implementation steps (3/3)
        1. Export metadata from DB2 OLAP Center
        2. Import the metadata in the universe using Application Mode
        3. Import the metadata in the universe using API mode
        4. Import the metadata in the universe using the batch mode
        5. Warning messages
      8. Reports and queries examples (1/2)
      9. Reports and queries examples (2/2)
        1. Query 1
        2. Query 2
        3. Query 3
      10. Deployment
        1. Optimization tips
      11. Conclusion: benefits
        1. Universe creation
        2. Improving response time with MQTs
    9. Chapter 13: Accessing DB2 dimensional data using MicroStrategy
      1. MicroStrategy product introduction
      2. Architecture and components involved
      3. Implementation steps
        1. Installation
        2. Prerequisites
        3. Import
      4. Mapping considerations and metadata refresh
        1. Mapping fundamentals
        2. Assumptions and best practices
        3. Metadata refresh
      5. Reports and query examples (1/3)
      6. Reports and query examples (2/3)
      7. Reports and query examples (3/3)
        1. The business case and the business questions
        2. Question 1: department contributions to sales
        3. Question 2: campaign contributions to sales
        4. Question 3: ranking the campaigns by region
        5. Question 4: obtaining the Top 5 campaigns
        6. Question 5: campaign impact by age range
      8. Conclusion: benefits
    10. Chapter 14: Web services for DB2 Cube Views
      1. Web services for DB2 Cube Views: advantages
      2. Overview of the technologies used
        1. Web services technology
        2. XML
        3. SOAP
        4. WSDL
        5. UDDI
        6. XPath
      3. Architecture of Web services for DB2 Cube Views
      4. Web services for DB2 Cube Views (1/3)
      5. Web services for DB2 Cube Views (2/3)
      6. Web services for DB2 Cube Views (3/3)
        1. Describe
        2. Members
        3. Execute
      7. Conclusion
  11. Part 4: Appendixes
    1. Appendix A: DataStage: operational process metadata configuration and DataStage job example
      1. Configure the operational metadata components
      2. Configure the server machine
      3. Configure the client
      4. Creating DataStage Server jobs (1/3)
      5. Creating DataStage Server jobs (2/3)
      6. Creating DataStage Server jobs (3/3)
    2. Appendix B: Hybrid Analysis query performance results (1/2)
    3. Appendix B: Hybrid Analysis query performance results (2/2)
    4. Appendix C: FAQs, diagnostics, and tracing
      1. Setup questions
      2. Metadata questions
      3. OLAP Center
      4. Tracing
    5. Appendix D: DB2 Cube Views stored procedure API
      1. API architecture overview
      2. Purposes and functionality of the API
      3. The stored procedure interface
      4. Example
      5. Error logging and tracing
      6. db2mdapiclient utility
    6. Appendix E: The case study: retail datamart
      1. The cube model
      2. The cube
      3. Tables in the star schema
      4. MQT
  12. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. How to get IBM Redbooks
  13. Index (1/5)
  14. Index (2/5)
  15. Index (3/5)
  16. Index (4/5)
  17. Index (5/5)
  18. Back cover

Product information

  • Title: DB2 Cube Views: A Primer
  • Author(s): Corinne Baragoin, Geetha Balasubramaniam, Bhuvaneshwari Chandrasekharan, Landon DelSordo, Jan B. Lillelund, Julie Maw, Annie Neroda, Paulo Pereira, Jo A. Ramos
  • Release date: September 2003
  • Publisher(s): IBM Redbooks
  • ISBN: None