O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

IBM Cognos Dynamic Query

IBM Cognos Dynamic Query

by Jason Tavoularis, Glen Seeds, Martin Petitclerc, Robert Hatfield, Henk Cazemier, Nigel Campbell
Publisher: IBM Redbooks
Release Date: September 2013
ISBN: 0738438723
View table of contents
Start reading

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