Contents

Title and Copyright Information

Send Us Your Comments

Preface

Oracle Discoverer Navigation and Accessibility

Keyboard Navigation

Documentation Accessibility

Intended Audience

Structure

Related Documents

Conventions

Part I Conceptual and task information

1 Introducing Oracle Discoverer Administrator

Introducing Oracle Discoverer Administrator

What is Oracle Discoverer?

What is Oracle Discoverer Administrator?

What is your role as a Discoverer manager?

Basic concepts for Discoverer managers

What is business intelligence?

What are relational databases, OLTP systems, and data warehouses?

What are the fundamental concepts behind a Discoverer system?

Introducing the End User Layer

Introducing business areas

Introducing folders and items

Introducing workbooks and worksheets

Introducing hierarchies and drills

Introducing summary folders

How does Discoverer work?

What are the steps to a successful Discoverer implementation?

What is involved in maintaining a Discoverer system?

What are the new features in Discoverer Administrator Version 9.0.4?

2 Getting started with Discoverer Administrator

Getting started with Discoverer Administrator

What are the prerequisites for using Oracle Discoverer?

What are the system prerequisites?

What are the data access prerequisites?

How to start Discoverer Administrator

What is the Workarea?

About the tabs in the Workarea window

About the context sensitive menus in the Workarea window

About the Administration Tasklist

About Discoverer Administrator documentation and online help

3 Creating and maintaining End User Layers

Creating and maintaining End User Layers

What is an End User Layer?

Why is the End User Layer useful?

What are End User Layer owners?

What privileges do you need to create an End User Layer in an Oracle database?

What privileges do you need to create an End User Layer in a non-Oracle database?

What privileges do you need to maintain an End User Layer?

What is the EUL Gateway?

How to create an End User Layer for an existing database user

How to create an End User Layer in a new database user

How to delete an End User Layer

How to view or change the default End User Layer

About copying EULs and EUL objects by exporting and importing

Which export/import method to use

About using the Discoverer Export Wizard and Import Wizard

About using the Discoverer command line interface to export/import EUL objects

About using the standard database export/import commands to export/import EUL owners

How to export the entire EUL using the Export Wizard

How to export selected business areas using the Export Wizard

How to export selected EUL objects using the Export Wizard

How to export End User Layer objects (to import to another database) using the
Discoverer command line interface

How to import End User Layer objects to a database (after export from another
database) using the Discoverer command line interface

How to export an EUL using the standard database export utility

How to import an EUL using the standard database import utility

How to import EUL objects from a file using the Import Wizard

Notes about EUL creation and maintenance

What are identifiers?

What are the space requirements and storage parameters for a typical EUL?

What are the EUL data migration scenarios with analytic functions?

What is a suggested workflow for initial EUL development and
ongoing maintenance?

4 Creating and maintaining business areas

Creating and maintaining business areas

What are business areas?

How to prepare to build a new business area

What is the Load Wizard?

How to create a business area using the Load Wizard

Loading from the online dictionary

Loading from a Gateway

Selecting tables and views

Specifying what Discoverer Administrator generates for the business area

Naming the business area, specifying object name and sorting options

How to open an existing business area when connecting to Discoverer Administrator

How to open an existing business area when already connected to
Discoverer Administrator

How to copy business areas between EULs

How to edit business area properties

How to delete a business area

How to synchronize a business area with the database

Notes about business area creation and maintenance

Why might a business area (or folders within it) not be visible to a user?

5 Creating and maintaining folders

Creating and maintaining folders

What are folders?

What are simple folders?

What are complex folders?

What are the benefits of using complex folders instead of database views?

What are custom folders?

What are the differences between custom folders and simple folders?

Examples of custom folders

About dependencies between folders

About sharing folders across business areas

How to create simple folders from the database

How to create complex folders

How to create custom folders

How to edit folder properties

How to edit a custom folder's SQL statement

How to delete folders from a business area

How to assign folders to a business area

How to assign a folder to multiple business areas

How to view orphaned folders in the EUL

How to remove an orphaned folder from the EUL

How to sort folders in a business area

How to sort items in a folder

How to re-order folders in a business area

How to validate folders in a business area

