...

...

...

Introduction to Discoverer for End Users

Overview

Introduction to Discoverer for End Users

Distribution

UVA ODS Specialist

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Section Objectives

At the end of this course, you should understand the:

• Three types of reports used by the Integrated System
• Relevance of Discoverer as a query tool
• Reporting Regulations
• ODS Structure
• Key Terminology
• Where to find help

Reports Overview

There are three types of reports used with the Integrated System:

• Standard Reports - Contained in and run from the Integrated System applications.  These standard reports cannot be accessed through Discover.
• Pre-Defined Reports (PDR) - Created by ISDS developers and opened with Discoverer from the Operational Data Store
• User-Defined Reports (UDR) - Created by users and  saved on the user’s pc.

Discoverer is the only application that will open Pre-Defined (PDR) reports.  It may also be used to create User-Defined (UDR) reports or modify Pre-Defined reports to your needs. Users who have taken any training on the Integrated System applications may use Discoverer to open PDR and UDR reports, and/or create UDR reports.

Pre-Defined Reports have been created by the Integrated System developers and can be identified by their naming convention. The Report names begin with the characters IS, a period, two characters identifying the module most of the information comes from, then an underscore followed by the report's title:

AP = Accounts Payable AR = Accounts Receivable FM = Funds Management (Grants Accounting) GL = General Ledger IN = Inventory OE = Order Entry PO = Purchase Order LD = Labor Distribution HR = Human Resources PY = Payroll

Reports that combine data from Grants Accounting and General Ledger will most often use the Funds Management prefix: FM

Reports that begin with other characters are User-Defined Reports, created by a Discoverer user, and shared with others. The first 4-5 characters of the report's name are the university computing ID of the creator, a period, and then the report's title.

Discoverer Overview

Discoverer is a powerful and easy-to-use query and analysis tool developed by Oracle to enable end-users to view data stored in the Integrated System database, and to analyze and create reports based on that data. Discoverer is a highly intuitive tool with a similar look and feel to Microsoft Excel. It uses workbook wizards to guide you through all the steps you need to create a query.

The Integrated System application modules come with hundreds of standard reports. Discoverer is not a replacement for the Standard reports, but is an enhancement that’s intended to give you greater power to access your data.

Predefined reports and queries are the heart of most information systems. Organizations invest a lot of money in developing them. Traditionally, queries and reports are fixed and impossible for the end-user to change. However, users and viewers of Discoverer can rearrange the data received from a query.  Data sets can be sorted, sequences changed, cross tabs created, etc, allowing the end-user to analyze data from many different angles with one basic query.

Every organization needs to be able to readily access and analyze administrative data in order to make informed business decisions.  Data that is entered into a database can be queried to pull the required information from the database.

When we create a report in Discoverer, we actually create a query.

What is a query?

• A query asks the database a question.

• To define the “question” you need to understand the data available to you, where and how you can access the data, and what you want the end result to be.

Knowledge of your business, reporting requirements, and the database, will be critical in designing and running an effective query.

What are we referring to when we say know your business, your reporting requirements, and the database?

• Your Business

• Know your organization – its objectives, what it does, and how it does it

• Your Reporting Requirements

• Things you need to know about your business
• Things you want to know about your business

• The Database

• Know what data is available and its relevance to your business
• Know from which business area you will be obtaining the information

Knowledge in these three areas – the business, reporting requirements, and the database, will be critical in designing and running an effective query. 

Key Terms

Business Area:  A Business Area is a logical grouping of database tables or views that apply to your specific data requirements. 

For example, the Purchasing department wants data about Purchase Orders and Invoices, while project managers want data specifically about their projects as well as expenses and budgets. Although some of the data you need may be the same, the exact combination of tables and views for each department is usually unique. Using Discoverer's Administration Edition, your Discoverer administrator tailors the grouping of data to provide you with the proper access to the precise data needed for analysis, decision support, and presentation of results.

Folders: Folders store details about groups of related information. For example, all details about employees of an organization may be stored in one folder, while information about vendors is stored in another folder. Folders map to database tables, to views, or to combinations of tables and views.

Items: Folders are composed of items. Items map to columns in a database table. Each item has a name and contains a specific type of information. For example, if a folder contains details about employees, the items may include the employee’s name, start date and department. When you include an item in a query, it is known as a query item. An item can also be calculated or derived.

The Integrated System financial application the business areas are:

