Reporting from an Oracle Database: A Tiered Approach

1
Reporting from an Oracle DatabaseA Tiered
Approach
Presenters Lucretia Parham Janice
Zeigler Armstrong Atlantic State University May
14 1015 a.m. - 1115 a.m.
2
Background of the issue

• Slow and ineffective information retrieval
  methods
• Increase in the amount and complexity of
  information required from the database
• Critical role of information management in
  organizational success
• Sheer volume of the data

3
Introduction

• Armstrong Atlantic
• State University
• Savannah, Georgia
• Tiered web-based reporting solution for
  extracting information from Banner
• Levels based on the complexity of the reports

4
After this session you will be able to

• Articulate a clear vision for student information
  reporting at your institution
• Design a methodology for meaningful information
  retrieval from any database
• Implement a solution incorporating technology,
  security and user concerns

5
Multi-tiered approach

• Meta application to create standard reports
• Web site that allows users to create simple
  reports
• Interface for more sophisticated users to create
  complex reports using third party software
• Web site where users choose reports created by
  technical staff

6
Meta Application

• PL/SQL code generation capability imbedded in
  Oracle database
• Interface to code generation capability created
  by technical staff
• User defined database elements with meaningful,
  descriptive labels

7
Meta application (cont.)

• Criteria for the report selected from
  user-defined list
• Selection criteria used to generate and produce
  the report
• Report criteria saved by users for future use or
  revision

8
Meta application examples

• Registered or joint enrolled students
• Financial aid students
• Academic suspension/probation list
• Honors, freshmen, sophomores, juniors, seniors,
  post baccalaureates, athletes, senior citizens,
  graduating seniors

9
Meta application user benefits

• Generation of tabular or multi-line reports
• Creation of letters and labels
• Increased comprehension of a complex database
• Intuitive (web) interface to data
• Immediate access to critical organizational data

10
Meta application technical benefits

• Ease of implementation
• Non-proprietary solution
• Little technical staff intervention
• Availability of tools
• Cross-platform capability

11
Web site report creation

• Uses Oracles WebDB and report server products
• Allows users to browse and create simple reports
• Uses local tables and views
• Exploits the data warehouse concept

12
Web site report creation

• Examples of views created include
• Registration, Admissions, Recruiting, Financial
  Aid, Accounts Receivable, Academic History, Deans
  and Department Heads, Student Affairs, Athletic
• Design of the views/tables based on the
  requirements of the different areas

13
User benefits of web site

• Immediate feedback to the ultimate consumer of
  the information
• Simplicity of the interface
• Views and tables based on user-defined needs
• Daily updates of information

14
Technical benefits of web site

• Use of non-production database
• Authentication via Banner login name and password
• Security control through Oracle roles
• Ease of modification and maintenance

15
Using third party tools

• Crystal Reports or Access used to create reports
• More complex reporting capability made available
• Users trained by internal or external staff
• Examples include cross validation and time series
  analysis

16
Benefits of top three tiers

• Increased knowledge discovery through rapid
  report development (data mining)
• Easy extraction of previously unknown and
  potentially useful information
• True ad hoc reporting capability

17
Web Site Report Retrieval

• Mirrors the current practice
• Technical staff create reports that users run
• Available through web site
• Includes the most complex reports
• Examples include enrollment statistics

18
Issues and Concerns

• Database complexity led to creation of local
  tables and views
• Technical criteria include hardware and software
  needs
• Training must be done
• Security is always an issue

19
Software Involved in the Creation and
Presentation of Local Reports

• Report Builder 6.0.8.8.3
• Oracle Server 8.0.6.0.0
• Acrobat Reader 4.0

20
Level One User

• User only has access to run reports for area
• Can choose to AutoRun reports
• Access to Report Output

21
Level Two User
22
Level Three User
Access to Application Development Links
23
Level Four UserTop Level of Access
Access to Technical documentation and utilities
CIS access to Meta-App Development Links
24
Extra Benefits
Users have
the option to
set
preferences
for
icons and
colors.
25
Extra Benefits
Pop up help screens are available
and are customizable.
26
Extra Benefits
The ability
for users to
add their
own help

links will be
in place
soon.
27
Extra Benefits
Error messages are incorporated.
28
Extra Benefits
Color coded
error
messages are
available as

well
29
Summary

• Users at all levels can create reports
• Information format makes sense to users
• Web-based intuitive solution
• Comprehensive mechanism to solve the ongoing
  issue of information retrieval

30
Questions and Answers

• Are there any questions?
• Thank you for your time.
• lucretia_at_mail.armstrong.edu