6 Controlling access to information

Controlling access to information

About Discoverer and security

About Discoverer access permissions

About Discoverer task privileges

How to specify a user or role (responsibility) that can access a business area

How to specify the business areas a user or role (responsibility) can access

How to specify the tasks a user or role (responsibility) can perform

How to specify a user or role (responsibility) to perform a specific task

How to set query retrieval limits

How to set scheduled workbook limits

How to share Discoverer workbooks using a database role

7 Scheduling workbooks

Scheduling workbooks

What are workbooks and scheduled workbooks?

Why schedule workbooks?

What happens when an end user schedules a workbook?

What Oracle database features support workbook scheduling?

Where to store the results of scheduled workbooks?

What are the prerequisites for scheduling workbooks?

How to confirm that the DBMS_JOB package is installed

How to specify the owner of the tables containing scheduled workbooks results

How to control the frequency that the server checks for new scheduled jobs

How to view and manage scheduled workbooks

About scheduled workbooks and compatibility between the current version of
Discoverer and previous releases

8 Maintaining items and item classes

Maintaining items and item classes

What are items?

What are item classes?

What are lists of values?

About setting up lists of values

What are alternative sorts?

Examples of different ways to implement alternative sorts

Example 1: Using a custom folder to implement alternative sorts

Example 2: Using a database table to implement alternative sorts

Example 3: Using a calculated item and a DECODE statement to implement
alternative sorts

What is drill to detail?

About setting up drill to detail

What are date items?

What are date format masks?

About truncating date items and the EUL_DATE_TRUNC function

About applying conditions to truncated date items

About drilling out to other applications

How to edit item properties

How to create a LOV item class

How to create an alternative sort item class

How to create a drill to detail item class

How to create a list of values using a custom folder

How to edit an item class

How to add items to an item class

How to add items to an item class using drag and drop

How to add items to an item class using the Edit Item Class dialog

How to add items to an item class using the Item Properties dialog

How to remove items from an item class

How to remove items from an item class using a popup menu

How to remove items from an item class using the Edit Item Class dialog

How to remove items from an item class using the Item Properties dialog

How to view the items that use an item class

How to view the list of values for an item

How to view the list of values for an item class

How to delete items and item classes

Notes about maintaining items and item classes

Notes about items based on columns with user-defined datatypes

9 Creating and maintaining joins

Creating and maintaining joins

What are joins?

What are single item joins?

What are multi-item joins?

About joining more than two folders

What are non-equi-joins?

What are one-to-many joins, one-to-one joins, and many-to-many joins?

About joining two folders using more than one join

What effect do joins have on query results and query performance?

Examples of how joins can affect query results from complex folders

What are outer joins?

How to create joins

How to view or edit joins

How to delete a join

What are fan traps, and how does Discoverer handle them?

Example of a fan trap

What to do if you set up a fan trap inside a complex folder

What to do if you have a data type mismatch in a join

Example of how to create a join between two items that have mismatched data types

Step 1: Create a new item that converts the Day of Week item from a varchar
to a date data type

Step 2: Create a join using the new item

10 Creating and maintaining calculated items

Creating and maintaining calculated items

What are calculated items?

What are derived items?

What are aggregate calculated items?

What restrictions apply to aggregate calculated items?

What are the differences in behaviour between analytic functions and
aggregate calculated items?

What are aggregate derived items?

Example of an aggregate derived item

How to create calculated items

How to edit calculated item properties

How to edit calculated items

How to delete calculated items

How to create a calculated item that enables end users to drill out from one
worksheet to display another worksheet in Discoverer Viewer

Why do you need PL/SQL functions?

What are custom PL/SQL functions?

About registering custom PL/SQL functions

How to register custom PL/SQL functions automatically

How to register custom PL/SQL functions manually

11 Creating and maintaining conditions

Creating and maintaining conditions

What are conditions?

About condition categories

How do conditions work in Discoverer?

What are the different types of condition?

How to create simple conditions

How to create advanced conditions

How to edit conditions

How to edit condition properties

How to delete conditions

Example conditions

Example 1: Using a condition to find data for the year 2002

Example 2: Using a condition to find sales in the last seven days

Example 3: Using a condition to find shipments in Quarter 3

