DB2 Cube Views : a primer / [Corinne Baragoin ... et al.].

Format:

Book

Contributor:

Baragoin, Corinne.

International Business Machines Corporation. International Technical Support Organization.

Series:

IBM redbooks.

IBM redbooks

Language:

English

Subjects (All):

Relational databases.

OLAP technology.

IBM Database 2.

Physical Description:

xxxvi, 718 p. : ill.

Edition:

1st ed.

Other Title:

Database two Cube Views

Place of Publication:

[S.l.] : IBM, International Technical Support Organization, c2003.

Language Note:

English

System Details:

text file

Summary:

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.

Contents:

Front cover

Contents

Figures

Tables

Examples

Notices

Trademarks

Preface

The team that wrote this redbook

Become a published author

Comments welcome

Part 1 Understand DB2 Cube Views

Chapter 1. An OLAP-aware DB2

1.1 Business Intelligence and OLAP introduction

1.1.1 Online Analytical Processing

1.1.2 Metadata

1.2 DB2 UDB V8.1 becomes OLAP-aware

1.3 Challenges faced by DBA's in an OLAP environment

1.3.1 Manage the flow of metadata

1.3.2 Optimize and manage custom summary tables

1.3.3 Optimize MOLAP database loading

1.3.4 Enhance OLAP queries performance in the relational database

1.4 How DB2 can help

1.4.1 Efficient multidimensional model: cube model

1.4.2 Summary tables optimization: Optimization Advisor

1.4.3 Interfaces

1.5 Metadata bridges to back-end and front-end tools

Chapter 2. DB2 Cube Views: scenarios and benefits

2.1 What can DB2 Cube Views do for you?

2.2 Feeding metadata into DB2 Cube Views

2.2.1 Feeding DB2 Cube Views from back-end tools

2.2.2 Feeding DB2 Cube Views from front-end tools

2.2.3 Feeding DB2 Cube Views from scratch

2.3 Feeding front-end tools from DB2 Cube Views

2.3.1 Supporting MOLAP tools with DB2 Cube Views

2.3.2 Supporting ROLAP tools with DB2 Cube Views

2.3.3 Supporting HOLAP tools with DB2 Cube Views

2.3.4 Supporting bridgeless ROLAP tools with DB2 Cube Views

2.4 Feeding Web services from DB2 Cube Views

2.4.1 A scenario

2.4.2 Flow and components

2.4.3 Benefits

Part 2 Build and optimize the DB2 Cube Model

Chapter 3. Building a cube model in DB2

3.1 What are the data schemas that can be modeled?

3.1.1 Star schemas

3.1.2 Snowflakes

3.1.3 Star and snowflakes characteristics

3.2 Cube model notion and terminology

3.2.1 Measures and facts

3.2.2 Attributes

3.2.3 Dimensions.

3.2.4 Hierarchies

3.2.5 Attribute relationships

3.2.6 Joins

3.2.7 In a nutshell: cube model and cubes

3.3 Building cube models using the OLAP Center

3.3.1 Planning for building a cube model

3.3.2 Preparing the DB2 relational database for DB2 Cube Views

3.3.3 Building the cube model by import

3.3.4 Building a cube model with Quick Start wizard

3.3.5 Creating a basic complete cube model from scratch

3.4 Enhancing a cube model

3.4.1 Based on end-user analytics requirements

3.4.2 Based on Optimization Advisor and MQT usage

3.5 Backup and recovery

3.6 Summary

Chapter 4. Using the cube model for summary tables optimization

4.1 Summary tables and optimization requirements

4.2 How cube model influences summary tables and query performance

4.3 MQTs: a quick overview

4.3.1 MQTs in general

4.3.2 MQTs in DB2 Cube Views

4.4 What you need to know before optimizing

4.4.1 Get at least a cube model and one cube defined

4.4.2 Define referential integrity or informational constraints

4.4.3 Do you know or have an idea of the query type?

4.4.4 Understand how Optimization Advisor uses cube model/cube

4.5 Using the Optimization Advisor

4.5.1 How does the wizard work

4.5.2 Check your cube model

4.5.3 Run the Optimization Advisor

4.5.4 Parameters for the Optimization Advisor

4.6 Deploying Optimization Advisor MQTs

4.6.1 What SQL statements are being run?

4.6.2 Are the statements using the MQTs?

4.6.3 How deep in the hierarchies do the MQTs go?

4.6.4 Check the DB2 parameters

4.6.5 Is the query optimization level correct?

4.7 Optimization Advisor and cube model interactions

4.7.1 Optimization Advisor recommendations

4.7.2 Query to the top of the cube

4.7.3 Querying a bit further down the cube

4.7.4 Moving towards the middle of the cube.

4.7.5 Visiting the bottom of the cube

4.8 Performance considerations

4.9 Further steps in MQT maintenance

4.9.1 Refresh DEFERRED option

4.9.2 Refresh IMMEDIATE option

4.9.3 Refresh DEFERRED versus refresh IMMEDIATE

4.9.4 INCREMENTAL refresh versus FULL refresh

4.9.5 Implementation guidelines

4.9.6 Limitations for INCREMENTAL refresh

4.10 MQT tuning

4.11 Configuration considerations

4.11.1 Estimating memory required for MQTs

4.11.2 Estimating space required for MQTs

4.12 Conclusion

Part 3 Access dimensional data in DB2

Chapter 5. Metadata bridges overview

5.1 A quick summary

Chapter 6. Accessing DB2 dimensional data using Office Connect

