Oracle Discoverer Administrator Administration Guide
Part Number B10270-01
Title and Copyright Information
Send Us Your Comments
Oracle Discoverer Navigation and Accessibility
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
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
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
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
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 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
How to delete summary folders
How to view the status of the summary tables/materialized views for a
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
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
How to specify the business areas that an Oracle Applications user/responsibility
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
How to display your Oracle Applications database user name and responsibility in a workbook
17 Using Discoverer with components of Oracle
Using Discoverer with components of Oracle
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
/export (EUL objects)
/import (Business Area)
/import (EUL objects)
Discoverer Administrator command modifier reference
Discoverer Desktop command reference
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
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
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
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)
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
Join Properties dialog
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
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
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
Refresh Wizard dialog
Refresh Wizard Step 1
Refresh Wizard Step 2
Repository Users 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
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
Copyright © 1996, 2003 Oracle Corporation.
All Rights Reserved.