IBM Cognos Dynamic Query

IBM Cognos Dynamic Query

by Nigel Campbell, Henk Cazemier, Robert Hatfield, Martin Petitclerc, Glen Seeds, Jason Tavoularis
Released September 2013
Publisher(s): IBM Redbooks
ISBN: 9780738438726

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

This IBM® Redbooks® publication explains how IBM Cognos® Business Intelligence (BI) administrators, authors, modelers, and power users can use the dynamic query layer effectively. It provides guidance for determining which technology within the dynamic query layer can best satisfy your business requirements. Administrators can learn how to tune the query service effectively and preferred practices for managing their business intelligence content.

This book includes information about metadata modeling of relational data sources with IBM Cognos Framework Manager. It includes considerations that can help you author high-performing applications that satisfy analytical requirements of users.

This book provides guidance for troubleshooting issues related to the dynamic query layer of Cognos BI.

Table of contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  4. Chapter 1. Overview of Cognos Dynamic Query
    1. 1.1 Introduction
    2. 1.2 Background
    3. 1.3 Architecture
      1. 1.3.1 Planning and executing the query
    4. 1.4 Technology selection guidance
      1. 1.4.1 Pure relational analytics
      2. 1.4.2 OLAP analytics
  5. Chapter 2. Administration
    1. 2.1 Configuring the query service
      1. 2.1.1 Memory sizing
      2. 2.1.2 Throughput sizing
      3. 2.1.3 Multi-server environments
    2. 2.2 Data source administration
      1. 2.2.1 Connection command blocks
      2. 2.2.2 JDBC drivers
      3. 2.2.3 OLAP connections
      4. 2.2.4 ERP and CRM data sources
    3. 2.3 Cache management
      1. 2.3.1 Priming the cache
      2. 2.3.2 Clearing the cache
      3. 2.3.3 Automating cache operations
  6. Chapter 3. Metadata modeling
    1. 3.1 Cognos Framework Manager
    2. 3.2 Goals of metadata modeling relational data sources
      1. 3.2.1 Modeling for self-service analysis
    3. 3.3 Framework Manager architecture
    4. 3.4 Key objects of a relational model
      1. 3.4.1 Query subjects
      2. 3.4.2 Dimensions
      3. 3.4.3 Determinants
      4. 3.4.4 Relationships
    5. 3.5 Organizing relational models
      1. 3.5.1 Data view
      2. 3.5.2 Business logic view
      3. 3.5.3 Presentation view
    6. 3.6 Relational modeling for performance
      1. 3.6.1 As view versus minimized SQL generation
      2. 3.6.2 Security-aware caching
  7. Chapter 4. Macros
    1. 4.1 Macros explained
    2. 4.2 Macro language
      1. 4.2.1 Operator
      2. 4.2.2 List separator character
      3. 4.2.3 Functions
      4. 4.2.4 Comments
      5. 4.2.5 Simple case construct
    3. 4.3 Parameter maps
    4. 4.4 Session parameters
    5. 4.5 Advanced examples
      1. 4.5.1 Member unique name for next year
      2. 4.5.2 Turning promptmany result into a rowset
      3. 4.5.3 Dynamic column drill
      4. 4.5.4 Filtering for internal and external customers
  8. Chapter 5. Report authoring
    1. 5.1 Authoring interfaces
      1. 5.1.1 Cognos Workspace Advanced
      2. 5.1.2 Cognos Report Studio
    2. 5.2 Processing report executions
      1. 5.2.1 Local and database processing
    3. 5.3 Database functions
    4. 5.4 Dimensional and relational reporting styles
    5. 5.5 Suppression
    6. 5.6 Dimensional summaries
    7. 5.7 Advanced features in Report Studio’s Query Explorer
      1. 5.7.1 Reference queries
      2. 5.7.2 Union, intersect, and except queries
      3. 5.7.3 Join relationships
      4. 5.7.4 Master detail relationships
  9. Chapter 6. Optimizing SQL for performance
    1. 6.1 Remember that less is faster
    2. 6.2 Make use of enforced and non-enforced constraints
    3. 6.3 Use indexes and table organization features
    4. 6.4 Review column group statistics
    5. 6.5 Avoid complex join and filter expressions
      1. 6.5.1 Temporal expressions
      2. 6.5.2 Expressions on table columns in predicates
    6. 6.6 Reduce explicit or implicit conversions
    7. 6.7 Minimize complexity of conditional query items
    8. 6.8 Review the order of conjunctions and disjunctions
    9. 6.9 Avoid performance pitfalls in sub-queries
    10. 6.10 Avoid unnecessary outer joins
    11. 6.11 Avoid using SQL expression to transpose values
    12. 6.12 Apply predicates before groupings
    13. 6.13 Trace SQL statements back to reports
  10. Chapter 7. Troubleshooting
    1. 7.1 Problem solving strategy
      1. 7.1.1 The half-split method
    2. 7.2 Error messages
    3. 7.3 Log files and tracing
      1. 7.3.1 Query planning trace
      2. 7.3.2 Query execution trace
    4. 7.4 Dynamic Query Analyzer
      1. 7.4.1 Graph nodes
      2. 7.4.2 Views
    5. 7.5 IBM technical support
      1. 7.5.1 IBM Support Portal
      2. 7.5.2 Service requests and PMRs
      3. 7.5.3 IBM Fix Central
  11. Related publications
    1. IBM Redbooks
    2. Online resources
    3. Help from IBM
  12. Back cover

Product information

  • Title: IBM Cognos Dynamic Query
  • Author(s): Nigel Campbell, Henk Cazemier, Robert Hatfield, Martin Petitclerc, Glen Seeds, Jason Tavoularis
  • Release date: September 2013
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738438726