Download - 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