Discoverer Tips & Techniques … Useful Ones …

Copyright  2000 by Russ Proudman
September 16, 2023
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!

Copyright  2000 by Russ Proudman
September 16, 2023
Agenda
Outside Discoverer
actions you should consider outside of Discoverer to help keep your sanity
Inside Discoverer
actions your end users should incorporate within Discoverer to help keep their sanity
Russ’s Top 10
Creating a successful reporting environment
Actions you should consider to help keep your sanity … their sanity … and your job!
Q&A
*

Copyright  2000 by Russ Proudman
September 16, 2023
Skill Testing Question #1
The consulting company sponsoring me with this presentation - Lightship Enterprises LLC - has a motto. Is it?
“Oracle Shmoricle – Oracle consultants are a dime a dozen right now … wanna’ tries ours?”
Lightship Enterprises – “it takes a licking … and keeps on ticking …”
Lightship Enterprises – “guiding you to brighter talent”.
Lightship Enterprises – “guiding you to brighter talent”.

Copyright  2000 by Russ Proudman
September 16, 2023
Outside Discoverer
“actions you should consider outside of Discoverer to help keep your sanity”
section I

Copyright  2000 by Russ Proudman
September 16, 2023
Outside Discoverer (1 of 2)
NoetixViews
In the fairly large number of installations I have been involved with, the ones NOT using NoetixViews in their reporting environment have the most difficulty. Why?
Top 5 reasons you should use NoetixViews.
It’s relatively cheap!
(costs way more to create SQL code by employees or contractors)
It’s logical.
(all views are by business area – for business area users)
Views are … views.
(can by called by ANY query tool / report platform independent)
Complete dynamic help file.
You get all the SQL code!
*

