session id: 40106 darrell hilliard senior delivery manager oracle university oracle corporation

Session id: 40106

Darrell HilliardSenior Delivery ManagerOracle UniversityOracle Corporation

Session id: 40106

Tips and Techniques to Enhance and Optimize Access to Your data using Discoverer

Overview Overview of Discoverer Strategies for Optimizing Queries

– Query Creation– Batch Scheduling– Complex Folders– Summary Tables– Materialized Views– Calculations– Indexes

Questions Summary

End User Layer

Data warehouse, data mart, or relational database

Discoverer Viewer

DiscovererAdministrator

DiscovererPlus

Discoverer Desktop

DiscovererPortlet Provider

Discoverer Product Set

Report Types Complex Cross-Tab reports may use more

client memory for display than Tabular report (depending on amount of data returned)

Can avoid these if a tabular report shows the same data

Report types

Cross tab reports can often be duplicated as tabular reports, giving the same results. If you find display performance is an issue, try this.

Query Governor OptionsDiscoverer Plus Discoverer Administrator

Batch Scheduling Allows reports to be created now, but run at

either a later time or on a regular basis

Batch Scheduling Result tables are stored in the database in

one of the following areas:– User’s own schema– Repository user’s schema

If in user’s schema, user needs CREATE TABLE, CREATE PROCEDURE, CREATE VIEW privileges

Advantages to each

Batch SchedulingCan be controlled in Discoverer Administrator

Complex Folders Created in Discoverer Administrator Similar to a database view Can be built from one or more existing

folders Useful to users – allow them to choose

commonly used items from one folder instead of many simple folders

Discoverer uses intelligence to join only necessary base tables

Complex Folders

Simple Folders

Complex Folders

Items

Complex Folders

Sales Fact• Sales• Profit• Customer Count

• Product Key• Description• Type• Category• Department

• Store Key• City• Region

• Day• Month• Quarter• Year

TimesStoresProducts

Sales Analysis

Reasons for Using Complex Folders Easier than using database views No need to know SQL No need to modify the database schema,

grants, and so on Provide data security and restrictions in

addition to the underlying database security Can be used to simplify business areas with

numerous simple folders

Intelligent Joins in DiscovererSimple folders Complex folders

Video AnalysisStore and Sales Detail

Sales facts

Products

Stores

Selecting items from Store and Sales Detail generates SQL that will only join the necessary base tables

If items in the report are based only on Sales Facts and Stores, then SQL will include join only on those tables.

Summary Management

One of the most important features of Discoverer.

When used correctly, it can reduce query response time significantly, resulting in queries that take seconds rather than hours.

Managing summaries is a key to good performance with Discoverer implementations.

Summary tables (database)

Contain preaggregated and prejoined data

Hold the results of frequently run queries

Summary folders (Discoverer)• Contain information

about the summary tables

• Direct queries to run against the summary tables

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Summary Management

Materialized Views in Discoverer Discoverer Summary tables (pre-Oracle 8.1.7) Materialized views (Oracle 8.1.7 and later versions) Automatic Summary Management

– Discoverer creates and maintains summaries automatically

You can create summaries manually– From the items in the End User Layer– Using query performance statistics– Registering external summary tables

Materialized Views in Discoverer Used by Summary Manager in Discoverer Can be manually created and used by Administrator

– can base Business Area folders on MVs In either case, indexes can be used to supplement

Materialized Views to help query performance

Summary awareness

OrData

tables

Summary tables

Summary Table Redirection

Calculations in Discoverer Can be created by Discoverer Administrator or by

End User Provide strong support for analytical tasks Can be simple, such as an arithmetic operation

between items that belong to a folder, can be complex mathematical or statistical expressions.

Use of ALL in Parameter List

ALL now appears in both cross-tabular and tabular reports

Avoid using DECODE in Discoverer Administrator to add the ‘ALL’ item, as it is now included. No need to create custom folder in Discoverer Administrator.

Analytical Functions Ranking Functions Window aggregate Functions Reporting aggregate Functions LAG and LEAD Functions First and Last Functions

Creating Analytical Functions

Creating Analytical Functions

Speed and Analytical Functions Calculated Items can be created by

Administrator, and belong to a folder Calculated Items can be created by User, and

belong to workbook Discoverer Administrator may want to create

index on Analytical Function item if commonly used by Users

Registering PL/SQL Functions

– Enable use of custom functions in calculations– Extend the standard set of functions– Can be used in conditions, derived items, and

so on– Are executed for every row that is returned– Can include SQL SELECT statements

Registering PL/SQL Functions

1. Create PL/SQL function2. Grant Execute to users3. Register function in Discoverer Administrator4. Is now usable in Discoverer Plus

Registering PL/SQL Functions

1

2 3

Summary Display speed can be impacted by complex

queries Batch Scheduling can greatly reduce query time Complex folders are useful for organizing items

from numerous simple folders, and Discoverer uses intelligent joins to reduce query time

Summary tables can greatly enhance query time Calculations are widely used, and may be good

candidates for function-based indexes

Next Steps…. Recommended sessions

– Session 36019: Discoverer for Sales Trends– Session 36225: Best Implementation Practices for Discoverer

Try our hands-on labs and see Discoverer in action! See Your Business in Our Software

– Visit the DEMOgrounds for a customized architectural review, see a customized demo with Solutions Factory, or receive a personalized proposal. Visit the DEMOgrounds for more information.

Relevant web sites to visit for more information– OTN.oracle.com (Oracle TechNet)– Education.oracle.com (Oracle University)– Oracle.com/education/oln (Oracle Online Library)

Reminder – please complete the OracleWorld online session survey

Thank you.

AQ&Q U E S T I O N SQ U E S T I O N SA N S W E R SA N S W E R S