About outer joins and the DisableAutoOuterJoinsOnFilters registry setting

Example 1: No condition applied

Example 2: Condition applied to query and DisableAutoOuterJoinsOnFilters set to 1 (switched off)

Example 3: Condition applied to query and DisableAutoOuterJoinsOnFilters set to 0 (switched on)

How to create row level security using a mandatory condition

How to enable summary folders if a source folder includes a mandatory
condition (e.g. with row level security)

12 Creating and maintaining hierarchies

Creating and maintaining hierarchies

What are hierarchies?

What are item hierarchies?

What are date hierarchies?

What are date hierarchy templates?

About date hierarchies and performance

Example of how a date hierarchy can affect performance in Discoverer Plus

Example of how performance can be retained

How to create item hierarchies

How to create date hierarchies and templates

How to edit item hierarchies

How to edit date hierarchy templates

How to apply a date hierarchy template to a date item

How to set the default date hierarchy template

How to delete item hierarchies or date hierarchy templates

How to modify default date hierarchies that use date format 'RR' following
upgrade to Oracle Discoverer Administrator

About creating a hierarchy using the CONNECT BY clause in a custom folder

How to create a hierarchy with a custom folder and the CONNECT BY clause,
using the emp table from the scott schema

13 Managing summary folders

Managing summary folders

About folders and summary folders in Discoverer

What are summary folders?

What are Discoverer summary tables?

What are materialized views?

What is Automated Summary Management (ASM)

How does ASM work?

What are the prerequisites for creating summary folders with ASM?

What are the different ways to run ASM and when do you use them?

What is the ASM policy?

Why must you refresh summary data?

What happens when a summary folder is refreshed?

How to run ASM using the Summary Wizard

How to run ASM after bulk load using the Load Wizard

How to run ASM using the command line interface

How to run ASM using a batch file and the operating system scheduler

How to configure the database for summary folders

How to confirm that DBMS_JOB is installed for summary management

How to use SQL*Plus to grant the privileges required to create summary folders

How to determine and reset tablespace quotas

How to check object/schema name

How to set the start time for summary processing

Limiting the number of processing requests that can run simultaneously

14 Creating summary folders manually

Creating summary folders manually

What is manual summary folder creation?

What are the prerequisites for creating summary folders manually in Discoverer?

What are summary combinations?

What to consider when defining summary combinations?

Guidelines for setting up summary combinations

When will an expression use a summary folder?

How to create summary folders based on items in the EUL

How to create summary folders based on users' queries

How to create summary folders based on external summary tables

How to manually refresh a summary folder

How to edit the properties of summary folders

How to edit summary folders

How to edit database storage properties of summary combinations for a
summary folder

How to delete summary folders

How to view the status of the summary tables/materialized views for a
summary folder

15 Additional information about summary folders

Additional information about summary folders

What is the context and future for Discoverer's use of summary tables/materialized
views?

What is query rewrite?

What are the conditions for query rewrite by Discoverer?

About viewing the SQL and execution plan for query rewrite in Discoverer

About configuring how Discoverer displays SQL in the SQL Inspector dialog

Examples of execution plan (in Discoverer) using/not using a materialized view

Example of the SQL (in Discoverer) where Discoverer rewrites the query

Example illustrating the advantages of rewriting a query to use a summary table

What characteristics differ between summary folders in Oracle Standard Edition
databases and Oracle 8.1.7 (or later) Enterprise Edition databases?

What is different about summary folders that are based on external summary tables?

What is different between mapping external summary tables and views to EUL
items, with Oracle 8.1.7 (or later) Enterprise Edition databases?

About refresh options for Oracle 8.1.7 (or later) Enterprise Edition databases

About refreshing summary folders following import between Oracle databases

16 Using Discoverer with Oracle Applications

Using Discoverer with Oracle Applications

What are Oracle Applications?

What are Oracle Applications responsibilities?

What features does Discoverer support for Oracle Applications users?

What are the prerequisites for using Discoverer with Oracle Applications?

What is different about running Discoverer in Oracle Applications mode?

Differences with the Discoverer Connect dialog in Oracle Applications mode

Differences with Discoverer security in Oracle Applications mode

