ABLE has help resources for all of the financial and HR applications ABLE's collection of forms Harvard's financial policies and procedures Go to the financial applications log on page Go to HARVIE, Harvard's HR intranet, where you can log onto PeopleSoft (requires a PIN)
 
 
data models, definitions, connections
 • 
 DWCENPRD data model
 • 
 data dictionary (Excel)
 • 
 HDWPROD to DWCENPRD view mapper
 • 
 creating an OCE connection in Brio Explorer
 • 
 Database Connectivity
 
release and testing resources
 • 
 Quarterly release update
 • 
 Recording and Submitting Test Results & Report Ad Hoc Problems
 
tips & tricks
 • 
 Hyperion Intelligence Tips & Tricks "Cookbook"
 • 
 Hyperion Intelligence Cookbook 2007
 
getting help
 • 
 local contacts for financial ad hoc reporting
 • 
 financial authorized requestors
 • 
 ABCD Brio working group
 • 
 common Brio messages
 •  contact Administrative Systems Training
 •  submitting problematic queries

Resources for Direct Query (Ad Hoc) Access to Financial Data
in the Harvard Data Warehouse

Topics discussed in this introduction:


What is direct query access?     

Direct query or ad hoc access provides a specially trained user with the ability to directly access the underlying data structures on which the CREW reports are written and independently create their own custom reports.

Who can get this level of access?      top of page

Candidates for direct query access have the following basic qualifications:

  • An advanced understanding of the available suite of standard reports in CREW and their capabilities
  • A comprehensive understanding of the subject area that will be queried (e.g., Harvard's General Ledger, Grants Management, or Human Resources data)
  • Training or experience in relational database structures
  • Training or experience in query tools like SQLPlus, Hyperion/Brio, Powerbuilder (Infomaker), etc., as well as best practices in constructing SQL-based queries

For help determining whether you are a candidate for direct query access, use the contacts listed below:

Getting started in Financial ad hoc reporting      top of page

Candidates for direct query access to the financial reporting structures in the Harvard Data Warehouse will need to perform the following actions to begin writing reports:


Training      top of page

Brio/Hyperion – Training in using the Brio/Hyperion product is offered on a routine basis through the Center for Workplace Development. Courses include:

Data Model – Introductory training to direct query access reporting listed below is offered to all users. Additional subject-area-specific training is also available. Registration for other subject area classes and other courses is available on the Center for Workplace Development pages on HARVie. NOTE: A request can be made to hold additional classes for a department and/or subject area.


DWCENPRD financial data model, business definitions, and database connections      top of page


Testing      top of page

The Harvard Data Warehouse environment is constantly evolving. Our hardware (servers, etc.) age and need to be replaced; the vendors who provide our operating systems and software require upgrades to keep our data secure and to ensure that it can continue to integrate with our hardware and software; and the vendors for our major business applications like Oracle and PeopleSoft supply fixes and enhancements for their own code to make sure it continues to meet their customers' business needs. All of these changes can have a downstream impact on the data integrity and performance of the Data Warehouse. Finally, the Data Warehouse's own customers request fixes and enhancements to the mappings, views, applications, and reports that they utilize.

In order to protect the integrity of our production systems, the accumulated upgrades, vendor-supplied fixes, enhancements, and security patches, and Harvard's own fixes and enhancements are bundled together and installed into a test version of the database or application, where technical staff and designated product managers on the business side execute a rigorous series of test scripts meant to flush out any potential issues prior to releasing the code to production. This testing and release process is scheduled three times per year in the fall (Sept/Oct), late winter (Feb/Mar), and late spring (May/Jun).

Because each direct query user of the Harvard Data Warehouse has the capability to write numerous distinct queries and reports against the reporting environments, it is not possible for HDW technical staff and product managers to conduct testing to ensure that the bundled patches, fixes, and enhancements have not negatively affected them. It is the responsibility of each user granted direct query access to the HDW to test their business-critical queries and reports during each release cycle.

To facilitate this testing prior to each release testing period, the Financial Ad Hoc Reporting support team sends out to the active financial ad hoc users listproc a summary document of HDW fixes and enhancements associated with the release, with detailed information on connecting to the test databases. The team has also established templates to be used to summarize the tests conducted and to report any issues uncovered. Documents for the upcoming/current release are available below:


System availability      top of page

Normal system hours of operation are posted on the ABLE home page at http://able.harvard.edu/.

Support service alerts are posted at http://www.uis.harvard.edu/system_status/. Critical system availability notices are also posted on the CREW gateway page and sent out via e-mail.

HDW direct query access is supported by the Help Desk from 8 a.m. to 6 p.m. Monday through Friday. Unsupported access is available at other times, except for designated “maintenance weekends” (every third week) or during FINDINI upgrades.

Please note: Source system updates with greater than average numbers of records can delay system availability (e.g., the annual process to open the new budget year can add 5-6 hours to the GL data loads).

Supported query tools      top of page

Brio Explorer 6.6.4.4 is the supported ad hoc reporting and querying tool, though direct SQL access through Oracle tools like SQL Plus and TOAD are also supported. Other query tools (e.g. Infomaker, Filemaker Pro, Access, or Paradox) can be used to access ad hoc data, but are not supported by the HDW.

Brio licenses may be obtained from UIS Enterprise Licensing at http://www.uis.harvard.edu/enterprise_licensing/about/ or by calling 617-496-SITE (7483). Contact your local desktop support and have them purchase and install a licensed version of the Brio Explorer 6.6.4.4 software. The initial cost for new Brio users is $300, which covers purchase of the software ($150) and an annual maintenance subscription for the fiscal year ($150). No pro-rating for a partial year subscription is available. The annual maintenance subscription will be charged each fiscal year as long as the software resides on the user's desktop.

Note on the Brio 6.6.4.4 product: Brio was purchased by Hyperion a few years ago and Hyperion has recently phased out the Brio brand name. As part of this conversion, Hyperion has "desupported" the Brio Explorer 6.6.4.4 product to move customers over to its Hyperion Intelligence 8.3 product. The two products are fundamentally identical: standard queries written in the Brio product can be brought into the Hyperion product the same way one might upgrade from MS Word 2000 to MS Word 2003.

Further information on the upgrade to Hyperion 8.3 will be communicated to the ad hoc community once the project plan is finalized


Additional support      top of page

Ongoing additional support is available through the following mechanisms:

  • The ABCD-Brio user group is a peer-to-peer support group comprised of Brio/Hyperion users from across the University. It meets monthly on the 3rd Thursday during the academic year (September to June) to share tips and tricks for working with Harvard's ad hoc query structures and the Brio/Hyperion query tool. Members also may subscribe to a listproc abcd-brio@abcd.harvard.edu, to which they may submit their questions and problems to the group list for potential solutions and workarounds. The following procedures describe how to sign up for this email support group. At the ABCD-Brio site, select Membership, then the Signup link. Provide the information required at the prompts and click the submit button. An email will be sent to confirm the information entered and further instructions to complete the membership registration to participate in any of the mail list groups.
  • Users are also encouraged to network with their peers. Much of the process of building queries involves finding creative solutions to constraints either in the query tool, the available data, or the reporting structures. There are times when a quick check-in with a peer can save you from “reinventing the wheel.”