Complete Analytics with IBM DB2 Query Management Facility: Accelerating Well-Informed Decisions Across the Enterprise

Complete Analytics with IBM DB2 Query Management Facility: Accelerating Well-Informed Decisions Across the Enterprise

by Kristi Ramey, Mike Biere, Peter Richardson, Shawn Sullivan, Jeremy Weatherall
Released August 2012
Publisher(s): IBM Redbooks
ISBN: None

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

There is enormous pressure today for businesses across all industries to cut costs, enhance business performance, and deliver greater value with fewer resources. To take business analytics to the next level and drive tangible improvements to the bottom line, it is important to manage not only the volume of data, but the speed with which actionable findings can be drawn from a wide variety of disparate sources. The findings must be easily communicated to those responsible for making both strategic and tactical decisions. At the same time, strained IT budgets require that the solution be self-service for everyone from DBAs to business users, and easily deployed to thin, browser-based clients.

Business analytics hosted in the Query Management Facility™ (QMF™) on DB2® and System z® allow you to tackle these challenges in a practical way, using new features and functions that are easily deployed across the enterprise and easily consumed by business users who do not have prior IT experience. This IBM® Redbooks® publication provides step-by-step instructions on using these new features:

  • Access to data that resides in any JDBC-compliant data source

  • OLAP access through XMLA

  • 150+ new analytical functions

  • Graphical query interfaces and graphical reports

  • Graphical, interactive dashboards

  • Ability to integrate QMF functions with third-party applications

  • Support for the IBM DB2 Analytics Accelerator

  • A new QMF Classic perspective in QMF for Workstation

  • Ability to start QMF for TSO as a DB2 for z/OS stored procedure

  • New metadata capabilities, including ER diagrams and capability to federate data into a single virtual source