6.1 Product overview

6.2 Architecture and components

6.3 Accessing OLAP metadata and data in DB2

6.3.1 Prepare metadata

6.3.2 Launch Excel and load Office Connect Add-in

6.3.3 Connect to OLAP-aware database (data source) in DB2

6.3.4 Import cube metadata

6.3.5 Bind data to Excel worksheet

6.4 OLAP style operations in Office Connect

6.5 Saving and deleting reports

6.6 Refreshing data

6.7 Optimizing for better performance

6.7.1 Enable SQLDebug trace in Office Connect

6.7.2 Use DB2 Explain to check if SQL is routed to the MQT

6.7.3 Scenario demonstrating benefit of optimization

Chapter 7. Accessing dimensional data in DB2 using QMF for Windows

7.1 QMF product overview

7.2 Evolution of QMF to DB2 Cube Views support

7.3 Components involved

7.4 Using DB2 Cube Views in QMF for Windows

7.4.1 QMF for Windows OLAP Query wizard

7.4.2 Multidimensional data modeling

7.4.3 Object Explorer

7.4.4 Layout Designer

7.4.5 Query Results View

7.5 OLAP report examples and benefits

7.5.1 Who can use OLAP functionality?

7.5.2 Before starting

7.5.3 Sales analysis scenario

7.6 Maintenance.

7.6.1 Invalidation of OLAP queries

7.6.2 Performance issues

7.7 Conclusion

Chapter 8. Using Ascential MetaStage and the DB2 Cube Views MetaBroker

8.1 Ascential MetaStage product overview

8.1.1 Managing metadata with MetaStage

8.2 Metadata flow scenarios with MetaStage

8.2.1 Importing ERwin dimensional metadata into DB2 Cube Views

8.2.2 Leveraging existing enterprise metadata with MetaStage

8.2.3 Performing cross-tool impact analysis

8.2.4 Performing data lineage and process analysis in MetaStage

8.3 Conclusion: benefits

Chapter 9. Meta Integration of DB2 Cube Views within the enterprise toolset

9.1 Meta Integration Technology products overview

9.1.1 Meta Integration Works (MIW)

9.1.2 Meta Integration Repository (MIR)

9.1.3 Meta Integration Model Bridge (MIMB)

9.2 Architecture and components involved

9.3 Metadata flow scenarios

9.4 Metadata mapping and limitations considerations

9.4.1 Forward engineering from a relational model to a cube model

9.4.2 Reverse engineering of a cube model into a relational model

9.5 Implementation steps scenario by scenario

9.5.1 Metadata integration of DB2 Cube Views with ERwin v4.x

9.5.2 Metadata integration of DB2 Cube Views with ERwin v3.x

9.5.3 Metadata integration of DB2 Cube Views with PowerDesigner

9.5.4 Metadata integration of DB2 Cube Views with IBM Rational Rose

9.5.5 Metadata integration of DB2 Cube Views with CWM and XMI

9.5.6 Metadata integration of DB2 Cube Views with DB2 Warehouse Manager

9.5.7 Metadata integration of DB2 Cube Views with Informatica

9.6 Refresh considerations

9.7 Conclusion: benefits

Chapter 10. Accessing DB2 dimensional data using Integration Server Bridge

10.1 DB2 OLAP Server and Integration Server bridge

10.1.1 Integration Server

10.1.2 Hybrid Analysis.

10.1.3 Integration Server Bridge

10.2 Metadata flow scenarios

10.2.1 DB2 OLAP Server and DB2 Cube Views not installed

10.2.2 DB2 OLAP Server and IS installed, but not DB2 Cube Views

10.2.3 DB2 OLAP Server installed, but not IS and DB2 Cube Views

10.2.4 DB2 Cube Views installed, but not DB2 OLAP Server

10.3 Implementation steps

10.3.1 Metadata flow from DB2 Cube Views to Integration Server

10.3.2 Metadata flow from Integration Server to DB2 Cube Views

10.4 Maintenance

10.5 DB2 OLAP Server examples and benefits

10.5.1 Data load

10.5.2 Hybrid Analysis

10.5.3 Drill through reports

10.6 Conclusions

Chapter 11. Accessing DB2 dimensional data using Cognos

11.1 The Cognos solution

11.1.1 Cognos Business Intelligence

11.2 Architecture and components involved

11.3 Implementation steps

11.4 Implementation considerations

11.4.1 Optimizing drill through

11.4.2 Optimizing Impromptu reports

11.4.3 Implementation considerations: mappings

11.4.4 Enhancing the DB2 cube model

11.5 Cube model refresh considerations

11.6 Scenarios

11.6.1 Sales analysis scenario

11.6.2 Financial analysis scenario

11.6.3 Performance results with MQT

11.7 Conclusion: benefits

Chapter 12. Accessing DB2 dimensional data using BusinessObjects

12.1 Business Objects product overview

12.1.1 BusinessObjects Enterprise 6

12.2 BusinessObjects Universal Metadata Bridge overview

12.2.1 Metadata mapping

12.2.2 Complex measure mapping

12.2.3 Data type conversion

12.3 Implementation steps

12.3.1 Export metadata from DB2 OLAP Center

12.3.2 Import the metadata in the universe using Application Mode

12.3.3 Import the metadata in the universe using API mode

12.3.4 Import the metadata in the universe using the batch mode

12.3.5 Warning messages

12.4 Reports and queries examples.

12.4.1 Query 1.

Notes:

"September 2003."

Includes bibliographical references and index.

OCLC:

80246044