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.