Differences with Discoverer summary folders in Oracle Applications mode

Differences with Discoverer and secure views/language settings in
Oracle Applications mode

How to configure the Connect dialog for Oracle Applications users

How to create an Oracle Applications EUL

How to grant task privileges to all Oracle Applications users via the Public user

How to specify the Oracle Applications users/responsibilities who can access a
business area

How to specify the business areas that an Oracle Applications user/responsibility
can access

How to specify the tasks an Oracle Applications user or responsibility can perform

How to specify the Oracle Applications users/responsibilities who can perform a
specific task

How to display your Oracle Applications database user name and responsibility in a workbook

17 Using Discoverer with components of Oracle Application Server

Using Discoverer with components of Oracle Application Server

Using Discoverer with Oracle Enterprise Manager

Using Discoverer with OracleAS Reports

Using Discoverer with OracleAS Web Cache

Using Discoverer with Oracle Warehouse Builder

Using Discoverer with OracleAS Login Server

Using Discoverer with OracleAS Portal

18 EUL status workbooks

EUL status workbooks

What are EUL status workbooks?

About the Discoverer EUL V5 business area

How to install the standard EUL status workbooks

How to install Oracle Applications EUL status workbooks

How to run Oracle Applications EUL status workbooks

How to uninstall the Discoverer EUL V5 business area

About the EUL Data Definition workbook

About the Query Statistics workbook

Notes on EUL status workbooks and the table DBA_JOBS_RUNNING

19 Predicting query performance

Predicting query performance

About predicting query performance

How does query prediction work in Discoverer Plus?

Why might query prediction not be available?

About improving the speed and accuracy of query prediction

How to make the necessary database views available for query prediction

How to verify and change the timed_statistics parameter for query prediction

How to analyze data tables

How to verify and change the optimizer_mode parameter for query prediction

How to reduce long query prediction times

How to use query prediction with secure views

How to delete old query prediction statistics

20 Discoverer registry settings

Discoverer registry settings

What are Discoverer registry settings?

About Discoverer Plus and Discoverer Viewer registry settings

About Discoverer Administrator and Discoverer Desktop registry settings

Which registry settings improve Discoverer Desktop performance?

What are the Discoverer Administrator and Discoverer Desktop registry settings?

How to edit Discoverer Administrator and Discoverer Desktop registry settings

21 Oracle Discoverer command line interface

Oracle Discoverer command line interface

Which Discoverer command line interfaces are available?

What is the Oracle Discoverer command line interface?

What are the differences between the Discoverer command line interface and the
Discoverer EUL command line for Java

What is a Discoverer command line interface command?

What is a Discoverer command line interface command modifier?

About using the Discoverer command line interface

What privileges are required to run the Discoverer command line interface?

Notes on using commands

What command syntax conventions does this guide use?

What is the syntax for commands?

Examples of commands

What is a command file?

How to create a command file

How to run a command file

Examples of command files

Quick guide to Discoverer Administrator commands

Discoverer Administrator command reference

/?

/apps_fndnam

/apps_user

/asm

/cmdfile

/connect

/create_eul

/create_eul /apps_mode

/delete

/delete_bus_area

/delete_eul

/export (EUL objects)

/import (Business Area)

/import (EUL objects)

/load

/refresh_bus_area

/refresh_folder

/refresh_summary

Discoverer Administrator command modifier reference

/aggregate

/all

/apps_grant_details

/apps_responsibility

/apps_security_group

/asm_space, /asm_tablespace

/audit_info

/ba_link

/business_area

/capitalize

/condition

/date_hierarchy

/db_link

/description

/eul

/eul_language

/external_element

/folder

/function

/hier_node

/hierarchy

/identifier

/insert_blanks

/item

/item_class

/join

/keep_folder

/keep_format_properties

/log

/log_only

/lov

/object

/overwrite

/parameter

/password

/private

/refresh

/remove_prefix

/rename

/schema

/show_progress

/sort_folders

/sort_items

/source

/summary

/user

/workbook

/xmlworkbook

Discoverer Desktop command reference

/?

/batch

/connect

/export

/open

/opendb

/parameter

/p

/pt

/savedb

