Transcript Summary Management

Session id: 40106
Darrell Hilliard
Senior Delivery Manager
Oracle University
Oracle 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
Discoverer Product Set
Discoverer
Administrator
Discoverer
Desktop
Discoverer
Plus
Discoverer
Viewer
End User Layer
Data warehouse, data mart,
or relational database
Discoverer
Portlet Provider
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 Options
Discoverer 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 Scheduling
Can 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
Complex Folders
Simple Folders
Items
Complex Folders
Sales Analysis
Products
• Product Key
• Description
• Type
• Category
• Department
Stores
• Store Key
• City
• Region
Sales Fact
• Sales
• Profit
• Customer Count
Times
• Day
• Month
• Quarter
• Year
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 Discoverer
Simple folders
Sales facts
Stores
Products
Selecting items from
Store and Sales Detail
generates SQL that will
only join the necessary
base tables
Complex folders
Store
andAnalysis
Sales
Video
Detail
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 Management
Summary tables
Summary folders
(database)
(Discoverer)
 Contain preaggregated • Contain information
and prejoined data
about the summary
tables
 Hold the results of
frequently run queries
• 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
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 Table Redirection
Summary
awareness
Summary
tables
Or
Data
tables
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.
2.
3.
4.
Create PL/SQL function
Grant Execute to users
Register function in Discoverer Administrator
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.
QUESTIONS
ANSWERS