Transcript Apps Are In Data's In Now How Do I Get The

A Corporate-Wide Reports
Distribution System < $1000
You Bet!
Russ Proudman
Lightship Enterprises LLC
www.proudman.homestead.com
[email protected]
ODTUG San Diego 2001 – 1
Copyright  2001 by Russ Proudman
July 21, 2015
Agenda
1.
2.
3.
4.
5.
6.
Corporate-wide reporting system for < $1000!
Oracle Applications - Cost Distribution Analysis
The End-User Reporting Tool
The Meta-Data Layer
The Corporate-wide Distribution System
Under $1000 … and it works … really?!
7. Q & A
ODTUG San Diego 2001 – 2
Copyright  2001 by Russ Proudman
July 21, 2015
“You can create a Corporate-wide
Report Distribution System for
under $1000!” …
famous last words by Russ Proudman
(… when attempting to get a contract)
Includes:
• end-user query tool
• Report scheduler to ‘push’ output
• Complete directory structure for ‘pull technology’
ODTUG San Diego 2001 – 3
Copyright  2001 by Russ Proudman
July 21, 2015
Oracle Applications – Cost
Distribution Analysis
Installing Oracle Apps
More money spent
than budgeted
Budgets frozen for now
Custom reports?
… ‘ya right!
ODTUG San Diego 2001 – 4
Copyright  2001 by Russ Proudman
July 21, 2015
Oracle Applications – Cost
Distribution Analysis
• Oracle Applications are installed
Your company has paid for:
…
…
…
…
software
hardware
updates
consulting & employee costs
The last thing your boss wants to hear is:
“We need a corporate-wide report
distribution system!”
ODTUG San Diego 2001 – 5
Copyright  2001 by Russ Proudman
July 21, 2015
The End-User Reporting Tool
Many end-user tools are available for reporting.
•
•
•
•
Oracle Discoverer, Cognos Impromptu
Brio Query, Business Objects
Crystal Reports, InfoMaker
ODBC tools (ie: Access, Excel, etc.)
All these tools can see the Oracle Apps tables
•
•
•
•
•
ODTUG San Diego 2001 – 6
but 200 – 300 tables per module!
best tools have a middle layer (ie: EUL, catalog, universe)
must populate this end-user query tool middle layer
what happens to other tools you use? (TOAD, SQL, etc.)
what happens w/ security changes, Oracle upgrades, etc?
Copyright  2001 by Russ Proudman
July 21, 2015
The Meta-Data Layer
Average Oracle Apps Module
# modules at your site
= 200 – 300 tables
X 10
-------------------------Total Oracle Apps tables at site = 3000 tables!
In the majority of cases …
You need a meta-data layer between your
query tool and the Oracle Apps database.
ODTUG San Diego 2001 – 7
Copyright  2001 by Russ Proudman
July 21, 2015
NoetixViews Meta-Data Layer
Oracle Tables
PO_LINE_TYPES
PO_HEADERS
PO_LINES
MTL_ITEM_CATEGORIES
MTL_ITEMS
NoetixViews
PO_Lines
ODTUG San Diego 2001 – 8
Copyright  2001 by Russ Proudman
July 21, 2015
Oracle BIS
(Business Intelligent System)
Like a portal front-end on a reporting solution.
• Uses Key Performance Measures (Indicators)
• “KPIs are calculated from the production system using predefined formulas in Oracle Alert”.
Exists for many Oracle module areas.
• Customer Intelligence, Call Center Intelligence, etc.
Many pre-configured reports, alerts preset for you.
Questions:
• Are reports, views, etc. dynamic?
• Is there a help file for creating new reports?
• Can end-users create reports from scratch?
ODTUG San Diego 2001 – 9
Copyright  2001 by Russ Proudman
July 21, 2015
Meta-Data Layer Considerations
• Need faster, easier access to business information
using your end-user query tool.
• Need dynamic business views built automatically.
• Need Online help for your unique configuration
• Metadata layer must work with ANY end-user
query tool!
• You must be shielded from Apps & Db. upgrades
ODTUG San Diego 2001 – 10
Copyright  2001 by Russ Proudman
July 21, 2015
So now what?
So we’ve got the query tool …
• Let’s use Oracle Discoverer.
(holds the majority of Oracle Apps-only sites)
And we’ve got the meta-data layer …
• Let’s use NoetixViews
(is used at a considerable number of Oracle
Applications clients I’ve worked with …)
So let’s created the corporate-wide report
distribution system!
ODTUG San Diego 2001 – 11
Copyright  2001 by Russ Proudman
July 21, 2015
The Corporate-wide Report
Distribution System
Made up of 5 main parts.
1.
2.
3.
4.
5.
ODTUG San Diego 2001 – 12
The directory structure
The automatic scheduler
HTML Dashboards to the rescue
1 Shortcut – a one button feature
Security
Copyright  2001 by Russ Proudman
July 21, 2015
The Directory Structure
•
•
•
•
•
•
•
Everything is contained within the directory structure.
All source Discoverer (.dis) files are there.
All Disco output from command-line mode (.htm) is there.
All help files (.doc) are there.
All dashboards are there.
All dashboard shortcuts are there.
All scheduler batch files are there.
• Again, everything is completely contained
within the directory structure.
ODTUG San Diego 2001 – 13
Copyright  2001 by Russ Proudman
July 21, 2015
Disco source file
The Directory Structure
Disco output file (.htm)
Developer area
source, help, docs, etc.
Dashboard shortcuts
All reports input / output
by module + special
(VIP) area
Dashboards (.doc & .htm files)
All JIT Scheduler info
found here (batch files,
preferences, etc.)
ODTUG San Diego 2001 – 14
Copyright  2001 by Russ Proudman
July 21, 2015
The Automatic Scheduler
•
•
•
•
•
JIT Scheduler set up to run DOS batch command files.
Runs batch files (daily, weekly, monthly) early in morning.
Batch files contain the required statements to run Discoverer in
command line mode with automatic sign-on, parameters, etc.
JIT, Pre-version 5 – free. Version 5+ … about $10 shareware!
Example command line mode command:
•
ODTUG San Diego 2001 – 15
C:\orant\discvr31\dis31usr.exe /connect “account/password@instance” /open
“y:/production reporting system/reports/ar/ar customer stats by period bo/ar customer
stats by period bo” /batch /export “HTML” “y:/production reporting system/reports/ar/ar
customer stats by period bo/ar customer stats by period bo” /parameter “customer” “%%”
Copyright  2001 by Russ Proudman
July 21, 2015
Dashboards To The Rescue
•
•
•
•
•
•
•
ODTUG San Diego 2001 – 16
Dashboards are created via Microsoft Word and saved as a web
page (HTML format).
A dashboard is created for each Oracle Apps module within a
geographic region (ie: US AR, UK PA, etc.).
A dashboard is created for special sensitive batch jobs (VIP).
A dashboard is created for online Disco jobs for a few users.
Dashboards can – and are – updated continually with new
reports … easily.
Security (covered later) allows users only to run jobs associated
with particular dashboard (ie: AR people run AR Dashboard).
Dashboards are created like a tree structure (ie: 1 master
dashboard can call multiple dashboards below it, etc.).
Copyright  2001 by Russ Proudman
July 21, 2015
Dashboards To The Rescue
ODTUG San Diego 2001 – 17
Copyright  2001 by Russ Proudman
July 21, 2015
Shortcuts
As users climb the
corporate ladder …
The ability to push more than
one button … decreases!
Solution  1 Shortcut for everybody
•
•
•
Every end user using the Production Reporting System is
emailed a shortcut that is stored on their Windows desktop.
Shortcut points to the Master Dashboard HTML page on a
reporting server containing the self-contained directory system.
Security handles what dashboards a user can see any lower than
the Master Dashboard.
Solves my initial goal “a user just has to push a button!”
ODTUG San Diego 2001 – 18
Copyright  2001 by Russ Proudman
July 21, 2015
Security
•
•
•
Only ‘REPORTING AR’
users can see all these
reports’ .html output
ODTUG San Diego 2001 – 19
•
Directory structure allows complete security.
Create a user group for each Oracle module (or set of .html
output a user will be looking at) – ie: AR, AP, GL, etc.
Each subdirectory within the directory structure allows only
particular ‘user groups’ to read everything in it.
John Smith has been added to the ‘user group’ – REPORTING
AR. Only people with this ‘user group’ (ie: John Smith) can
choose a hotlink from the AR Dashboard and have it work.
Copyright  2001 by Russ Proudman
July 21, 2015
Improvements to the Corporate-wide
Report Distribution System
• Purchase a reports distribution server so system
can be controlled from a local environment.
(get a cheap DELL or equivalent).
• Create a set of programs to administer copying
to correct area, reporting on all reports that
exist, creating the DOS command line batch
files, etc.
(I created a simple Access DB for this).
ODTUG San Diego 2001 – 20
Copyright  2001 by Russ Proudman
July 21, 2015
Under $1000 … and it works … really?
Under $1000? YES!
1 copy of Discoverer
approx. $975.00
1 copy of JIT Scheduler approx. $10.00
-----------------------TOTAL COST
APPROX. $985.00
… and NO!
•
•
•
•
May
May
May
May
require some users to run on-line jobs – more licenses.
want to purchase NoetixViews.
want to purchase an NT Reporting Server.
want to calculate costs of setting it up (salaries, etc.)
Does it work … really? YES!
I have set this system up at 3 different clients and hundreds of
users can access the .html reports run by the scheduler. All they do
Is click on a shortcut to the appropriate desktop(s).
All securely … and from around the world.
ODTUG San Diego 2001 – 21
Copyright  2001 by Russ Proudman
July 21, 2015
Questions & Answers
ODTUG San Diego 2001 – 22
Copyright  2001 by Russ Proudman
July 21, 2015