/sheet

22 Discoverer support for non-Oracle databases and Oracle Rdb

Discoverer support for non-Oracle databases and Oracle Rdb

What are Heterogeneous Services?

About using generic connectivity to connect to non-Oracle databases

What is the difference between using generic connectivity and ODBC drivers to
connect to non-Oracle databases?

What are the advantages of using generic connectivity?

How to set up generic connectivity for Discoverer using Enterprise Manager

How to migrate an EUL from a non-Oracle database (accessed using native
ODBC drivers) to an Oracle database (to support generic connectivity)

About using the Transparent Gateway to connect to non-Oracle databases

About Discoverer support for Oracle Rdb

What are the software requirements for using Discoverer with Oracle Rdb?

Which Discoverer features are not supported by Oracle Rdb?

Which Discoverer features are partially supported by Oracle Rdb?

23 Upgrading from earlier versions of Discoverer

Upgrading from earlier versions of Discoverer

Which upgrade path to take?

What are the prerequisites for upgrading to Discoverer Version 9.0.4?

About Discoverer versions and EUL versions

An overview of the upgrade process

Upgrading from Discoverer Release 3.1 (or earlier) to Discoverer Version 9.0.4

Upgrading from Discoverer Release 4.1 to Discoverer Version 9.0.4

About upgrading from Discoverer Release 4.1 to Discoverer Version 9.0.4

Upgrade step 1: Back up the Release 4.1 EUL

Upgrade step 2: Upgrade the Release 4.1 EUL

Upgrade step 3: Refresh summary folders

Upgrade step 4: Migrate users to Discoverer Version 9.0.4

Upgrade step 5: Remove the Release 4.1 EULs

Upgrade step 6: Remove Discoverer Release 4.1 products from client machines

Upgrading from Discoverer Version 9.0.2 to Discoverer Version 9.0.4

About upgrading from Discoverer Version 9.0.2 to Discoverer Version 9.0.4

Upgrade step 1: Back up the Version 5 EUL

Upgrade step 2: Upgrade the Version 5 EUL

Upgrade step 3: Migrate users to Discoverer Version 9.0.4

Upgrade step 4: Remove Discoverer Version 9.0.2 products from client machines

Notes about upgrading to Discoverer Version 9.0.4

Notes about upgrading Oracle Applications EULs

Notes about upgrading EULs that contain analytic functions

Notes about upgrading the Video Stores tutorial

Notes about upgrading scheduled workbooks

Notes about upgrading workbooks saved in .dis files

Notes about upgrading Discoverer systems that use the EUL Gateway

Notes about upgrading summary folders containing SET operators

Notes about upgrading identifiers containing invalid characters

Part II Reference information

24 Reference dialogs

Reference dialogs

List of reference dialogs

Add Combinations of Items dialog

Add Items to Summary Folder dialog

Alphabetical Sort dialog

Business Area Properties dialog

Changing Identifier Warning dialog

Choose Folder dialog (for an item, condition or join)

Choose Join dialog

Choose user or table/view dialog

Condition Properties dialog

Confirm Business Area Delete dialog

Confirm Folder Delete dialog

Confirm Delete dialog

Connect dialog

Create EUL Wizard dialog

Create EUL Wizard: Step 2 dialog

Create EUL Wizard: Step 2 dialog (Oracle Applications EUL)

Custom Folder dialog

Custom Folder Properties dialog

Database Information dialog

Database Storage Properties dialog: List of tabs

Database Storage Properties dialog: Materialized Views tab

Database Storage Properties dialog: Mappings tab

Database Storage Properties dialog: Properties tab

Database Storage Properties dialog: Tablespaces tab

Date formats dialog

Delete EUL dialog

Edit Calculation dialog

EUL Manager dialog

EUL Upgrade Error dialog

EUL Upgrade dialog

Export Wizard Step 1 dialog

Export Wizard: Step 2 dialog (to export selected business areas)

Export Wizard Step 2 dialog (to export selected objects)

Export Wizard: Step 2/3 dialog

Folder Properties dialog

General tab

Dependents tab

Components tab (complex folders)

Hierarchy Wizard dialog

Hierarchy Wizard: Step 3 dialog (items that use date hierarchy)

