IBM Cognos Dynamic Cubes

IBM Cognos Dynamic Cubes

Released July 2015
Publisher(s): IBM Redbooks
ISBN: 9780738440835

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

IBM® Cognos® Business Intelligence (BI) provides a proven enterprise BI platform with an open data strategy. Cognos BI provides customers with the ability to use data from any source, package it into a business model, and make it available to consumers in various interfaces that are tailored to the task.

IBM Cognos Dynamic Cubes complements the existing Cognos BI capabilities and continues the tradition of an open data model. It focuses on extending the scalability of the IBM Cognos platform to enable speed-of-thought analytics over terabytes of enterprise data, without having to invest in a new data warehouse appliance. This capability adds a new level of query intelligence so you can unleash the power of your enterprise data warehouse.

This IBM Redbooks® publication addresses IBM Cognos Business Intelligence V10.2.2 and specifically, the IBM Cognos Dynamic Cubes capabilities. This book can help you in the following ways:

  • Understand core features of the Cognos Dynamic Cubes capabilities of Cognos BI V10.2

  • Learn by example with practical scenarios by using the IBM Cognos samples


    • This book uses fictional business scenarios to demonstrate the power and capabilities of IBM Cognos Dynamic Cubes. It primarily focuses on the roles of the modeler, administrator, and IT architect.

    Table of contents

    1. Front cover
    2. Notices
      1. Trademarks
    3. Preface
      1. The team who wrote this book
      2. Now you can become a published author, too
      3. Comments welcome
      4. Stay connected to IBM Redbooks
    4. Summary of changes
      1. July 2015, Second Edition
    5. Chapter 1. Overview of Cognos Dynamic Cubes
      1. 1.1 Introduction to IBM Cognos Dynamic Cubes
        1. 1.1.1 High performance interactive analysis
        2. 1.1.2 Enterprise data warehouses for analytics
        3. 1.1.3 Data volumes and context in the Enterprise Data Warehouse
        4. 1.1.4 Architecture summary
      2. 1.2 Skill set requirements for IBM Cognos Dynamic Cubes
      3. 1.3 When to use Cognos Dynamic Cubes
      4. 1.4 System requirements
      5. 1.5 Comparison to other IBM cube technologies
      6. 1.6 Overview of the remainder of the book
    6. Chapter 2. IBM Cognos Dynamic Cubes architecture
      1. 2.1 Dynamic cubes in the IBM Cognos BI environment
        1. 2.1.1 Dynamic cubes and the data warehouse
        2. 2.1.2 Modeling dynamic cubes
        3. 2.1.3 The published dynamic cube model
        4. 2.1.4 Dynamic cubes as Cognos BI data sources
        5. 2.1.5 Configuring dynamic cubes
        6. 2.1.6 Dynamic cube management
        7. 2.1.7 IBM Cognos Dynamic Cubes Aggregate Advisor
      2. 2.2 Dynamic cubes and the query service
        1. 2.2.1 Dynamic cubes and the report server
        2. 2.2.2 Coexistence of 32-bit report server and 64-bit query service
        3. 2.2.3 Dynamic cubes in the query service JVM
        4. 2.2.4 Query service interactions with dynamic cubes
      3. 2.3 Cognos Dynamic Cubes caching
        1. 2.3.1 Result set cache
        2. 2.3.2 Expression cache
        3. 2.3.3 Member cache
        4. 2.3.4 Aggregate cache
        5. 2.3.5 Data cache
      4. 2.4 Dynamic cubes query processing
        1. 2.4.1 Metadata query processing in the query service
        2. 2.4.2 OLAP query processing in the query service
      5. 2.5 Using the database to evaluate set expressions
      6. 2.6 Management of Cognos Dynamic Cubes
        1. 2.6.1 Memory usage
        2. 2.6.2 Data currency
        3. 2.6.3 Communication
    7. Chapter 3. Installation and configuration of IBM Cognos Cube Designer and IBM Cognos Dynamic Query Analyzer
      1. 3.1 Introduction
      2. 3.2 Cognos Cube Designer operating requirements
        1. 3.2.1 IBM Cognos 10.2.2 Business Intelligence Server
        2. 3.2.2 IBM Cognos 10.2.2 Report Server
        3. 3.2.3 Java Runtime Environment Libraries
        4. 3.2.4 Supported relational databases
        5. 3.2.5 Cognos Cube Designer supported operating systems
      3. 3.3 Cognos Dynamic Cubes hardware requirements for the BI Server
      4. 3.4 Installing IBM Cognos Cube Designer
      5. 3.5 Installing Cognos Dynamic Query Analyzer
        1. 3.5.1 Cognos Dynamic Query Analyzer installation requirements
        2. 3.5.2 Installation procedure
        3. 3.5.3 Configuring Dynamic Query Analyzer
    8. Chapter 4. Modeling with IBM Cognos Cube Designer
      1. 4.1 Introduction to Cognos Cube Designer
      2. 4.2 Starting Cognos Cube Designer
      3. 4.3 Creating a project
      4. 4.4 The Cognos Cube Designer workspace
        1. 4.4.1 The Cognos Cube Designer menu bar
        2. 4.4.2 Metadata
        3. 4.4.3 The Project Explorer
        4. 4.4.4 The Editor window
        5. 4.4.5 The Details window
        6. 4.4.6 The expression editor
      5. 4.5 Using the Get Metadata menu
        1. 4.5.1 Using Get Metadata with a data source
        2. 4.5.2 Using Get Metadata against Framework Manager
      6. 4.6 Exploring metadata and data
        1. 4.6.1 Viewing data from a table
    9. Chapter 5. Basic modeling
      1. 5.1 Modeling Concepts
        1. 5.1.1 Cubes
        2. 5.1.2 Dimensions
        3. 5.1.3 Levels
        4. 5.1.4 Hierarchies
        5. 5.1.5 Members
        6. 5.1.6 Attributes
        7. 5.1.7 Calculated members
        8. 5.1.8 Measures
        9. 5.1.9 Named sets
      2. 5.2 Starting a dynamic cubes project
        1. 5.2.1 Managing locales
        2. 5.2.2 Manually create a dynamic cube
        3. 5.2.3 Generate a dynamic cube
      3. 5.3 Modeling dimensions
        1. 5.3.1 Modeling levels
        2. 5.3.2 Modeling hierarchies
        3. 5.3.3 Parent-child dimensions
        4. 5.3.4 Joins
        5. 5.3.5 Dimension filters
        6. 5.3.6 Multilingual Attributes
        7. 5.3.7 Generate a dimension
      4. 5.4 Modeling measures
        1. 5.4.1 Creating a measure
        2. 5.4.2 Measures with expressions
        3. 5.4.3 Calculated measures
        4. 5.4.4 Measure folders
        5. 5.4.5 Measure filters
        6. 5.4.6 The default measure and transaction ID
      5. 5.5 Bringing dimensions and measures together in a cube
        1. 5.5.1 Relationships
        2. 5.5.2 Aggregation rules
        3. 5.5.3 Named Sets
        4. 5.5.4 Estimating hardware requirements
      6. 5.6 Parameter maps
      7. 5.7 Validation
      8. 5.8 Deploying dynamic cubes for reporting and analysis
        1. 5.8.1 Quick-deploy options in Cognos Cube Designer
        2. 5.8.2 Manually deploying a dynamic cube
        3. 5.8.3 Working with packages
      9. 5.9 Managing data sources
    10. Chapter 6. Advanced topics in modeling
      1. 6.1 Model design considerations
        1. 6.1.1 Cube design considerations
        2. 6.1.2 Dimension design considerations
        3. 6.1.3 Hierarchy design
        4. 6.1.4 Measure design
      2. 6.2 Time dimensions and relative time
        1. 6.2.1 Current periods
        2. 6.2.2 Creating a time dimension
        3. 6.2.3 Exercise for creating a time dimension
        4. 6.2.4 Level types
        5. 6.2.5 Relative time members
        6. 6.2.6 Custom relative time members
      3. 6.3 Slowly changing dimensions
        1. 6.3.1 Modeling slowly changing dimensions
        2. 6.3.2 Modeling a regular slowly changing dimension
        3. 6.3.3 Modeling time-stamped slowly changing dimensions
      4. 6.4 Role-playing dimensions
      5. 6.5 Multiple fact and multiple fact grain scenarios
        1. 6.5.1 Multiple fact tables
        2. 6.5.2 When the fact grain and dimension grain differ
      6. 6.6 Data quality and integrity
      7. 6.7 Logical Modeling
      8. 6.8 Modeling for null values
      9. 6.9 Scenario dimensions
        1. 6.9.1 Modeling a scenario dimension with a forced null default member
        2. 6.9.2 Modeling a secured scenario dimension
      10. 6.10 Troubleshooting
        1. 6.10.1 Double counting
        2. 6.10.2 Importing metadata
        3. 6.10.3 Publishing errors
        4. 6.10.4 Refreshing metadata that changed in the database
        5. 6.10.5 Members
        6. 6.10.6 Missing members
        7. 6.10.7 Ellipses in the project viewer tree
        8. 6.10.8 No Protocol message while running Cognos Cube Designer
        9. 6.10.9 Logging in Cognos Cube Designer
    11. Chapter 7. Administering dynamic cubes
      1. 7.1 Adding and removing cubes
        1. 7.1.1 Adding a cube to the server group
        2. 7.1.2 Removing a cube from the query service
      2. 7.2 Starting, stopping, and monitoring cubes
        1. 7.2.1 Starting a cube
        2. 7.2.2 Stopping a cube
        3. 7.2.3 Monitoring cube state through metrics
      3. 7.3 Managing the cache
      4. 7.4 Setting up routing rules
      5. 7.5 Setting up redundant cube instances
      6. 7.6 Scheduling the refresh of the cache
      7. 7.7 Administering cubes from command line
    12. Chapter 8. Virtual cubes
      1. 8.1 Overview of virtual cubes
      2. 8.2 Creating virtual cubes
        1. 8.2.1 Source object merging rules
        2. 8.2.2 Manually merging source cube objects
        3. 8.2.3 Virtual calculated measures
        4. 8.2.4 Deploying virtual cubes
        5. 8.2.5 Exercise: Modeling a virtual cube
      3. 8.3 Common use cases for virtual cubes
        1. 8.3.1 Time partitioning
        2. 8.3.2 Currency conversion
        3. 8.3.3 Multiple fact tables
      4. 8.4 Managing virtual cubes
        1. 8.4.1 Starting a virtual cube
        2. 8.4.2 Stopping or pausing a virtual cube
        3. 8.4.3 Stopping the source cubes
        4. 8.4.4 Verifying virtual cube dependency
      5. 8.5 Virtual cube considerations
        1. 8.5.1 Modeling considerations for virtual cubes
        2. 8.5.2 Caching and performance considerations
    13. Chapter 9. Dimensional security
      1. 9.1 Overview of dimensional security
      2. 9.2 Security model
        1. 9.2.1 Security filters
        2. 9.2.2 Security views
      3. 9.3 Grant versus deny approaches to security
      4. 9.4 Visible ancestors
        1. 9.4.1 Example: Visible ancestors unnecessary
        2. 9.4.2 Example: Basic visible ancestor
        3. 9.4.3 Example: Combination of visible and granted ancestors
        4. 9.4.4 Examples: View merge impact on visible ancestors
      5. 9.5 Secured padding members
        1. 9.5.1 Example: Secured padding member unnecessary
        2. 9.5.2 Example: Secured padding member needed at leaf level
        3. 9.5.3 Example: Secured padding members needed on multiple levels
      6. 9.6 Default members
        1. 9.6.1 Default member examples
        2. 9.6.2 Data caching
      7. 9.7 Calculated members
      8. 9.8 Merging security views or security filters
        1. 9.8.1 Secured tuples
        2. 9.8.2 Example 1
        3. 9.8.3 Example 2
        4. 9.8.4 Example 3
        5. 9.8.5 Example 4
        6. 9.8.6 Example 5
        7. 9.8.7 Example 6
        8. 9.8.8 Example 7
      9. 9.9 Security lookup tables
      10. 9.10 Defining security in Cognos Cube Designer
        1. 9.10.1 Selecting the hierarchy on which to define the security filter
        2. 9.10.2 Creating security filters
        3. 9.10.3 Creating security views
        4. 9.10.4 Securing measures
        5. 9.10.5 Securing dimensions
        6. 9.10.6 Securing attributes
      11. 9.11 Publishing and starting the cube
      12. 9.12 Applying security views
      13. 9.13 Verifying the security model
      14. 9.14 Transformer-style security
        1. 9.14.1 Cognos Transformer suppress option
        2. 9.14.2 Cognos Transformer Apex option
        3. 9.14.3 Cognos Transformer Cloak option
        4. 9.14.4 Cognos Transformer Summarize option
        5. 9.14.5 Cognos Transformer Exclude option
    14. Chapter 10. Securing the IBM Cognos BI environment
      1. 10.1 Roles and capabilities required to manage dynamic cubes
      2. 10.2 Securing cube data
        1. 10.2.1 Setting up the access account
        2. 10.2.2 Securing data source access to exclude the system administrator
      3. 10.3 Securing the development environment
      4. 10.4 Securing the production environment
    15. Chapter 11. Optimization and performance tuning
      1. 11.1 Performance implications of OLAP-style reports
      2. 11.2 Aggregate awareness in Cognos Dynamic Cubes
      3. 11.3 Overview of the Aggregate Advisor
        1. 11.3.1 Model-based analysis
        2. 11.3.2 Workload-based analysis
        3. 11.3.3 Preparing your environment for running the Aggregate Advisor
        4. 11.3.4 Running the Aggregate Advisor wizard
        5. 11.3.5 Opening Aggregate Advisor results and viewing details
        6. 11.3.6 Rerunning the Aggregate Advisor
      4. 11.4 In-memory aggregates
        1. 11.4.1 Applying in-memory aggregates
        2. 11.4.2 Loading in-memory aggregates into the aggregate cache
        3. 11.4.3 Monitoring aggregate cache hits
        4. 11.4.4 User-defined in-memory aggregates
        5. 11.4.5 Automatic optimization of in-memory aggregates
        6. 11.4.6 In-memory aggregate tips and troubleshooting
      5. 11.5 Database aggregates
        1. 11.5.1 In-database aggregate recommendations
        2. 11.5.2 Creating recommended database aggregates
        3. 11.5.3 Maintaining database aggregates
        4. 11.5.4 Monitoring database aggregate table hits
        5. 11.5.5 Database aggregates tips and troubleshooting
      6. 11.6 Modeling in-database aggregates
        1. 11.6.1 Identifying in-database aggregates in the model
        2. 11.6.2 Identifying aggregate table scenarios
        3. 11.6.3 Degenerate dimensions with matching level keys
        4. 11.6.4 Aggregate table with matching join keys
        5. 11.6.5 Rollup dimensions
        6. 11.6.6 Parent-child dimensions
        7. 11.6.7 Custom aggregation
        8. 11.6.8 Slicers and partitioned aggregates
        9. 11.6.9 Other modeling considerations
        10. 11.6.10 Exploring the in-database aggregate definition in the samples
        11. 11.6.11 Automatic aggregate creation
      7. 11.7 Modeling user-defined in-memory aggregates
      8. 11.8 Cache Priming Techniques
        1. 11.8.1 Preloading the caches
        2. 11.8.2 Priming the caches
        3. 11.8.3 Scheduling priming reports to run by trigger
        4. 11.8.4 Advanced setting to identify priming-only reports
        5. 11.8.5 Optimization of calculated measures
      9. 11.9 Cache size effective practices
      10. 11.10 Scenarios for performance tuning
        1. 11.10.1 Cases for adjusting cache sizes
        2. 11.10.2 Cases for aggregates
        3. 11.10.3 Optimizing the cube model
        4. 11.10.4 Optimizing reports
        5. 11.10.5 Performance and the data warehouse
        6. 11.10.6 Other possibilities for slow queries
    16. Chapter 12. Near real-time updates
      1. 12.1 Near real-time updates overview
      2. 12.2 Modeling near real-time updates
        1. 12.2.1 The database structure
        2. 12.2.2 Enabling near real-time updates in the cube model
        3. 12.2.3 Adding near real-time updates to an existing cube
      3. 12.3 Applying near real-time updates
        1. 12.3.1 Manually incrementing data
        2. 12.3.2 Scheduled incremental updates
        3. 12.3.3 Using the DCAdmin command-line tool to apply incremental updates
        4. 12.3.4 New dimension element processing
        5. 12.3.5 Committing new data to the historic storage
    17. Chapter 13. Dynamic cube lifecycle
      1. 13.1 Overview of the dynamic cube lifecycle
        1. 13.1.1 Analyze
        2. 13.1.2 Deploy
        3. 13.1.3 Run
        4. 13.1.4 Optimize
        5. 13.1.5 Lifecycle across different environments
        6. 13.1.6 Development
        7. 13.1.7 Test
        8. 13.1.8 Production
      2. 13.2 Mechanisms for moving dynamic cubes
        1. 13.2.1 Importing the cube using a deployment archive
        2. 13.2.2 Republishing the cube by using Cognos Cube Designer
      3. 13.3 Effective practices for deploying between environments
        1. 13.3.1 Keep a copy of the dynamic cube project file under source control
        2. 13.3.2 Use consistent data source, schema, and table names in all environments
        3. 13.3.3 Skip the optimize step in the development environment
        4. 13.3.4 Use a separate database for each environment
        5. 13.3.5 Include complete dimensional data in development environment
        6. 13.3.6 Make the test system similar to the production system
    18. Chapter 14. Troubleshooting
      1. 14.1 Common problems
        1. 14.1.1 Cube does not start due to logon error
        2. 14.1.2 Unexpected data values returned
        3. 14.1.3 Troubleshooting dynamic cubes aggregate matching
        4. 14.1.4 Incorrect numbers from in-memory aggregate
        5. 14.1.5 Incorrect auto summary value
        6. 14.1.6 Query returns empty cell for a calculated member
        7. 14.1.7 Cube loaded but relative time members are missing
        8. 14.1.8 Dispatcher global unique identifier (GUID) not found
      2. 14.2 IBM Cognos Dynamic Query Analyzer troubleshooting features
        1. 14.2.1 Installing and configuring DQA
        2. 14.2.2 Creating a virtual directory to access log files
        3. 14.2.3 Running DQA
        4. 14.2.4 The DQA workspace
      3. 14.3 Enabling query execution tracing
        1. 14.3.1 Enabling query execution tracing on a report basis from DQA
        2. 14.3.2 Enabling query execution tracing for all requests
      4. 14.4 Enabling query plan tracing
      5. 14.5 The query service log file
      6. 14.6 Enabling Cognos Dynamic Cubes logging
        1. 14.6.1 Query service environment variables and JVM heap size
        2. 14.6.2 Dynamic cube advanced settings configuration
        3. 14.6.3 Dynamic cube management: Start, stop
        4. 14.6.4 Dynamic cube hierarchy load
        5. 14.6.5 SQL queries statement
        6. 14.6.6 Dynamic cube timing
        7. 14.6.7 Query Performance
        8. 14.6.8 Virtual Cubes
        9. 14.6.9 Additional event groups
        10. 14.6.10 Inspecting cube start
      7. 14.7 Reviewing Cognos reports with DQA
        1. 14.7.1 Reviewing the query execution trace
        2. 14.7.2 Subsequent run requests: Taking advantage of the result set cache
        3. 14.7.3 Confirming the role of caching
        4. 14.7.4 Reviewing Report SQL
        5. 14.7.5 Verifying changes to the BranchProfitByYear report
        6. 14.7.6 Virtual cube query execution tracing
      8. 14.8 Open query service log with DQA
      9. 14.9 IBM Support Assistant
        1. 14.9.1 Accessing ISA problem determination tools
    19. Chapter 15. Query service memory monitoring
      1. 15.1 Overview of query service memory
      2. 15.2 Query service memory considerations
      3. 15.3 Memory monitoring in query service
        1. 15.3.1 Criteria used to cancel queries
        2. 15.3.2 Algorithm used to cancel queries
        3. 15.3.3 Analyzing Logs
        4. 15.3.4 Memory monitoring enhancements in Cognos BI V10.2.2 Fix Pack 1
        5. 15.3.5 Memory monitoring limitations
      4. 15.4 Configuring the query service monitoring settings
    20. Chapter 16. Using Framework Manager models
      1. 16.1 Introduction
      2. 16.2 Cube Designer and Framework Manager paradigms compared
      3. 16.3 Process to import Framework Manager models
        1. 16.3.1 Process overview
        2. 16.3.2 Preparation
        3. 16.3.3 Retrieving the Framework Manager model metadata
        4. 16.3.4 Generation of the dynamic cube metadata
        5. 16.3.5 Model cleanup and refinement
    21. Chapter 17. Hardware sizing
      1. 17.1 Using Proven Practices documentation
      2. 17.2 Using the hardware sizing calculator in IBM Cognos Cube Designer
        1. 17.2.1 Starting the hardware sizing calculator
        2. 17.2.2 Changing parameter values
      3. 17.3 Applying estimated values to the cube configuration
      4. 17.4 Hardware requirements additional considerations
        1. 17.4.1 Multiple locales and hardware implications
        2. 17.4.2 Shared dimensions and hardware implications
        3. 17.4.3 Number of measures in a single cube
        4. 17.4.4 User-defined in-memory aggregates and hardware implications
        5. 17.4.5 Attributes and hardware implications
    22. Chapter 18. Dynamic cubes in a multi-dispatcher environment
      1. 18.1 Overview of a multi-dispatcher environment
        1. 18.1.1 Cognos BI server topology
        2. 18.1.2 Dispatcher routing
        3. 18.1.3 Load balancing
        4. 18.1.4 Failover
      2. 18.2 Deploying and managing cubes in a multi-dispatcher environment
        1. 18.2.1 Bringing a new dynamic cube in-service
        2. 18.2.2 Adding a dispatcher to an existing active server group
        3. 18.2.3 Removing a dispatcher from an active server group
        4. 18.2.4 Moving a cube from one active server group to another
        5. 18.2.5 Near real-time incremental update
        6. 18.2.6 Cache refresh
      3. 18.3 Database impacts in a multi-dispatcher environment
      4. 18.4 Aggregate Advisor in a multi-dispatcher environment
        1. 18.4.1 Installation
        2. 18.4.2 Workload logging
        3. 18.4.3 Aggregate Advisor results
        4. 18.4.4 Saving and clearing in-memory aggregates
        5. 18.4.5 Connecting to a different dispatcher
        6. 18.4.6 Automatic optimization of in-memory aggregates
      5. 18.5 Cube Designer in a multi-dispatcher environment
    23. Related publications
      1. IBM Redbooks
      2. Online resources
      3. Help from IBM
    24. Back cover

    Product information

    • Title: IBM Cognos Dynamic Cubes
    • Author(s):
    • Release date: July 2015
    • Publisher(s): IBM Redbooks
    • ISBN: 9780738440835