Table of contents

  1. Notices
    1. Trademarks
  2. 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
  3. Chapter 1: Assessing business analytics solutions
    1. The value of business analytics
    2. The underlying components of an enterprise business analytics solution
      1. Bet-your-business operating platform
      2. Centralized architecture
      3. Sound data modeling and database management
      4. Mapping of features and functions to end-user requirements
    3. An overview of IBM business analytics with QMF
    4. Evaluating total cost of ownership
    5. The Spiffy Insurance Corporation: A fictitious company with real business needs
  4. Chapter 2: Business analytics from the ground up: Hardware, data modeling, and data warehousing
    1. The need for speed: IBM System z processors and IBM DB2 Analytics Accelerator
      1. The z196 processor: Optimal design for enterprise analytics
      2. Extensibility with the zEnterprise BladeCenter
      3. Maximum query performance with the IBM DB2 Analytics Accelerator
      4. The System z Integrated Facility for Linux for support of Linux on System z
    2. Good decisions start with good data: Warehousing with DB2 for z/OS
      1. Why a solid data warehousing solution is critical for business analytics
      2. Why DB2 for z/OS is ideal for data warehousing
      3. IBM industry data models
      4. IBM InfoSphere Information Server
      5. The IBM InfoSphere Warehouse
  5. Chapter 3: DB2 for z/OS as an analytics engine
    1. Advantages for analytics in DB2 10
      1. CPU savings for lower total cost of ownership
      2. Advancements in scalability
      3. Reduced catalog lock contention
      4. 64-bit virtual storage relief
      5. Bitemporal queries
      6. Integrated XML support
      7. Support for OLAP: Moving sums, averages, and aggregates
      8. System Management Facility compression
      9. Dynamic compression with INSERT
      10. Dynamic SQL EXPLAIN
      11. Instance-based statement hints
      12. Dynamic SQL information
      13. Query parallelism enhancements
      14. Index enhancements
      15. Buffer pool enhancements
      16. Work file enhancements
      17. Sort enhancements
      18. Inline LOB support
      19. Dynamic statement cache enhancements
    2. Effective database management with IBM DB2 tools
  6. Chapter 4: Maximizing your existing System z investment
    1. Overview of QMF Classic capabilities
      1. QMF Classic Version 10 enhancements
      2. The QMF Classic user interface
      3. Developing a query in QMF for TSO and CICS
      4. Formatting the report
      5. Charting your data
      6. Developing procedures and applications
      7. Authentication methods and security
      8. Customizing the QMF work environment for users and groups
      9. Connecting to remote databases in the DB2 family
    2. Using QMF Classic perspective within QMF for Workstation
    3. The QMF catalog: Accessing QMF Classic objects from QMF for Workstation
    4. Directing work to System z from workstation environments
      1. The RUNTSO command in QMF for Workstation
      2. How to start QMF for TSO as a DB2 stored procedure
      3. Running TSO applications through the stored procedure interface
      4. Launching batch jobs through the stored procedure interface
    5. Using QMF HPO to manage and administer the QMF Classic environment
      1. Managing QMF objects and tracking and governing session activity
      2. Optimizing resource-intensive operations
  7. Chapter 5: Installing QMF for Workstation, QMF for WebSphere, and touring the interfaces
    1. Deciding on your configuration
    2. System requirements
    3. Installing QMF for Workstation
    4. Installing QMF for WebSphere
    5. Touring the QMF for Workstation interface
      1. Eclipse-based architecture
      2. Views
      3. Perspectives (1/2)
      4. Perspectives (2/2)
      5. Shared repository storage
      6. Repositories
      7. Workspaces
      8. Repository connections
    6. Touring the QMF for WebSphere interface
  8. Chapter 6: Configuring access to data sources and populating user workspaces
    1. Creating shared repository storage
      1. Defining connectivity to the database that will host the shared repository storage
      2. Creating and configuring shared repository storage (1/2)
      3. Creating and configuring shared repository storage (2/2)
    2. Creating a repository within shared repository storage
    3. Configuring access to data sources
      1. Adding a relational data source
      2. Adding a multidimensional data source
      3. Adding a hierarchical data source
      4. Adding a semi-structured data source
    4. Populating the Repository Explorer
      1. Populating user workspaces with content
      2. Creating workspace links to content in the Repository Explorer
  9. Chapter 7: Defining virtual data sources to reduce complexity for business users
    1. Benefits of the QMF metadata layer
    2. Reducing data complexity with virtual tables
      1. Creating a virtual table from a QMF query
      2. Using a virtual table to simplify a table schema
    3. Creating a virtual data source that federates data from multiple sources
  10. Chapter 8: Configuring security
    1. Security strategies
      1. Database-based security (1/3)
      2. Database-based security (2/3)
      3. Database-based security (3/3)
      4. Internal security (1/2)
      5. Internal security (2/2)
      6. LDAP security
      7. Using no security
    2. Minimizing the number of logins required
      1. Automating the repository connection login
      2. Using repository storage database credentials to log into the repository
      3. Storing authentication information for each data source
      4. Using repository storage database credentials to log into each data source
      5. Mapping logins to group IDs
      6. Using the personal repository
    3. Use of security in dashboards
      1. Integration methods for establishing security in dashboards (1/3)
      2. Integration methods for establishing security in dashboards (2/3)
      3. Integration methods for establishing security in dashboards (3/3)
      4. Restricting fourth quarter rows from the table (row-level security)
  11. Chapter 9: Getting to the data you need: Query methods
    1. Queries against relational data
      1. Developing queries using the query diagram view
      2. Developing queries using the prompted query view
      3. Developing queries using the SQL view
      4. Calling stored procedures from QMF queries
    2. Queries against hierarchical data
    3. Transforming data using analytical queries
    4. What can I do with my query results?
      1. Manipulating data in the query results grid
      2. Transferring query results and formatting to Microsoft Excel
      3. Adding charts and graphs to visualize query data
      4. Developing reports and dashboards from query data
      5. Defining drill-down paths through query data
    5. Queries against multidimensional data
  12. Chapter 10: Updating data with the QMF table editor
    1. Controlling access to the QMF table editor
    2. Using the QMF table editor
  13. Chapter 11: Creating reports
    1. Creating classic reports
    2. Creating visual reports
      1. Getting started
      2. The visual designer
      3. Adding a heading with a graphic (1/2)
      4. Adding a heading with a graphic (2/2)
      5. Adding group summary highlighting and labels (1/2)
      6. Adding group summary highlighting and labels (2/2)
      7. Adding conditional formatting
      8. Including data from additional queries
      9. Including fixed pages in the report
  14. Chapter 12: Working with procedures
    1. Creating a procedure
      1. A sample linear procedure
      2. A sample procedure with logic
    2. How QMF finds objects referenced by the procedure
    3. Uniquely referencing objects in the procedure
      1. Using the Key property to point to the object
      2. Referencing the object by its relative path
    4. Scheduling a procedure
  15. Chapter 13: Analysis and forecasting functions
    1. Using the QMF analytical functions
    2. Forecasting outcomes
    3. Embedding SPSS functions in QMF dashboards
  16. Chapter 14: Putting it all together: Developing dashboards
    1. Basic elements of a QMF dashboard
    2. How to create a dashboard (1/2)
    3. How to create a dashboard (2/2)
    4. Scenario 1: Field agent dashboard (relational and hierarchical data) with three scenes (three data sources)
      1. Creating a virtual data source
      2. Creating a new dashboard
      3. Creating data source connections
      4. Specifying the first query that will supply data to the dashboard
      5. Designing scene 1: Insurance business overview (1/2)
      6. Designing scene 1: Insurance business overview (2/2)
      7. Designing scene 2: Historic customer activity
      8. Designing scene 3: Customer activity in the last 30 days
    5. Scenario 2: Market analysis dashboard (OLAP and relational data) with one scene
      1. Creating data source connections
      2. Specifying the first query that will supply data to the dashboard
      3. Designing the scene: Adding grid for sales by region with product type slicer
      4. Designing the scene: Adding column chart for sales by time with a time slicer (1/2)
      5. Designing the scene: Adding column chart for sales by time with a time slicer (2/2)
      6. Designing the scene: Adding a geospatial map driven by selections in the column chart (1/3)
      7. Designing the scene: Adding a geospatial map driven by selections in the column chart (2/3)
      8. Designing the scene: Adding a geospatial map driven by selections in the column chart (3/3)
      9. Designing the scene: Adding table driven by state selected on the map (1/5)
      10. Designing the scene: Adding table driven by state selected on the map (2/5)
      11. Designing the scene: Adding table driven by state selected on the map (3/5)
      12. Designing the scene: Adding table driven by state selected on the map (4/5)
      13. Designing the scene: Adding table driven by state selected on the map (5/5)
  17. Chapter 15: Deploying created content to QMF users
    1. Including QMF content in enterprise mashups
    2. Deploying content to users by a web link
  18. Appendix A: Analytical functions available in the Expression Designer
    1. Arithmetic functions
    2. Color functions
    3. Conversion functions
    4. Data formatting functions
    5. Date and time functions
    6. Hierarchy functions
    7. Information functions
    8. Logical functions
    9. Math and trigonometry functions
    10. Measured functions
    11. Security function
    12. Spatial functions
    13. Statistical functions
    14. Text functions
    15. Visual report functions
  19. Related publications
    1. IBM Redbooks publications
    2. Other publications
    3. Online resources
    4. Help from IBM
  20. Index (1/2)
  21. Index (2/2)
  22. Back cover

Product information

  • Title: Complete Analytics with IBM DB2 Query Management Facility: Accelerating Well-Informed Decisions Across the Enterprise
  • Author(s): Kristi Ramey, Mike Biere, Peter Richardson, Shawn Sullivan, Jeremy Weatherall
  • Release date: August 2012
  • Publisher(s): IBM Redbooks
  • ISBN: None