Hierarchy Wizard: Step 3/4 dialog (name hierarchy)

Impact dialog

Import PL/SQL Functions dialog

Import Wizard: Step 1 dialog

Import Wizard: Step 2 dialog

Import Wizard: Step 3 dialog,

Install/Uninstall Tutorial Wizard: Step 1 dialog

Install Tutorial Wizard Step 1

Uninstall Tutorial Wizard Step 1

Install/Uninstall Tutorial Wizard: Step 2 dialog

Install Tutorial Wizard Step 2

Uninstall Tutorial Wizard Step 2

Install Tutorial Wizard: Step 3 dialog

Item Class Wizard dialog

Item Class Wizard: Advanced options for list of values dialog

Item Class Wizard: Choose Attributes dialog

Item Class Wizard: Select Item dialog (if you chose alternative sort)

Item Class Wizard: Select Item dialog (on which to base LOV)

Item Class Wizard dialog (select items that use this item class)

Item Properties dialog

Items dialog

Join Properties dialog

General

Dependents

Join Wizard: Step 1 dialog

Join Wizard: Step 2 dialog

Load Wizard: Step 1 dialog

Load Wizard: Step 2 dialog

Load Wizard: Step 3 dialog

Load Wizard: Step 4 dialog

Load Wizard: Step 5 dialog

Manage Folders dialog: By Business Area tab

Manage Folders dialog: By Folder tab

Manage Folders dialog: Orphaned Folders tab

Manage Scheduled Workbooks dialog

Name Summary Folder dialog

For summary folders based on summary tables or materialized views

For summary folders based on external summary tables

New Condition dialog (Advanced)

New Condition dialog (select an item or folder for)

New/Edit Condition dialog

New Date Format dialog

New Item dialog (select a folder for)

New Item dialog

New Join dialog (select a folder for)

Online Dictionary Options dialog

Open Business Area dialog

Options dialog: Connection tab

Connection tab

Options dialog: Default EUL tab

PL/SQL Functions dialog: Arguments tab

PL/SQL Functions dialog: Functions tab

Privileges dialog: Privileges tab

Privileges dialog: Query Governor tab

Privileges dialog: Scheduled Workbooks tab

Privileges dialog: User/Role tab

Properties dialogs

Refresh Business Area dialog

Refresh Summaries dialog

Refresh Summary Folder dialog

For summary folders (based on Discoverer summary tables or materialized views)
refreshed by Discoverer

For summary folders (based on external summary tables) refreshed by another
application

Refresh Wizard dialog

Refresh Wizard Step 1

Refresh Wizard Step 2

Repository Users dialog

Responsibilities dialog

Schedule Workbook dialog: General tab

Scheduled Workbook dialog: Parameter values

Schedule Workbook dialog: Schedule tab

Security dialog: Business Area - > User tab

Security dialog: Users - > Business Area tab

Select items to include in this hierarchy dialog

Summary Properties dialog

Summary Wizard (ASM): List of dialogs

Summary Wizard (ASM): Change default settings: List of dialog tabs

Summary Wizard (ASM): Change default settings dialog: Folders tab

Summary Wizard (ASM): Change default settings dialog: Analyze tab

Summary Wizard (ASM): Change default settings dialog: Deletion tab

Summary Wizard (ASM): Change default settings dialog: Query Usage tab

Summary Wizard (ASM): Change default settings dialog: Query User tab

Summary Wizard (ASM): Not Analyzed dialog

Summary Wizard (ASM): Recommended Summaries dialog

Summary Wizard (ASM): Step 2 Analyze Folders dialog

Summary Wizard (ASM): Step 3 Allocate Space dialog

Summary Wizard: Step 1 dialog

Summary Wizard: Step 2 dialog

Use Query Statistics dialog

Using External Tables dialog

Using Help

Values dialog

Workarea: Data tab

Workarea: Hierarchies tab

Workarea: Item Classes tab

Workarea: Summaries tab

25 Using SQL files and trace files

Using SQL files and trace files

List of SQL files supplied with Discoverer

Working with Oracle Support to resolve Discoverer issues

How to create trace files to diagnose Discoverer problems

Glossary

Index