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
1609 Babcock Street, Newport Beach, CA, USA
949.574-7908
www.proudman.homestead.com
[email protected]
OAUG Asia Pacific 2001 – 1
Copyright  2001 by Russ Proudman
July 7, 2015
Drawings, prizes, fame, etc!
During the presentation there will be a few drawings.
Will draw a business card from the box and ask you a skill testing
question.
Get it right … and the prize is yours …
So make sure you’ve put your business card in the box!
OAUG Asia Pacific 2001 – 2
Copyright  2001 by Russ Proudman
July 7, 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
OAUG Asia Pacific 2001 – 3
Copyright  2001 by Russ Proudman
July 7, 2015
Skill Testing Question #1
The consulting company sponsoring me with this presentation Lightship Enterprises LLC - has a motto. Is it?
1.
“Oracle Shmoricle – Oracle consultants are a dime a
dozen right now … wanna’ tries ours?”
2.
Lightship Enterprises – “it takes a licking … and keeps on
ticking …”
3.
Lightship Enterprises – “guiding you to brighter talent”.
*
OAUG Asia Pacific 2001 – 4
Copyright  2001 by Russ Proudman
July 7, 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’
OAUG Asia Pacific 2001 – 5
Copyright  2001 by Russ Proudman
July 7, 2015
Oracle Applications – Cost
Distribution Analysis
Installing Oracle Apps
More money spent
than budgeted
Budgets frozen for now
Custom reports?
… ‘ya right!
OAUG Asia Pacific 2001 – 6
Copyright  2001 by Russ Proudman
July 7, 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!”
OAUG Asia Pacific 2001 – 7
Copyright  2001 by Russ Proudman
July 7, 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
•
•
•
•
•
OAUG Asia Pacific 2001 – 8
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 7, 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.
OAUG Asia Pacific 2001 – 9
Copyright  2001 by Russ Proudman
July 7, 2015
NoetixViews Meta-Data Layer
Oracle Tables
PO_LINE_TYPES
PO_HEADERS
PO_LINES
MTL_ITEM_CATEGORIES
MTL_ITEMS
NoetixViews
PO_Lines
OAUG Asia Pacific 2001 – 10
Copyright  2001 by Russ Proudman
July 7, 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
OAUG Asia Pacific 2001 – 11
Copyright  2001 by Russ Proudman
July 7, 2015
Skill Testing Question #2
NoetixViews is a software product that …
1.
… is vaporware because it comes from Seattle and those
guys just hang out at Starbucks all day …
2.
… is a great tool that makes your reporting environment
much easier to create and maintain …
3.
… is a name that sounds kinda’ neat, but nobody has a
clue what it means …
*
OAUG Asia Pacific 2001 – 12
Copyright  2001 by Russ Proudman
July 7, 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!
OAUG Asia Pacific 2001 – 13
Copyright  2001 by Russ Proudman
July 7, 2015
The Corporate-wide Report
Distribution System
the
engine
presentation
layer
OAUG Asia Pacific 2001 – 14
Made up of 5 main parts.
1.
2.
3.
4.
5.
The directory structure
The automatic scheduler
Security
HTML Dashboards
Shortcuts – a one button feature
Copyright  2001 by Russ Proudman
July 7, 2015
Engine - 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.
OAUG Asia Pacific 2001 – 15
Copyright  2001 by Russ Proudman
July 7, 2015
Disco source file
Engine - 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.)
OAUG Asia Pacific 2001 – 16
Copyright  2001 by Russ Proudman
July 7, 2015
Engine - 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:
•
OAUG Asia Pacific 2001 – 17
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 7, 2015
Engine - Security
•
•
•
Only ‘REPORTING AR’
users can see all these
reports’ .html output
OAUG Asia Pacific 2001 – 18
•
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 7, 2015
Presentation – HTML Dashboards
•
•
•
•
•
•
•
OAUG Asia Pacific 2001 – 19
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 7, 2015
Presentation – HTML Dashboards
OAUG Asia Pacific 2001 – 20
Copyright  2001 by Russ Proudman
July 7, 2015
Presentation - 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!”
OAUG Asia Pacific 2001 – 21
Copyright  2001 by Russ Proudman
July 7, 2015
Skill Testing Question #3
The name of the company that’s putting on this presentation is …
1.
Jefferson Starship …
2.
Lighthouse Cookies …
3.
Starship Voyager …
4.
Lightship Enterprises LLC …
*
OAUG Asia Pacific 2001 – 22
Copyright  2001 by Russ Proudman
July 7, 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).
OAUG Asia Pacific 2001 – 23
Copyright  2001 by Russ Proudman
July 7, 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.
OAUG Asia Pacific 2001 – 24
Copyright  2001 by Russ Proudman
July 7, 2015
Skill Testing Question #4
You can create a complete corporate-wide reports distribution
system for less than $1000!
1.
Sure bub … you’re lying …
2.
It’s the absolute truth …
3.
Like many things in life … it depends …
*
OAUG Asia Pacific 2001 – 25
Copyright  2001 by Russ Proudman
July 7, 2015
Questions & Answers
Information about this presentation?
Contact:
Lightship Enterprises LLC.
1609 Babcock Street,
Newport Beach, CA, USA
949.574-7908
or talk to me.
Information about NoetixViews?
Contact:
Talk to me.
OAUG Asia Pacific 2001 – 26
Copyright  2001 by Russ Proudman
July 7, 2015