You are previewing IBM DB2 Web Query for i Version 2.1 Implementation Guide.
O'Reilly logo
IBM DB2 Web Query for i Version 2.1 Implementation Guide

Book Description

Business Intelligence (BI) is a broad term relating to applications designed to analyze data for purposes of understanding and acting on the key metrics that drive profitability in an enterprise. Key to analyzing that data is providing fast, easy access to it while delivering it in formats or tools that best fit the needs of the end user.

At the core of any business intelligence solution are end user query and reporting tools that provide intuitive access to data supporting a spectrum of end users from executives to "power users," from spreadsheet aficionados to the external Internet consumer.

IBM® DB2 Web Query for i offers a set of modernized tools for a more robust, extensible, and productive reporting solution than the popular Query for System i tool (also known as Query/400).

IBM DB2 Web Query for i preserves investments in the reports developed with Query/400 by offering a choice of importing definitions into the new technology or continuing to run existing Query/400 reports as-is. But it also offers significant productivity and performance enhancements by leveraging the latest in DB2 for i query optimization technology.

This IBM Redbooks® publication provides a broad understanding of the new DB2 Web Query product. It entails a group of self-explanatory tutorials to help you get up to speed quickly. Overall, this book is designed for IT users. You can use Part 2, "Tutorials for DB2 Web Query" on page 161, as stand-alone tutorials for anyone who is developing their own queries.

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. Part 1 Background, installation, and setup
  5. Chapter 1. Product overview and architecture
    1. 1.1 Product background
      1. 1.1.1 Taking advantage of DB2 for i analytics processing power
      2. 1.1.2 Optimizing the analytics with a dedicated, optimized server environment
    2. 1.2 Product structure
    3. 1.3 Metadata
    4. 1.4 Architecture
      1. 1.4.1 Web browser clients
      2. 1.4.2 Web server
      3. 1.4.3 Application server
      4. 1.4.4 Reporting server
      5. 1.4.5 Data adapters
    5. 1.5 Request for changes on Web Query
  6. Chapter 2. Installation and server operations
    1. 2.1 Installation and setup
      1. 2.1.1 Installing DB2 Web Query
      2. 2.1.2 Authorizing and verifying users
      3. 2.1.3 License keys
      4. 2.1.4 Dynamic Language Switching
      5. 2.1.5 Sample database
    2. 2.2 Requirements
      1. 2.2.1 PC requirements
      2. 2.2.2 IBM i requirements
      3. 2.2.3 Developer Workbench requirements
    3. 2.3 Web Query administrative commands
    4. 2.4 DB2 Web Query server jobs
    5. 2.5 Running QU2 and WQX concurrently
  7. Chapter 3. Defining metadata
    1. 3.1 What metadata is
      1. 3.1.1 Benefits of metadata
    2. 3.2 Creating metadata
      1. 3.2.1 Creating metadata with the web interface
      2. 3.2.2 Creating metadata with Developer Workbench
      3. 3.2.3 Creating metadata with the CL command CRTWQSYN
    3. 3.3 Deleting metadata
      1. 3.3.1 Deleting metadata with the web interface
      2. 3.3.2 Deleting metadata with the Developer Workbench tool
      3. 3.3.3 Deleting files in the IFS
    4. 3.4 Refreshing metadata when IBM i database object structure changes
      1. 3.4.1 Refreshing metadata with the browser interface
      2. 3.4.2 Refreshing metadata with Developer Workbench
      3. 3.4.3 Refreshing metadata with the command CRTWQSYN
      4. 3.4.4 What happens to metadata if the IBM i database object is deleted
    5. 3.5 Miscellaneous considerations about creating metadata
      1. 3.5.1 Stored procedure synonyms
      2. 3.5.2 Considerations with multimember files
    6. 3.6 Joining database objects
      1. 3.6.1 Referential integrity
      2. 3.6.2 Creating metadata on tables with referential integrity
      3. 3.6.3 Creating metadata over tables without referential integrity
      4. 3.6.4 Defining joins in SQL views
      5. 3.6.5 Defining joins in DB2 Web Query synonyms
    7. 3.7 Date decomposition
      1. 3.7.1 Date decomposition with web interface
      2. 3.7.2 Date decomposition using Developer Workbench
    8. 3.8 Understanding your data
    9. 3.9 Field formatting
      1. 3.9.1 Field formatting with the web interface
      2. 3.9.2 Field formatting with Developer Workbench
    10. 3.10 New fields: Define versus Compute
      1. 3.10.1 Define field: Web interface
      2. 3.10.2 Define field: Developer Workbench
      3. 3.10.3 Compute field: Web interface
      4. 3.10.4 Compute field: Developer Workbench
    11. 3.11 Creating filters
      1. 3.11.1 Creating filters with the web interface
      2. 3.11.2 Creating filters with Developer Workbench
    12. 3.12 Defining dimensions: InfoMini and OLAP
    13. 3.13 Segmenting and securing Metadata: DBA
      1. 3.13.1 Field level security, No Print
      2. 3.13.2 Row level security, Value
    14. 3.14 Business views
  8. Chapter 4. Security Center
    1. 4.1 Security architecture and concepts
      1. 4.1.1 Security architecture
      2. 4.1.2 Security concepts
    2. 4.2 System i security
    3. 4.3 Groups and their capabilities
    4. 4.4 Top level folders and subfolders
      1. 4.4.1 Top level folders
      2. 4.4.2 Subfolders
      3. 4.4.3 Controlling accessibility to subfolders and reporting objects
      4. 4.4.4 Controlling order of elements
  9. Part 2 Tutorials for DB2 Web Query
  10. Chapter 5. Getting started with the tutorials
    1. 5.1 The “Century Challenge”
    2. 5.2 The assignments
    3. 5.3 The QWQCENT library
      1. 5.3.1 Restoring QWQCENT on your system
      2. 5.3.2 Downloading completed tutorials
  11. Chapter 6. Security Center: Setting up users
    1. 6.1 How to open the Security Center
    2. 6.2 Registering user profiles to DB2 Web Query
    3. 6.3 How to designate a Web Query Administrator
    4. 6.4 Removing a user from a Web Query group
    5. 6.5 Adding a developer to a Top Level Folder
    6. 6.6 Giving run-only authority to an existing IBM i group profile
    7. 6.7 How to manage users and their attributes
    8. 6.8 Changing the owner of a report
    9. 6.9 Preparing steps for the tutorial
  12. Chapter 7. Creating and editing metadata: Century database
    1. 7.1 Creating metadata using the metadata creation wizard
    2. 7.2 Enhancing metadata: Join
    3. 7.3 Date decomposition
  13. Chapter 8. Assignment #1: Summary reports
    1. 8.1 Revenue summary
      1. 8.1.1 Creating the revenue summary report
      2. 8.1.2 Adding a gross profit Define field
      3. 8.1.3 Adding a header and footer
      4. 8.1.4 Changing the theme
      5. 8.1.5 Adding traffic lighting
      6. 8.1.6 Publishing the report
    2. 8.2 Top 10 (ten) products by margin
      1. 8.2.1 Opening and saving a new copy of the report
      2. 8.2.2 Adding by field
      3. 8.2.3 Creating Define and Compute fields
      4. 8.2.4 Adding rank to the profit margin
      5. 8.2.5 Adding data visualization bars
    3. 8.3 Revenue summary with subtotals
    4. 8.4 Simple revenue report group by year
    5. 8.5 Creating Define fields and Compute fields in the synonym
      1. 8.5.1 Creating a profit field by using a Define field
      2. 8.5.2 Creating a margin field by using a Compute field
  14. Chapter 9. Assignment #2: Implementing various date functions in reports
    1. 9.1 Dates using DB2 Web Query functions
      1. 9.1.1 Changing the report theme
      2. 9.1.2 Using the aggregation function
      3. 9.1.3 Adding the month name as a Define field
    2. 9.2 Dates using SQL prefix
    3. 9.3 Dates using date table
  15. Chapter 10. Assignment #3: Implementing other report features and formats
    1. 10.1 Table of Contents
    2. 10.2 Accordion
    3. 10.3 Pages on Demand
    4. 10.4 Stack Measures
    5. 10.5 PDF
    6. 10.6 Excel formula
    7. 10.7 Active Reports
      1. 10.7.1 Sorting
      2. 10.7.2 Filtering
      3. 10.7.3 Charting
      4. 10.7.4 Calculate: % of total
      5. 10.7.5 Rollup
      6. 10.7.6 Rollup result converted to chart
      7. 10.7.7 Chart Tool
      8. 10.7.8 Exporting a chart to Excel
      9. 10.7.9 Pivot Tool
      10. 10.7.10 Adding comments
      11. 10.7.11 Notes for Active Reports
    8. 10.8 Active Flash
  16. Chapter 11. Assignment #4: Charting
    1. 11.1 The assignment
    2. 11.2 Pie chart: Revenue and Gross Profit by Product Type
    3. 11.3 Bar chart: OTD by Product Type
    4. 11.4 Vertical Stacked Area: Orders Backlog by Period
    5. 11.5 Gauge chart: OTD overall
  17. Chapter 12. Assignment #5: Adding filters to reports and charts
    1. 12.1 Simple filters: Revenue Simple Filter report
    2. 12.2 Advanced filters: Revenue Advanced Filter report
    3. 12.3 Filters in charts: Revenue trend graph with a variable date range
      1. 12.3.1 Creating a line graph
      2. 12.3.2 Adding a user-specified date range parameter
    4. 12.4 Filter in metadata: Europe Revenue and Profit report
      1. 12.4.1 Defining a filter in a synonym
      2. 12.4.2 Using predefined filters in a report
    5. 12.5 InfoMini Slicers: InfoMini Revenue and Cost report
      1. 12.5.1 Defining dimensions in a synonym
      2. 12.5.2 InfoMini Slicers report
    6. 12.6 InfoMini Formats: InfoMini Revenue and Cost Format report
    7. 12.7 Publishing your folder
  18. Chapter 13. Assignment #6: Detail/print reports
    1. 13.1 A graphical structure of the application
    2. 13.2 Creating the basic summary report with Info Assist (6a - Basic Summary Report)
      1. 13.2.1 Creating an initial summary report
    3. 13.3 Creating a summary report with a parameter (6b - Summary Report Product Category)
    4. 13.4 Creating a detail report with a parameter (6c - Detail Report Product Number)
    5. 13.5 Converting a report to a chart
    6. 13.6 Creating a chart (6d - Chart Good Profit)
    7. 13.7 Creating drill downs and putting it all together
      1. 13.7.1 Conditional drill downs from the Basic report
      2. 13.7.2 Non-conditional drill down from the second to the third report
      3. 13.7.3 Publishing the reports and charts
  19. Chapter 14. Assignment #7: Implementing OLAP
    1. 14.1 Assignment
    2. 14.2 OLAP terminology
    3. 14.3 Turning on the OLAP option in Info Assist
      1. 14.3.1 Creating an initial report
      2. 14.3.2 Enabling an OLAP report: How to use Auto Drill & Analysis
    4. 14.4 Working with an OLAP enabled report
      1. 14.4.1 Slicing, dicing, and drilling down for more details
      2. 14.4.2 Manipulating the data using the OLAP Control Panel
  20. Chapter 15. Assignment #8: Building documents and dashboards
    1. 15.1 Documents and dashboards
      1. 15.1.1 Prerequisites
      2. 15.1.2 What a dashboard is
      3. 15.1.3 Developing KPIs
      4. 15.1.4 Business leader requests
      5. 15.1.5 Creating a dashboard document with Info Assist
      6. 15.1.6 Viewing business data on popular devices
    2. 15.2 Creating a document, adding an internally defined chart
      1. 15.2.1 Starting a new document
      2. 15.2.2 Adding a chart
      3. 15.2.3 Manipulating fields for the chart
      4. 15.2.4 Making layout changes to improve the look of the chart
    3. 15.3 Adding existing reports and charts to a document
      1. 15.3.1 Opening the existing document
      2. 15.3.2 Inserting an existing report
      3. 15.3.3 Inserting more charts or reports into an existing document
  21. Chapter 16. Assignment #9: Exploring and comparing other dashboard options
    1. 16.1 Additional features of Web Query for documents
      1. 16.1.1 Prerequisites
    2. 16.2 A coordinated document
    3. 16.3 Creating documents with Form Control fields.
    4. 16.4 Self-service dashboards using the BI Portal
      1. 16.4.1 Favorites and Mobile Favorites
      2. 16.4.2 Creating a Personal BI Portal
    5. 16.5 DB2 Web Query Developer Workbench
      1. 16.5.1 Why you would use Developer Workbench
    6. 16.6 Dashboard using HTML Composer
      1. 16.6.1 Comparison between Info Assist and HTML Composer
      2. 16.6.2 Compound parameterized report (HTML1_KPI)
      3. 16.6.3 Preparing the charts for HTML Composer
      4. 16.6.4 Adding controls to the HTML Composer canvas
      5. 16.6.5 Referencing external procedures in HTML Composer
    7. 16.7 DB2 Web Query Spreadsheet client
      1. 16.7.1 Creating a dashboard in Excel
  22. Chapter 17. Assignment #10: Scheduling and distribution of reports
    1. 17.1 Report Broker overview
    2. 17.2 The assignment
    3. 17.3 Configuring Report Broker
    4. 17.4 Scheduling a simple report and distributing by email
    5. 17.5 Scheduling a report with parameters and distributing it via email
      1. 17.5.1 Creating an Email Distribution List
      2. 17.5.2 Scheduling the report
    6. 17.6 Scheduling a report and distributing it by DB2 Web Query
    7. 17.7 Scheduling a report and distributing it by FTP
    8. 17.8 Scheduling a report and distributing by printer
    9. 17.9 RUNBRSCHED command
    10. 17.10 Report Broker Console
  23. Chapter 18. Assignment #11: Going mobile
    1. 18.1 Mobile access assignment
    2. 18.2 Preparing reports for mobile access
    3. 18.3 Accessing mobile content using the web browser
    4. 18.4 Using Mobile Faves app
      1. 18.4.1 Installing Mobile Faves
      2. 18.4.2 Connecting to reporting server and running reports
      3. 18.4.3 Adding additional reports to mobile favorites
      4. 18.4.4 Saving reports for offline view
    5. 18.5 Opening reports received by email
    6. 18.6 How to use Active Technologies on mobile devices
  24. Chapter 19. Assignment #12: Stored procedures in action
    1. 19.1 Getting the most from your data with stored procedures
      1. 19.1.1 Prerequisites
    2. 19.2 Creating a stored procedure synonym
      1. 19.2.1 Reasons you might use a stored procedure
    3. 19.3 Example of an HR-related stored procedure
      1. 19.3.1 Creating the synonym for the stored procedure example
    4. 19.4 Creating a report using the stored procedure synonym
      1. 19.4.1 Starting Info Assist to create the report
      2. 19.4.2 Establishing the join between Plant table and stored procedure
      3. 19.4.3 Formatting the report
      4. 19.4.4 Saving your report based on a stored procedure
    5. 19.5 Example of an input parameter stored procedure in RPG
      1. 19.5.1 Creating the synonym for the MULTINPARM stored procedure
      2. 19.5.2 Creating the report from the MULTINPARM stored procedure synonym
      3. 19.5.3 Using Info Assist to create the report
      4. 19.5.4 Formatting the report
  25. Chapter 20. Assignment #13: Query/400 modernization
    1. 20.1 Query/400: a reliable reporting tool
    2. 20.2 DB2 Web Query versus Query/400: Function similarities
    3. 20.3 DB2 Web Query versus Query/400: Function differences
      1. 20.3.1 Parameter passing
      2. 20.3.2 Using output files for the next query
    4. 20.4 The assignment
    5. 20.5 Creating metadata over Query/400 objects
    6. 20.6 Using DB2 Web Query to edit a QRYDFN
    7. 20.7 Modernizing Query/400 definitions
      1. 20.7.1 Building the initial report
    8. 20.8 The migration dilemma for Query/400 reporting
    9. 20.9 Modernizing your reporting
      1. 20.9.1 IBM offering for Query modernization
    10. 20.10 Replacing Query/400 objects with Active Reports
      1. 20.10.1 Highly parameterized report development
      2. 20.10.2 Creating the report in HTML Composer
      3. 20.10.3 Parameter controls
      4. 20.10.4 Chaining the controls together
      5. 20.10.5 Using calendar controls
      6. 20.10.6 Providing multiple report output format options for users
    11. 20.11 Additional parameter tips
      1. 20.11.1 Displaying the values of one column while passing another
      2. 20.11.2 Using another report to control parameter sorting and filtering
  26. Chapter 21. Assignment #14: Creating JD Edwards reports
    1. 21.1 Connecting to a remote JD Edwards database using adapters for JD Edwards
      1. 21.1.1 Areas to consider when connecting to a remote JD Edwards database
      2. 21.1.2 Using JD Edwards adapter to connect to a remote JD Edwards database
      3. 21.1.3 JD Edwards dictionary files needed on local system to access remote JD Edwards databases
    2. 21.2 Using the Adapter for JD Edwards World
      1. 21.2.1 Preparing the JD Edwards World environment
      2. 21.2.2 Overview of the setup process
      3. 21.2.3 Configuring the Adapter for JD Edwards World
      4. 21.2.4 Refreshing the Metadata repository
      5. 21.2.5 Creating the JD Edwards World synonyms
    3. 21.3 Using the adapter for JD Edwards EnterpriseOne
      1. 21.3.1 Preparing the JD Edwards EnterpriseOne environment
      2. 21.3.2 Overview of the setup process
      3. 21.3.3 Configuring the adapter for JD Edwards EnterpriseOne
      4. 21.3.4 Refreshing the metadata repository
      5. 21.3.5 Refreshing the security extract
      6. 21.3.6 Creating the JD Edwards EnterpriseOne synonyms
    4. 21.4 Developing a JD Edwards report
  27. Chapter 22. The Century Challenge BI solution: Postmortem
    1. 22.1 Summary
  28. Part 3 Miscellaneous topics
  29. Chapter 23. Performance case study
    1. 23.1 Performance basics
    2. 23.2 DB2 CLI adapter performance
      1. 23.2.1 Report request process flow
      2. 23.2.2 Adapter processing and optimization
      3. 23.2.3 Remote database access considerations (including cross-system joining)
    3. 23.3 Query/400 adapter performance
    4. 23.4 DB2 for i optimization
      1. 23.4.1 Database design
      2. 23.4.2 Query Engine used
      3. 23.4.3 Indexes
      4. 23.4.4 Available hardware
      5. 23.4.5 Concurrent users
    5. 23.5 Performance case study
      1. 23.5.1 Identifying a long-running report
      2. 23.5.2 Performing analysis and looking for optimization disablers
      3. 23.5.3 Determining report-tuning options
      4. 23.5.4 Creating an SQL view and synonym
      5. 23.5.5 Creating a new report based on the SQL view
      6. 23.5.6 Performing additional database analysis and tuning
    6. 23.6 Performance benchmark
      1. 23.6.1 Objectives
      2. 23.6.2 Scenarios
      3. 23.6.3 Database and system configuration
      4. 23.6.4 Metrics
      5. 23.6.5 Conclusions
  30. Chapter 24. Migration considerations
    1. 24.1 New concepts and terms in Web Query 2.1.0
      1. 24.1.1 Business Intelligence Portal
      2. 24.1.2 New Web Query Administration User ID
      3. 24.1.3 Web Query folders
    2. 24.2 Migrating from Release 1.1.x
      1. 24.2.1 Migrating Web Query users, Group Profile license information, and metadata
      2. 24.2.2 Migrating Web Query content
    3. 24.3 Other considerations
  31. Chapter 25. IBM i Business Intelligence solution
    1. 25.1 Operational data stores, data marts, and data warehouses
    2. 25.2 Introducing the new IBM i for Business Intelligence solution
      1. 25.2.1 DB2 Web Query for i Standard Edition
  32. Chapter 26. Adapter for Microsoft SQL Server
    1. 26.1 Installation
    2. 26.2 Establishing a connection to your Microsoft SQL Server
    3. 26.3 Creating metadata
    4. 26.4 Additional SQL Server adapter information
  33. Part 4 Appendixes
  34. Appendix A. Date and time functionality
    1. Date and time system variables
    2. Date format
    3. Date format display options
    4. Controlling the date separator
    5. Using date fields
    6. Date fields in arithmetic expressions
    7. Converting date fields
    8. DB2 Web Query date built-in functions
    9. Example: Dynamic Date Range report
  35. Appendix B. DB2 Web Query system variables
  36. Appendix C. Change management considerations
    1. Change Management overview
    2. Change Management scenarios
    3. Exporting a change management package
    4. Importing a change management package
  37. Appendix D. Configuring Developer Workbench
    1. Configuring Developer Workbench
    2. Setting Developer Workbench default options
  38. Back cover