• General Ledger (GL) – the repository of all financial transactions for the University. 
• Grants Accounting (GA; PBPA and PCPA expenditure sub-ledgers) contains expenses, commitments, and budgets for Awards/Projects/Tasks.
• Labor Distribution (LD) – Contains payroll labor costs allocated to PTAEOs, as well as adjustments when labor is charged to the wrong PTAEO.
• Accounts Receivable & Cash Management (AR) – Provides customer account management information, including billing for Sponsored Programs. Cash Management contains cash related transactions generated by IS Receivables, Payables, and GL as shown on the University’s Bank statement. 
• Fixed Assets (FA) – Contains all the University’s fixed asset information including asset cost, ownership, location, and depreciation.
• Purchasing & Accounts Payable (PO & AP) - Contains transactions of procurement and payment, vendor information, etc. 
• Order Entry & Inventory (OE & IN) – Contains Chemstores (internal service provider) shipment and billing information.

The Human Resource business areas contains the following infomation:

• Human Resource Management - Contains all employee data from the applicant to retirement.
• Payroll – Contains payroll, as well as related HRM information in the Human Resource Management System to pay employees
• Time Management - Contains employee time and leave records. 
• HR Training Administration - Contains registration for training events, budgets and costs of training events, as well as documenting the skills and competencies gained by the employees who participate in training.

The Student Information Project is under way.  After implementation (Phase III), this application will replace the University's current student information system, ISIS.  The business area will contain student information. 

Data Types

Rows: A folder or item consists of any number of rows, each of which represents all the information about one member of that folder.

Queries: A query is composed of query items and retrieves information from the database. Running a query produces a report.

Workbooks and Worksheets: Workbooks contain worksheets. A worksheet contains information retrieved from the database based on the items selected in the query. The results from running a query are often referred to as a workbook, or a report.  Each workbook (or report) contains one or more worksheets.  This feature of Discoverer has the same look and feel as Excel workbooks and worksheets.  In Discoverer, you can produce different styles of reports.

Reporting Requirements and Query Development

Developers of the ISDS team have created Predefined Reports (PDRs) that contain information commonly required by Discoverer users.  In a lot of cases, these PDRs can be used as is or may be slightly modified to meet your reporting needs.  Other reporting requirements may necessitate development of a report from scratch.  With Discoverer, you can substantially modify existing PDRs and create reports resulting from the queries that you create.

Discoverer places power in the hands of the user. It is a tool that allows the user to view data stored in the Operational Data Store (ODS) and analyze and create customized reports.  With querying you can easily:

• Change the output and format
• Rearrange the data
• Remove unnecessary information
• Change the analysis

To design an effective query consider your reporting requirements.  Develop your query based on the following preliminary steps:

• Step 1:       Ask yourself, what information do I already have?
• Step 2:       Ask yourself, what do I need to know?
• Step 3:       Ask yourself, what would I like to know?
• Step 4:       Use an incremental approach - don’t try to answer every question by using one query.
• Step 5:       Document the “why” behind development and “what” the query is supposed to do and “who” requires this information.  (This step is helpful should your reporting requirements change.  It provides a history of the development process and a point of reflection.)

Once you have completed this process, you are ready to create the query and find the data that will answer the proposed questions.  After obtaining a working query, you will want to format the report so that it is easy to read, attractive and logically organized. 

The Database

Knowing Your Data

All data entered into the Integrated System in production is stored in a “live” database which is copied nightly to the Operational Data Store (ODS).  Therefore, the data in ODS is as recent as the last copy.  By copying the production data, the reporting data requests do not compete with the data entry of transactions in the production system. 

Figure 2.  Data Processing

The data in the ODS is refreshed every night, and reflects transactions that were processed the previous day.  IS production is backed up nightly and the IS nightly backup process kicks off shortly at the end of the work day.  After that, the ODS refresh job begins, reflecting transactions that were processed the previous evening. The ODS database will not have transactions posted during the nightly process

IS and the Operational Data Store

What are some other features of the Operational Data Store (ODS)?

• It is a separate database, on a separate machine from the primary Integrated System application database, created specifically for reporting purposes.
• This is a copy of production that is static for a 24 hour period.
• The data in the ODS is an exact copy of what is in the application database the previous evening. (Some data in the ODS may appear to be older, as the copy is made before batch processing).
• When you use Discoverer, the data will be categorized by business areas and made available to you through folders (also known as database views).
• Database views are virtual tables that combine data from different Integrated System tables in order to make query development a simpler task, and retrieve data in an efficient manner.
• The ODS contains financial information beginning with fiscal year 2002 (i.e. the July 2001 accounting period).  HR records were transferred to the IS effective 25-Sep-2002.  Actions initiated after 25-Sep-2002 can be viewed by using the date track feature in the Integrated System.  Historical data can also be viewed using the Information Warehouse.  See Figure 1.

Figure 1.  Data/Reporting and the ODS

How can you access the ODS?

• The ODS is located behind the UVA firewall. You must be logged in to the Virtual Private Network (VPN) in order to access the ODS.  (The VPN client and server are used to require more secure passwords when accessing the Integrated System services.)
• Discoverer is the primary tool that is being used at UVA to query the ODS, (although other tools may be used).
• In order to use Discoverer or other tools with the ODS, you need to have an active Integrated System account, including a UVA ODS Specialist responsibility.

