How Your DBA or Database Programmer Can Improve Your Argos Reporting!

1
How Your DBA or Database Programmer Can Improve
Your Argos Reporting!

• Presented By
• Bruce Knox
• University of Arkansas
• Division of Agriculture
• Cooperative Extension Service
• BAS S274 October 14th 2008

2
Argos provides THE missing Banner Reporting Tool
3
Even with Argos, there remains a need for further
simplification
4
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets

5
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets
• Flattened Banner Data

6
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets
• Flattened Banner Data
• Using Desktop Data with Banner

7
Views, Views, and Banner Views
8
Views, Views, and Banner Views

• To Deal with Effective Dated Records

9
Views, Views, and Banner Views

• To Deal with Effective Dated Records
• To Flatten The Database

10
Views, Views, and Banner Views

• To Deal with Effective Dated Records
• To Flatten The Database
• Using Existing Banner Provided Views

11
Effective Dated Records
12
Effective Dated Records

• The 1 Bane of Ad Hoc Banner Reporting

13
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current Banner
  Records

14
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current
  Records
• Oracle Views can Deliver them without the need
  for Additional Selection Criteria

15
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current
  Records
• Views can Deliver them without the need for
  Additional Selection Criteria
• Just JOIN the Views and the Query is Done!

16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
What Was Hidden Here?
20
What Was Hidden Here?

• FROM FTVORGN
• WHERE
• TRUNC(FTVORGN_EFF_DATE) lt SYSDATE
• AND (FTVORGN_NCHG_DATE gt SYSDATE OR
  FTVORGN_NCHG_DATE IS NULL)
• AND (FTVORGN_TERM_DATE gt SYSDATE OR
  FTVORGN_TERM_DATE IS NULL)
• AND FTVORGN_STATUS_IND 'A'

21
Thousands of Related Tables
22
Thousands of Related Tables

• The 2 Bane of Ad Hoc Banner Reporting

23
Thousands of Related Tables

• The 2 Bane of Ad Hoc Banner Reporting
• Oracle Views Can Flatten the Database

24
Views
25
(No Transcript)
26
Oracle Views
27
Oracle Views
28
(No Transcript)
29
(No Transcript)
30
Oracle Views
31
From the Users Point of View
32
(No Transcript)
33
(No Transcript)
34
(No Transcript)
35
Views
36
Banner Views
37
Banner Views

• Banner Actually Includes Useful Views

38
Banner Views

• PEVEMPL Employee Information View

39
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View

40
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View
• Flattens 16 Tables

41
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View
• Flattens 16 Tables
• Obscures the Source Column Names

42
(No Transcript)
43
(No Transcript)
44
Banner Views
45
Banner Views
46
Banner Views
47
Importing Desktop Data
48
Importing Desktop Data

• Oracle Operates Best with Data in Oracle

49
Importing Desktop Data

• Oracle Operates Best with Data in Oracle
• Upload Data Interactively with APEX

50
Importing Desktop Data

• Oracle Operates Best with Data in Oracle
• Upload Data Interactively with APEX
• Loading an Excel file
• Upload Using Batch Scripts

51
Oracle Application Express
52
Oracle Application Express
53
Oracle Application Express
54
Oracle Application Express
55
Oracle Application Express
56
Oracle Application Express
57
Oracle Application Express
58
Oracle Application Express
59
Oracle Application Express
60
Oracle Application Express
61
Oracle Application Express
62
Oracle Application Express
63
Oracle Application Express
64
Oracle Application Express
65
Oracle Application Express
66
Loading Excel Workbooks

• All you need is the Microsoft Excel Driver
  (.xls) ODBC Driver
• And you can load the structured data using
  instructions available from http//helpdesk.evisio
  ns.com
• But, you will not have access to another data
  source.

67
Batch Scripts
68
Batch Scripts
69
SQLPlus Batch Script

• -- publication_approval.sql Test Script for
  SQLPlus External Table Loads
• -- SET ECHO OFF
• -- 01/29/08 bknox Created
• --this script named publication_approval.sql is
  to be run by a matching publication_approval.bat
  for the Application.
• --If your file to load is publication_approval.c
  sv
• --then filename1 gt 'D\orcl_ext\Application\publi
  cation_approval.log'
• SET FEEDBACK OFF
• SET TIMING OFF
• COLUMN Variable HEADING "Variable" FORMAT A16
• COLUMN Value HEADING "Value" FORMAT A30
• HOST DEL D\orcl_ext\Scripts\publication_approval_
  run.log
• -- ONCE THE TESTING IS DONE HOST DEL
  D\orcl_ext\Publications\publication_approval.log

70
SQLPlus Batch Script

• SET FEEDBACK ON
• TRUNCATE TABLE pub_approval
• -- TRUNCATE TABLE is the preferred way to empty
  the Table, but only works when run by the Table
  Owner.
• --DELETE FROM pub_approval
• INSERT INTO pub_approval
• ( FUND,
• ORGN,
• PIDM )
• SELECT
• FUND,
• ORGN,
• PIDM
• FROM pub_approval_ext
• SET FEEDBACK OFF

71
Windows Script

• REM Windows Batch Script for running
  publication_approval.sql
• REM which loads Internal
  Table from External Table.
• D
• cd oracle_ext
• cd Scripts
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.log
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.bad
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.dsc
• sqlplus pubsid/pwd_at_APPS _at_D\oracle_ext\Scripts\pub
  lication_approval.sql
• EXIT

72
Bonus Report Template
73
Bonus Report Template
74
Conclusions

• Use Oracle Views to Simplify Banner
• Use Banner Views Whenever Available
• Use Batch Scripts to Upload Spreadsheets

75
Questions?

• Bruce Knox bknox _at_t uaex.edu
• http//www.uaex.edu/bknox
• http//www.uaex.edu/bknox/BannerArgos.htm
• University of Arkansas Division of
  Agriculture Cooperative Extension Service

Thank You! And, thanks to the Tennessee Board
of Regents (TBR) and the Middle Tennessee State
University
76
Contact Information

• Bruce Knox bknox _at_t uaex.edu
• http//www.uaex.edu/bknox
• http//www.uaex.edu/bknox/BannerArgos.htmhttp//w
  ww.uaex.edu/bknox/BannerScripts.htm password is
  scripts
• University of Arkansas Division of
  Agriculture Cooperative Extension Service

77
Argos Resources

• Training classes
  http//www.evisions.com/calendar
• Documentation and videos http//www.evisions.com
  /support/argos
• Argos Listserve
  http//www.evisions.com/community
• Argos Coop
  http//datablocks.evisions.com
• Evisions HelpDesk
  http//helpdesk.evisions.com