DB2 Cube Views a primer
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 provi...
| Autor Corporativo: | |
|---|---|
| Otros Autores: | |
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
[S.l.] :
IBM, International Technical Support Organization
c2003.
|
| Edición: | 1st ed |
| Colección: | IBM redbooks.
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009627168506719 |
Tabla de Contenidos:
- 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.