• If you wish to use a tool other than Discover, the responsibilities UVA GL Accountant, UVA GL Accounting Manager, UVA GL Administrator have the ability to define FSG (Financial Statement Generator) reports.

• A responsibility is a predefined set of data, menus, and forms that define your particular level of authority and what forms and information you have access to while using the ODS or the Integrated System.  A UVA ODS Specialist responsibility is assigned to individuals who have completed the required training for any IS responsibility.  UVA ODS Specialists are authorized to create or view reports, using the financial business areas, from the Operational Data Store using the Discoverer tool.  UVA ODS Specialist-Limited responsibility allows the authorized individual to create or view reports in the HR business areas, as well as in the financial business areas.

• Every area has been assigned a data security contact (DSC) who can assist you in applying for an Integrated System Responsibility.

Role of the Discoverer Administrator

It is the responsibility of the discoverer administrator to create and maintain the End User Layer, and the conceptual view of the database. The Discoverer Administrator performs many functions, including the following:

• Defining business areas
• Controlling user access
• DB Security
• Building hierarchies
• Creating views and joins

During login to the Integrated System Applications, the responsibility screen will display UVA ODS Specialist or UVA ODS Specialist-Limited as one of the choices.  Discoverer uses these responsibilities to grant you access to associated business areas.  Selecting that responsibility while you are in the Integrated System will not give you access to the Operational Data Store or Discoverer.  You will need to activate the VPN and logon to Discoverer.

End User Layer

The End User Layer (EUL) is the interface between you and the database.  What you see when you log onto Discoverer is the EUL.  It maintains all the links between you and the underlying database.  Whenever you create a Discoverer Query, it is converted into a Structured Query Language (SQL) statement by the EUL.

The EUL is a metalayer structure that preserves the database’s data integrity. Whatever the administrator or the end user does with Discoverer, it affects only the metadata in the EUL and not the database. Discoverer provides read-only access to the database.

Data Access

Within the End User Layer, related folders are grouped into business areas. Users can be granted access to more than one business area, and the same business area can be granted to more than one user. Also, the same object can be present in more than one business area.  You can include items from a folder in your query only if you have access to the business area that contains the data.

The data entered into IS as online transactions is transferred to the ODS for reporting purposes.

Where to Get Helpful Information

Learning Discoverer

Online Help: Select Help—>Topics to search the Help system for a particular topic. For context-sensitive Help, select a topic and then click the Help button in the active dialog box.

User Guide: Select Help—>Manuals to view the Discoverer on-line guide.

Quick Tour: This online tutorial introduces Discoverer.  Select Help—>Quick Tour to launch this Help facility.

Cue Cards: Use these step-by-step instructions for help performing common tasks. Select Help—>Cue Cards to launch this Help facility.

Wizards: These tools automate frequently performed tasks, such as building ad hoc queries.

Accessing Online Help

Integrated System How Do I….Web Help

Discoverer training “Help” is available via the Integrated System How do I…. web pages. 

URL: http://www.virginia.edu/integratedsystem/howdoi/reports.html

Applications Library

You can gain access to the online Discoverer User’s Guide via below path.

1.      Open Discoverer ® Select [Help / Manuals].

2.      Click a topic of interest to display a list of subtopics.

4.      Click a topic to display a window with Help on that topic. Use the buttons in the Help window to navigate the online Help.

5.      Select Contents from the Help window to return to the main list of topics for this product at any time.

6.      Select File -> Exit to close the Help window when you are finished.

Discoverer Online Help Options

Figure 1 Help Topics

Figure 2 Cue Cards

Figure 3 Discoverer Glossary in the Help Topics

Summary

In this chapter, we have discussed the concept of creating reports in Discoverer.  In order to develop high-quality reports, you need to understand your business, your reporting requirements and the database.  You also need to understand the underlying operations of the Integrated System as it relates to reporting. We have completed an overview of the Integrated System at the University and how this system is used by the business operations to input and store data and financial transaction information.  At the end of this chapter, a listing of the Help Resources for information on the ODS was provided.

In this course, you should have learned:

• Where to find help for Discoverer
• Key terminology
• The Role of the Discoverer Administrator
• Describe the Operational Data Store (ODS)
• Differentiate between Integrated System Standard reports and Discoverer reports
• Know your resources for help with Discoverer and the ODS

  Effective: 02/16/06

  Revision: 2 njk

  ---

  Copyright 2004 University of Virginia and Oracle Corporation. All rights reserved.

  Oracle® Tutor   Copyright © 1997, 2004, Oracle. All rights reserved.