Oracle Discoverer 4.1 for Power Users

Course Description

Experienced Oracle Discoverer users are interested in knowing how the advanced features of the Oracle SQL language can be incorporated into Discoverer queries. Within this course users will learn how to enhance their queries through the use of correlated subqueries, functions, processing of hierarchies and other advanced SQL techniques. When these advanced techniques are combined with the ease-of-use features of Oracle Discoverer then users are fully empowered to extract crucial information from their databases and data warehouses.
1 Day
Contact us for pricing
 

Prerequisites

Participants would need to have a good working knowledge of Oracle 9i Discoverer or have attended an Oracle 9i Discoverer Administration course.

Advanced Queries in Discoverer (1)

Fan Traps, Fan trap detection
What do these messages mean?
Disabling the Fan Trap Detection Option
Why are the totals wrong?
How do we get out of the Trap?
Why does the query work now?
Other Fan Trap Considerations
Multiple Join Paths, Creating a Query with Multiple Paths

Advanced Queries in Discoverer (2)

Join Folders
Recursive Queries

Analytic Functions (1)

What are Analytic Functions?
The Analytic Function Template
Template Example
Types of Analytic Functions
Ranking, Banding
Equi-Width Bands, Equi-Height Bands
Bands Using GREATEST, CASE, and NTILE
Windowing

Analytic Functions (2)

Moving Average, Cumulative Total
Lagging/Leading
Statistical Functions
Slope, Intercept
Analytic Functions and Conditions
Applying Criteria in Seperate Conditions
Applying Criteria in One Condition

Hierarchial Queries (Connect by Prior) (1)

Why we need Hierarchial Relationships
Problems seeing the Hierarchy
One to Many (:N) Hierarchy
:N Hierarchial Query
Many to many (M:N) Hierarchy
BOM Illustrated
M:N Hierarchy (Bill-of-Materials Table Definition)
Populating the Part Table

Hierarchial Queries (Connect by Prior) (2)

Populating the BOM Table
Bill-of-Materials Explosion
Bill of Materials Implosion

Advanced Hierarchial Queries (1)

Expanding Displays for Hierarchial Queries
Setting Up the :M Hierarchail Query for Multi-level Display
Building Hierarchail Query Custom Folders
Joining the Custom Folders
Hide the Linked Folders
Create the Folder the Users will Access
Creating the Query in the User Edition
Setting Up the BOM Hierarchial Query for Multi-Level Display

Advanced Hierarchial Queries (2)

Building BOM Hierarchial Query Custom Folders
Joining the BOM Custom Folders
Creating the Parts Explosion Folder that the Users will Access
Creating the BOM Query in the User Edition
Customizing the BOM Query

Writing and Registering a PL/SQL Function (1)

The Need for PL?SQL Functions
Writing the Function
Create the function specification
Declare Variables, Program Logic
The Exception Section, The End Statement
Building the Function
Preliminary Test
Registering the Function

Writing and Registering a PL/SQL Function (2)

Registered Function
Argument Description
Using the Function

Alternative Date Sorts

Changing a Date Format
Effect on the Date LOV
Defining and Alternate Date Sort

Discoverer Debug Trace

Reasons for Creating a Debug Trace
Setting up The Debug Trace, The Debug Trace Log File
The Debug Tracve Diagnostic Levels
Generating the Debug Trace File
Using Metalink
What Causes This Error?
How might we prevent this problem (Or live with it)?
Discoverer