Copyright  2000 by Russ Proudman
September 16, 2023
Outside Discoverer (2 of 2)
Windows Registry Fixes
There are a 3 settings that you can alter in the Windows registry for EACH client machine to speed up your Discoverer queries.
Depending on your environment, the speed increase can be dramatic!
Turn off query prediction.
(turn off the entire query prediction system if using pre-Apps 11i/ db 8i)
Stop query prediction forcing use of cost-based optimizer.
(use of the CBO (cost based optimizer) is not enforced
Stop validating that tables / views exist in database.
(if sure of your environment then don’t waste time checking objects are ok)
See full description at my website: www.proudman.homestead.com
*

Copyright  2000 by Russ Proudman
September 16, 2023
Skill Testing Question #2
NoetixViews is a software product that …
… is vaporware because it comes from Seattle and those guys just hang out at Starbucks all day …
… is a great tool that makes your reporting environment much easier to create and maintain …
… is a name that sounds kinda’ neat, but nobody has a clue what it means …
… is a great tool that makes your reporting environment much easier to create and maintain …

Copyright  2000 by Russ Proudman
September 16, 2023
“actions your end users should incorporate within Discoverer to help keep their sanity”
Inside Discoverer
section II

Copyright  2000 by Russ Proudman
September 16, 2023
Sort once … not for each column
Simple tip … huge difference
ALL rows in query (ie: millions!) have to be retrieved for every A->Z or Z->A click on a column (this can take minutes every time)
Instead use the Tools -> Sort window once and retrieve the records ONCE
Takes a while for end users to understand this tip … until they try it … so show ‘em
Power users … use the Tools -> Sort window in the Discoverer Wizard to sort at initial retrieval only
Inside Discoverer (1 of 5)

Copyright  2000 by Russ Proudman
September 16, 2023
Creating Conditions
Conditions are great!
Conditions are powerful!
Giving your end users the power to limit their queries any way they care to really empowers them.
However, there are 2 standards you should incorporate.
Inside Discoverer (2 of 5)

Copyright  2000 by Russ Proudman
September 16, 2023
Inside Discoverer (2 of 5)
Limit conditions to indexes 90% of the time.
There are a large number of indexes in the Oracle Apps system
which you can use.
The rule for end users is:
use an index to limit the condition – fast.
Don’t use an index – slow and worse over time.
But how do you identify indexes easily if you’re the end user?
If you bought NoetixViews, indexes are prefaced with A$.
If you didn’t buy NoetixViews, copy ‘em … preface indexes with A$ via Discoverer Admin and teach users the standard.
*

Copyright  2000 by Russ Proudman
September 16, 2023
Inside Discoverer (2 of 5)
Use parameters in conditions.
If conditions are great – then parameters are fantastic!
Without parameters, reports are hard-coded. With parameters, the same report can be used by many different departments.
The more parameters you create, the more people who can use it and/or the more detailed filtering that can happen.
Try and set conditions with LIKE so % can be used.
Major step forward – combine parameters and indexes.
Conditions with parameters that use indexes – the ultimate!
For each index in your query … make a condition that utilizes a parameter.
Now when your different users use the report for different uses … they can put % in the parameters they don’t care about and fill in the data for what they care about … and it’s always quick!
*

Copyright  2000 by Russ Proudman
September 16, 2023
Skill Testing Question #3
Using indexes in your conditions means …
… your reports will run so blazingly fast you will wonder if Santa Claus has come early to town …
… not much at all but you think columns starting with A$ sound kinda’ funky …
… your boss will be so impressed when you throw computer jargon like ‘index’ around in meetings …
… your reports will run so blazingly fast you will wonder if Santa Claus has come early to town …

Copyright  2000 by Russ Proudman
September 16, 2023
Inside Discoverer (3 of 5)
Using Percentages … ya’ right!
Most reports I create don’t need percentages … luckily!
Percentages in Discoverer 3.1x are buggy and difficult.
If you have 3 total columns and you want to add percentages, you may have to delete columns and start again.
Once you get them working they’re useful … but you may find that the time taken / effort required isn’t worth it!

Copyright  2000 by Russ Proudman
September 16, 2023
Inside Discoverer (4 of 5)
Using Calculations … great stuff!
Calculations are extremely powerful.
Most Oracle Apps systems are full of data problems (ie: data was loaded in not typed in, so many constraints not honored).
Calculations work great here! Not just for adding two columns together, but to perform high level math, filtering, data retrieval, etc.
Example calculation uses.
Stop divide by 0 errors.
Handle NULLS effectively.
Fix bad data in reports (DECODE function).
Use TRUNC on SYSDATE to ensure all values in a day are chosen.

Copyright  2000 by Russ Proudman
September 16, 2023
Inside Discoverer (5 of 5)
Exceptions … weak … weak … weak!
They’re a great idea.
Allow you to mark the ‘exceptions’ in your data
(ie: highlight problems or situations you want to note).
Unfortunately they’re weak and have bugs.
Can only use <, >, =, between. Cannot use <=, >=, NOT, NULL, LIKE, etc.
Cannot share a library of exceptions. Cannot use other objects in Discoverer like entered parameters, etc.
If want >=, just use > as bug still exists that codes SQL as >=. Unfortunately, > is therefore wrong.
Don’t use single quote around characters (like you do everywhere else) as it won’t return the data.
So great idea … just weak right now!

Copyright  2000 by Russ Proudman
September 16, 2023
Skill Testing Question #4
The name of the company that’s putting on this presentation is …
Jefferson Starship …
Starship Voyager …
Lightship Enterprises LLC
Lighthouse Cookies …
Lightship Enterprises LLC …

Copyright  2000 by Russ Proudman
September 16, 2023
Russ’s Top 10
Creating a Successful Reporting Environment
section III
Actions you should consider to help keep your sanity … their sanity … and your job!

Copyright  2000 by Russ Proudman
September 16, 2023
Note:
Many points in my Top 10 are handled well by NoetixViews.
Even if you don’t use NoetixViews … you can still learn from them.
“ Plagiarism is a most sincere form of flattery …
so flatter NoetixViews wildly! “
Russ’s Top 10

Copyright  2000 by Russ Proudman
September 16, 2023
Create an Open Systems Environment – don’t limit your options
don’t customize around your query tool (ie: creating SQL in custom folders in Discoverer Administrator)
Create views in the database so ALL query tools can use them
Handle environment changes (relatively) easily
If you use the views concept, then all changes (ie: table name changes, column name changes, etc.) only have to be changed in one place
All query tools that access the view will now be correct
Performance Optimization – stop unneccessary waiting
Tuning the SQL your queries, views, etc. use can make a surprising difference to increase the speed of data return
On day 1 of Oracle Apps setup it’s no big deal … but on day 200 after you have millions of rows of data … it can show!
Russ’s Top 10
*

Copyright  2000 by Russ Proudman
September 16, 2023
Organize Information by Functional Area
Using the views concept, you now have a number of views you’ve created for your organization.
You can manage these views by different methods (company, organization, functional area, user, etc.).
I’ve found that organizing by functional area is best as I can put groups of users together who all can look at U.S. AR, or U.K. AP, etc.
Index Column Identification – creates fast queries
How do you let your users know what’s an index and what isn’t?
Copy Noetix’s method and preface all index objects by A$.
Users can now be taught to ALWAYS use an A$ column in their condition – and not have to understand what an index is.
Simple concept for end users to learn, plus …
A huge difference on database access and query return time!
Russ’s Top 10
*

Copyright  2000 by Russ Proudman
September 16, 2023
Alphabetize Everything
In your view alphabetize all items in the sql statement.
Takes a bit of work, but you now have A$ columns at the top, all items in A->Z order and all folders in A->Z order.
Payoff? – users can find items MUCH faster which means less frustration for them … and you!
Create a Help File
Tough one to do as it’s a lot of work that management doesn’t see right off.
However, just a file containing a simple description telling what the view does and placed on a network server will make a huge difference.
You can point end users to it and needless questions about what view to use, what it does, etc. won’t keep occurring!
Russ’s Top 10
*

Copyright  2000 by Russ Proudman
September 16, 2023
Complexity completely hidden from end user (ie: maintain a top-notch EUL)
It seems to be a never ending job, but creating a great Disco EUL (end user layer) pays off many times!
Users can create queries quicker, you can remember what you did, security can be implemented, defaults can be set up …
For example, if an item has Detail / Sum set on it and the item is an employee number or some other identifier … then you can get rid of the SUM option showing, and the user won’t be confused.
Or predefined conditions … or predefined calculations …
All are worth the effort and the end user has no idea of the complexity
Run Discoverer end user classes at your office
As the Disco Admin, you’re the pro … you run ‘em
I’ve taught hundreds of classes and a simple day long class can be created and taught on a periodic basis
Smart end users … less dumb questions …
Russ’s Top 10
*

Copyright  2000 by Russ Proudman
September 16, 2023
Limit the number of end users using Discoverer
What’s this? … but Oracle wants to sell you lots of copies!
In my experience although every end user is told to go to training I see the following breakdown.
Russ’s Top 10
30 users take a class
That’s the 4 your want! … about 1 in 7 ½!
Buy them lunches, give ‘em a BMW, whatever … but make them happy and get them to create the never-ending reports.
And … save on your Oracle Discoverer licenses as well!
That’s the truth of it with my experience … pamper the 4 … and you’ll be happy in the end.
6 will be okay with it and create occasional queries (badly)
4 users will be great with it and the office pros
20 enjoyed the free lunch but won’t use Discoverer after the first week
*

Copyright  2000 by Russ Proudman
September 16, 2023
Create a corporate wide reporting system for < $1000!
Yup … it’s true … you can do it.
Here’s the basic info
Use a 3rd. Party scheduler ($5)
Use a corporate NT / 2000 server (okay, > $5!)
Have the scheduler run Discoverer each night and output into HTML format
Output from the scheduler to the correct subdirectories already setup for different user groups (ie: US GL, US AR, UK AP, etc.)
Maintain all users in user groups via NT/2000 security
Create a dashboard system (in HTML) via MS Word
And that’s it … only 1 copy if Discoverer need (< $950)
I now created this at 3 different companies … so it works!
For complete explanation go to my web page at:
www.proudman.homestead.com and see OAUG 2000 Hawaii presentation.
Russ’s Top 10 – BONUS!
*

Copyright  2000 by Russ Proudman
September 16, 2023
Skill Testing Question #5
You can create a complete corporate-wide reports distribution system for less than $1000!
Sure bub … you’re lying …
It’s the absolute truth …
Like many things in life … it depends …
2. It’s the absolute truth …
3. Like many things in life … it depends …