Ad Hoc Query

1
Ad Hoc Query Reporting Thread Tool
Recommendation

• Ad Hoc Query Reporting Thread
• March 26, 2002

2
Project Organization

• Pre-Programmed Reporting Thread
• Focused on the manipulation of pre-programmed
  (canned) reports that are currently generated in
  IMPAC II
• Ad Hoc Reporting Thread
• Focused on allowing the NIH user community to
  query the IMPAC II DB (OLTP IRDB) to retrieve
  data on an ad hoc basis.

3
Ad Hoc Q R Team

• Carol Martin, Group Advocate
• Krishna Collie, Analyst
• Jim Tucker, Analyst

4
Executive Summary

• User requirement provide and support a desktop
  ad hoc query reporting tool
• Analysis evaluated 4 COTS tools against stated
  requirements and tested using pre-defined report
  and selection criteria.
• Result - None of the tested tools met all of the
  MUST criteria.
• Recommendation
• Conduct user trial using Oracle Discoverer
  (recommended for pre-pgm.reports)

5
Objectives

• To retrieve data from the IRDB current, history
  and frozen (PUB) files and/or the OLTP current
  files on an ad hoc basis
• To produce formatted reports
• To export data to spreadsheet or ASCII delimited
  file
• To publish reports to the Web

6
Categories of Evaluation Criteria Used

• General Criteria Must, Should, Could
• Support and Maintenance Criteria
• Security Criteria
• Performance Metrics
• Other Considerations

7
COSSET
8
Functional Requirements

• Ease of use by end user
• Allow users the ability to craft queries
• Allow users ability to save queries
• Export report to Excel and/or ASCII delimited
  file

9
Technical Requirements

• Must be easy to configure, use and maintain
• Ease of creating/maintaining meta-layer
• Scalability across the enterprise
• Ability to self-join table more than once and use
  the self-joined tables

10
Architectural Issues

• Integration into eRA enterprise
• Installation on Users Desktops
• Seat Licenses
• Web or Client/Server version?

11
Vendor Survey

• Market Leaders
• Hummingbird
• COGNOS
• Business Objects
• ORACLE Discoverer
• (Gartner Group Survey)

12
Finalists

• While none of the tools met all musts,
  Discoverer merits careful consideration for
  following reasons
• Discoverer selected for pre-programmed reports
• OER already owns licenses for Discoverer
• NCI staff successfully using tool for ad-hoc
  query
• Other new IMPAC II products available to assist
  power users
• ICSTORe export custom hit list to Excel Advanced
  Query
• Pre-pgm. reports for user export/manipulation to
  be developed.

13
Product Evaluation

• Queries
• Complex
• Simple
• Report Formatting
• Multi-lines per record
• Headers and Footers
• Borders, Colors, Shading, Fonts

14
Sample of Test Report
15
Sample Test Complex Report
16
Results of Evaluation

• Hummingbird
• Difficult to use
• Cannot sort in the report
• Must save query separately from report in order
  to make changes
• Meta-layer setup difficult

17
Results of Evaluation

• Cognos
• Meta-layer setup difficult
• Cant self join a table more than 1 time
• Net80 directory must be on local hard drive
• After report is formatted, cannot add another
  data item

18
Results of Evaluation

• Business Objects
• Cant self join a table more than 1 time
• Not easy to add new data items to formatted
  report
• Very cumbersome to format multi-line report
• When partials results are returned, no way to
  next batch of results

19
Results of Evaluation

• Oracle Discoverer
• Requires careful meta-layer design maintenance
  to facilitate user friendliness
• Lacks advanced formatting capabilities
• Object terminology very confusing
• End-user training and support essential

20
Recommendation

• Conduct a live demo of Discoverer (IRDBDEV)
• Present demo to user groups
• RUG
• ePUG
• ADP/EP
• Determine if their needs are satisfied by this
  tool
• Group Advocate to continue evaluating other tools