Oracle Discoverer Administration Edition Administration Guide
Release 4.1 for Windows
A86730-01
Library
Index
Contents
Title and Copyright Information
Send Us Your Comments
Preface
1 Discoverer Overview
1.1 New Discoverer Features
1.2 Why do you need Discoverer?
1.2.1 Discoverer's Components
1.2.2 Discoverer Plus
1.2.3 Discoverer 4i Plus
1.2.4 Discoverer 4i Viewer
1.2.5 Administration Edition
1.2.6 End User Layer (EUL)
1.2.7 What is a Business Area?
1.2.7.1 Business Area Terminology
1.3 Discoverer Features and Benefits
1.4 Your Role as Discoverer Administrator
1.4.1 Before You Begin
1.4.2 Next Steps
2 Setting Up the Database
2.1 Scheduled Workbooks
2.1.1 Confirming that DBMS_JOBS is Installed
2.1.2 Specifying Result Set Storage
2.1.2.1 Storing the Result Set in the User's Schema
2.1.2.2 Storing the Result Set in the centralized Repository User's Schema
2.1.3 Setting the Start Time for Workbook Processing
2.2 Summary Management
2.2.1 Confirming that DBMS_JOBS is Installed
2.2.2 Privileges
2.2.3 Determining Tablespace Quotas
2.2.4 Checking Object/Schema Name
2.2.5 Setting the Start Time for Summary Processing
2.2.5.1 Limiting the Number of Processing Requests that can Run Simultaneously
2.3 Query Prediction
2.3.1 Enabling Query Prediction
2.3.1.1 What to do if Certain Views (SYS.V$xxx) are not Available
2.3.1.2 To Check the timed_statistics Parameter
2.3.1.3 To Analyze Tables
2.3.1.4 To Check the optimizer_mode Parameter
2.3.2 Reducing Long Query Prediction Times
3 Getting Started
3.1 Starting Discoverer Administration Edition
3.2 Connecting to the Database
3.3 The Work Area
3.3.1 Context-sensitive Menus in the Work Area
3.3.2 Pages of the Work Area Window
3.3.2.1 Using the Data Page
3.3.2.2 Using the Hierarchies Page
3.3.2.3 Using the Item Classes Page
3.3.2.4 Using the Summaries Page
3.4 Toolbar Icons
3.5 Using the Help Menu
4 Tutorial
4.1 Lesson 1: Creating a private End User Layer
4.1.1 Create a private End User Layer
4.2 Lesson 2: Using the Load Wizard
4.2.1 Identifying the Source Location for the Business Area
4.2.2 Selecting the User IDs and Tables
4.2.3 Selecting Tables and Views to Load into the Business Area
4.2.4 Default Business Area Design
4.2.5 Naming the Business Area
4.3 Lesson 3: Getting to Know the Work Area
4.4 Lesson 4: Granting Access Privileges
4.4.1 Granting Access Privileges to Users
4.4.2 Granting Access to the Business Area
4.5 Lesson 5: Modifying the Business Area and Folders
4.5.1 Adding a Description to the Business Area
4.5.2 Renaming and Adding Descriptions to Folders
4.5.3 Renaming and Adding Descriptions for an Item in a Folder
4.6 Lesson 6: Designing a Custom Folder
4.6.1 Creating a Custom Folder - Defining the SQL
4.6.2 Editing The Custom Folder's SQL
4.7 Lesson 7: Creating Joins
4.7.1 Creating Joins for Folders in the Business Area
4.8 Lesson 8: Customizing Items
4.8.1 Hiding Items in the Business Area
4.8.2 Setting the Display Axis for Items and Display Sequence
4.8.3 Creating Lists of Values
4.8.4 Creating an Alternative Sort
4.8.5 Creating a New Calculated Item
4.9 Lesson 9: Designing a Complex Folder
4.9.1 Creating a Complex Folder
4.9.2 Creating a Condition
4.10 Lesson 10: Working with Hierarchies
4.10.1 Defining a Simple Item Hierarchy
4.10.2 Defining a More Complex Item Hierarchy
4.10.3 Creating a Date Hierarchy Template
4.10.4 Modifying an Item's Content Type
4.10.5 Defining Drill to Detail Data
4.11 Lesson 11: Creating Summaries to Optimize Performance
4.11.1 Creating a Summary Folder
4.11.2 Setting up Internal Summary Combinations
4.11.3 Setting up the refresh schedule and naming the Summary Folder
4.12 Conclusion
5 The End User Layer
5.1 What is an End User Layer?
5.2 Creating an End User Layer
5.2.1 Required Privileges
5.2.2 Creating an EUL for an Existing User
5.2.3 Creating an EUL for a New User
5.3 Maintaining an End User Layer
5.4 Deleting an End User Layer
5.5 Moving End User Layer Elements Between Databases
5.6 Installing the Tutorial Data
5.6.1 Required Privileges
5.6.2 Installing the Tutorial Data
5.6.3 Installing the Tutorial Data in several EULs
5.6.4 Reinstalling the Tutorial Data
5.7 Uninstalling the Tutorial Data
6 Folders
6.1 Introduction
6.2 Folder Types
6.2.1 Simple Folders
6.2.2 Complex Folders
6.2.2.1 What is a Complex Folder?
6.2.2.2 Dependencies and Inheritance
6.2.2.3 Complex Folders vs. Database Views
6.2.3 Custom Folders
6.3 Adding Simple Folders from the Database
6.4 Creating Complex Folders
6.5 Creating Custom Folders
6.6 Editing Folder Properties
6.6.1 Editing the Properties of a Single Folder
6.6.2 Editing the Properties of Multiple Folders
6.6.3 The Owner attribute
6.6.3.1 Entering a value into the Owner field
6.6.3.2 Leaving the Owner field blank
6.7 Editing a Custom Folder's SQL Statement
6.8 Sharing Folders Across Business Areas
6.8.1 Assigning Multiple Folders to a Business Area
6.8.2 Assigning a Folder to Multiple Business Areas
6.9 Validating Folders
6.10 Re-ordering Folders in the Business Area
6.11 Deleting Folders
7 Business Areas
7.1 Introduction
7.2 Building a New Business Area
7.2.1 Preparing to Build a New Business Area
7.2.2 Using the Load Wizard to Build a New Business Area
7.2.2.1 What is the Load Wizard?
7.2.2.2 Starting the Load Wizard
7.2.2.3 Load Wizard: Step 1, Specifying the Metadata Source
7.2.2.4 Load Wizard: Step 2
7.2.2.5 Load Wizard: Step 3, Selecting Tables and Views
7.2.2.6 Load Wizard: Step 4, Automatic Attributes
7.2.2.7 Load Wizard: Step 5, Naming the Business Area
7.3 Opening an Existing Business Area
7.3.1 Using the Load Wizard to Open an Existing Business Area
7.3.2 Using the Open Business Area Dialog Box
7.4 Exporting a Business Area to a File
7.5 Importing EUL elements from a file
7.5.1 About Identifiers
7.5.2 Supported File Formats
7.5.3 Using the Import Wizard to import elements
7.5.3.1 What is the Import Wizard?
7.5.3.2 Starting the Import Wizard
7.5.3.3 Import Wizard: Step 1
7.5.3.4 Import Wizard: Step 2
7.5.3.5 Import Wizard: Step 3
7.6 Copying Business Areas Between EULs
7.7 Editing Business Area Properties
7.8 Deleting a Business Area
7.9 Synchronizing the Business Area with the Database
7.9.1 Refresh from Gateway
7.10 Data migration issues (Analytic Functions)
8 Access Privileges and Security
8.1 Introduction
8.2 Granting Access Permission for Business Areas
8.2.1 Specifying the Users / Roles who can Access a Business Area
8.2.2 Specifying the Business Areas a User / Role can Access
8.3 Granting Task Privileges
8.3.1 Available Tasks
8.3.1.1 Administration Edition Tasks
8.3.1.2 Discoverer Plus Tasks
8.3.2 The Privileges dialog
8.3.3 Specifying the Tasks a User / Role can Perform
8.3.4 Specifying the Users / Roles who can Perform a Specific Task
8.4 Specifying Query Retrieval Limits
8.5 Specifying Scheduled Workbook Limits
9 Scheduled Workbooks
9.1 Introduction
9.2 What Happens When a Workbook is Scheduled?
9.3 Enabling Scheduled Workbooks for a User or Role
9.4 Viewing Information About a Scheduled Workbook
9.5 Viewing an Error Message incurred by a Scheduled Workbook
9.6 Editing a Scheduled Workbook
9.7 Deleting a Scheduled Workbook's Result Set from the Database
9.8 Removing a Scheduled Workbook from the Process Queue
10 Items and Item Classes
10.1 Introduction
10.1.1 Items
10.1.2 Item Classes
10.1.2.1 Lists of Values
10.1.2.2 Alternative Sorts
10.1.2.3 Drill to Detail
10.2 Editing Item Properties
10.2.1 Editing the Properties of a Single Item
10.2.2 Editing the Properties of Multiple Items
10.3 Item Content Types
10.4 Creating an Item Class
10.4.1 Starting the Item Class Wizard
10.4.2 Choose the Item Class Attributes
10.4.3 Select the Item that Generates the List of Values
10.4.4 Select the Item that Contains the Alternative Sort Sequence
10.4.5 Select the Items that Use this Item Class
10.4.6 Specify the Name and Description for the Item Class
10.5 Editing an Item Class
10.6 Adding Items to an Item Class
10.7 Viewing the Items that use an Item Class
10.8 Removing Items from an Item Class
10.9 Viewing a List of Values
10.9.1 Viewing the List of Values for a Specific Item
10.9.2 Viewing the List of Values for an Item Class
10.10 Deleting Items & Item Classes
11 Joins
11.1 Introduction
11.2 Creating Joins
11.2.1 Using the New Join Dialog
11.2.2 Creating Multi-item Joins
11.3 Editing Join Properties
11.3.1 Editing the Properties of a Single Join
11.3.2 Editing the Properties of Multiple Joins
11.4 Editing Joins
11.5 Deleting Joins
11.6 Fan Traps
11.6.1 Fan Traps inside Complex Folders
12 Calculations
12.1 Introduction
12.1.1 What is a Calculated Item?
12.1.1.1 Derived Items
12.1.1.2 Aggregate Calculations
12.1.1.3 Restrictions on Aggregate Calculations
12.1.1.4 Aggregate Derived Items
12.1.2 Advantages of Calculations
12.1.3 Calculations and Analytic Functions
12.1.4 More information
12.2 Creating Calculations
12.3 Editing Calculation Properties
12.3.1 Editing the Properties of a Single Item
12.3.2 Editing the Properties of Multiple Items
12.4 Editing Calculations
12.5 Deleting Calculations
12.6 Registering Custom PL/SQL Functions
12.6.1 Registering Custom PL/SQL Manually
12.6.2 Registering PL/SQL Functions Automatically
13 Conditions
13.1 Introduction
13.1.1 What is a Condition?
13.1.2 Condition Types
13.2 Creating Conditions
13.2.1 Single Item Conditions
13.2.2 Multiple Item Conditions
13.3 Editing Condition Properties
13.3.1 Editing the Properties of a Single Condition
13.3.2 Editing the Properties of Multiple Conditions
13.4 Editing Conditions
13.5 Deleting Conditions
13.6 Example Conditions
13.6.1 Sales in the Last Seven Days
13.6.2 Shipments in Q3
13.6.3 Outer Join with Conditions behavior
14 Hierarchies
14.1 Introduction
14.1.1 What is a Hierarchy?
14.1.2 Types of Hierarchy
14.1.2.1 Item Hierarchies
14.1.2.2 Date Hierarchies
14.1.3 Date Hierarchy Templates
14.2 Creating Hierarchies
14.2.1 Creating Item Hierarchies
14.2.2 Creating Date Hierarchies
14.2.3 Date Format and Date Format Masks
14.2.3.1 Date Format
14.2.3.2 Date Format Mask
14.2.3.3 The EUL_DATE_TRUNC function
14.3 Editing Hierarchies
14.4 Editing Date Hierarchy Templates
14.5 Applying Date Hierarchy Templates to Date Items
14.6 Setting the Default Date Hierarchy Template
14.7 Deleting Hierarchies
14.8 Date Hierarchies and Performance
15 Summaries
15.1 Introduction
15.1.1 What is a summary?
15.1.1.1 Summary tables or Materialized Views?
15.1.1.2 What is a Summary Folder?
15.1.2 Summary Combinations
15.1.2.1 Summary Combinations
15.1.3 Summary tables/Materialized Views (MVs)
15.1.3.1 Discoverer summary tables versus server Materialized Views (8.1.6+)
15.1.3.2 Managed versus external summary tables/MVs
15.1.3.3 Registering external summaries against tables and views in Oracle 8.1.6+
15.1.3.4 Refreshing summary data
15.1.4 Summary Redirection
15.1.4.1 Summary redirection or query rewrite in Discoverer Plus?
15.1.4.2 Overview
15.1.4.3 Viewing Summary Redirection in Discoverer Plus
15.1.5 An Example
15.2 Good Summary Folder Design
15.2.1 Creating appropriate Summary Combinations
15.2.2 Hints for setting up Summary Folders
15.2.3 Notes on when an expression will use a summary
15.3 How to create Summary Folders
15.3.1 Prerequisites
15.3.2 Creating Summary Folders Based on Items in the EUL
15.3.3 Creating Summary Folders Based on Query Statistics
15.3.4 Creating Summary Folders Based on External summary tables
15.4 Editing Summary Folder Properties
15.4.1 Editing the Properties of a Single Summary Folder
15.4.2 Editing the Properties of Multiple Summary Folders
15.5 Editing Summary Folders
15.5.1 Editing a Summary Folder
15.6 Refreshing Summary Folders
15.6.1 What Happens When a Summary Folder is Refreshed?
15.6.1.1 Manually Refreshing a Summary Folder
15.6.1.2 Refresh following Import/Export across pre-8.1.6 and 8.1.6+ database versions
15.7 Viewing the Status of Managed summary tables
15.8 Deleting Summary Folders
15.9 Editing Database Storage Properties
15.9.0.3 Refresh Options (Oracle 8.1.6+ only)
16 Automated Summary Management
16.1 Introduction
16.1.1 What is Automated Summary Management?
16.1.2 How does ASM work?
16.1.3 When to use ASM or create summaries manually?
16.1.4 Running ASM
16.1.4.1 How often do I need to run ASM?
16.1.4.2 How do I run ASM?
16.1.4.3 Using the command line
16.1.4.4 Using a batch file and the scheduler
16.1.4.5 Using the Summary Wizard
16.1.5 Running ASM after Bulk Load
16.1.6 The ASM Policy (User Defined Constraints and Options)
16.1.6.1 Overview
16.1.6.2 Space options
16.1.6.3 Advanced settings
16.1.6.4 Performance versus coverage
16.2 Running ASM using the Summary Wizard
16.2.1 Prerequisites
16.2.2 Launch the Summary Wizard - Step 1
16.2.3 Analyze folders - Step 2
16.2.3.1 When some folders cannot be analyzed
16.2.4 Allocate space for summaries - Step 3
16.2.5 Recommended summaries dialog
16.2.6 Change default settings dialog
16.2.6.1 Analyze tab
16.2.6.2 Folders tab
16.2.6.3 Query User tab
16.2.6.4 Query Usage tab
16.2.6.5 Deletion tab
16.2.7 Refreshing ASM summaries
17 Using Discoverer with Oracle Applications
17.1 Supported Features
17.2 Prerequisites
17.2.1 Oracle Applications installed
17.2.2 Oracle Applications versions supported by Discoverer
17.2.3 For Oracle Applications users to launch Discoverer 4i Plus via a link on an Oracle Applications page.
17.3 Configuring the Connect dialog for Administration Edition and Discoverer Plus (for Windows)
17.3.1
Entering Details into the fields GWYUID/Password and FNDNAM
17.4 Configuring the Connect dialog for Discoverer 4i Plus and Discoverer 4i Viewer
17.5 How to use Discoverer Administration Edition in Applications Mode
17.5.1 Enable Discoverer Applications Mode
17.5.2 Enable Multiple Organizations Support in Discoverer
17.5.3 Changes in Behavior
17.5.3.1 Privileges and security
17.5.3.2 Managed Summaries
17.5.3.3 Secure Views and Language Settings
17.6 Create an Applications Mode EUL
17.7 Connect to an Applications Mode EUL
17.7.1 Connect as the EUL Owner to grant permissions and task privileges to other Oracle Applications users
17.7.1.1 Grant permissions to Oracle Applications users
17.7.2 Connect as an Applications user
17.7.2.1 Before you start
17.7.2.2 Connect
17.7.3 Oracle Applications Responsibilities
17.8 Granting Access Permission for Business Areas
17.8.1 Specifying the Users/Responsibilities who can access a Business Area
17.8.2 Specifying the Business Areas a User/Responsibility can Access
17.9 Granting Task Privileges
17.9.1 Specifying the Tasks a User/Responsibility can Perform
17.9.2 Specifying the Users / Responsibilities who can Perform a Specific Task
A Error Messages
A.1 Introduction
A.2 Discoverer Administration Edition Errors
B The EUL Status Workbooks
B.1 Introduction
B.2 Installation
B.2.1 Standard EUL Status workbooks install steps
B.2.1.1 Prerequisites
B.2.1.2 Install the workbooks
B.2.1.3 Delete Business Area/remove PL/SQL files
B.2.2 Oracle Applications EUL Status workbooks install steps
B.2.2.1 Prerequisites
B.2.2.2 Install the workbooks
B.2.2.3 Enable workbooks to run in Discoverer Plus for Oracle Applications EUL
B.2.3 Un-installing Discoverer V4 EUL Business Area
B.3 EUL Data Definition
B.4 Query Statistics
B.5 Creating Your Own Workbooks
B.5.1 Grant SELECT on dba_jobs_running
C Query Prediction
C.1 What is Query Prediction?
C.2 Enabling and Configuring Query Prediction
C.3 Improving the Accuracy of Query Prediction
C.4 Running queries against Secure Views and making Query Prediction faster
C.5 Deleting Old Query Prediction Statistics
D Command-line Interface
D.1 Introduction
D.2 How to use the Discoverer Command Line Interface
D.3 Required Privileges
D.4 Using Commands
D.5 Restrictions
D.6 Command Syntax
D.7 Syntax Conventions
D.8 Using Command Files
D.9 Command Reference
D.9.1 Quick Guide to commands
D.9.2 How the Command Reference section is arranged
D.9.3 Displaying Command-line Help
D.9.4 Running Command Files
D.9.5 Connecting to an EUL
D.9.6 Creating an Applications Mode EUL
D.9.7 Setting connection options for Oracle Applications users
D.9.8 Change an existing standard database user to an Oracle Applications user.
D.9.9 Connect as an Oracle Applications user
D.9.10 Creating an EUL
D.9.11 Bulk Loading Data into an EUL
D.9.12 Deleting a Business Area
D.9.13 Deleting an EUL
D.9.14 Deleting an EUL element
D.9.15 Running Automated Summary Management (ASM)
D.9.16 Refreshing a Business Area
D.9.17 Refreshing a Folder
D.9.18 Refreshing a Summary
D.9.19 Import a Business Area
D.9.20 Importing EUL elements from an EEX File
D.9.21 Exporting EUL elements to an EEX File
D.10 Command Modifier Reference
D.10.1 Introduction
D.10.2 How the Command Modifier Reference section is arranged
D.10.3 /aggregate
D.10.4 /all
D.10.5 /apps_grant_details
D.10.6 /apps_responsibility
D.10.7 /apps_security_group
D.10.8 /asm_space, /asm_tablespace
D.10.9 /audit_info
D.10.10 /ba_link
D.10.11 /business_area
D.10.12 /capitalize
D.10.13 /condition
D.10.14 /date_hierarchy
D.10.15 /db_link
D.10.16 /description
D.10.17 /eul
D.10.18 /folder
D.10.19 /function
D.10.20 /hier_node
D.10.21 /hierarchy
D.10.22 /identifier
D.10.23 /insert_blanks
D.10.24 /item
D.10.25 /item_class
D.10.26 /join
D.10.27 /keep_folder
D.10.28 /keep_format_properties
D.10.29 /log
D.10.30 /log_only
D.10.31 /lov
D.10.32 /multi_commit
D.10.33 /object
D.10.34 /overwrite
D.10.35 /parameter
D.10.36 /password
D.10.37 /private
D.10.38 /refresh
D.10.39 /remove_prefix
D.10.40 /rename
D.10.41 /schema
D.10.42 /show_progress
D.10.43 /source
D.10.44 /summary
D.10.45 /user
D.10.46 /workbook
E Registry Settings
E.1 Introduction
E.2 Registry Settings
F RDB Support
F.1 Software requirements
F.2 Features not supported using RDB
Glossary
Index
Copyright © 2000 Oracle Corporation.
All Rights Reserved.
Library
Index
