Session id: 40106

2. Session id: 40106 Darrell HilliardSenior Delivery Manager Oracle University Oracle Corporation

3. Session id: 40106 Tips and Techniques to Enhance and Optimize Access to Your data using Discoverer

4. Overview • Overview of Discoverer • Strategies for Optimizing Queries • Query Creation • Batch Scheduling • Complex Folders • Summary Tables • Materialized Views • Calculations • Indexes • Questions • Summary

5. Discoverer Product Set Discoverer Desktop DiscovererAdministrator DiscovererPlus Discoverer Viewer DiscovererPortlet Provider End User Layer Data warehouse, data mart, or relational database

6. 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

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

8. Query Governor Options Discoverer Plus Discoverer Administrator

9. Batch Scheduling • Allows reports to be created now, but run at either a later time or on a regular basis

10. 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

11. Batch Scheduling Can be controlled in Discoverer Administrator

12. 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

13. Complex Folders Complex Folders Simple Folders Items

14. Complex Folders Sales Analysis Times Products Stores • Store Key • City • Region • Product Key • Description • Type • Category • Department • Day • Month • Quarter • Year • Sales Fact • Sales • Profit • Customer Count

15. 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

16. Intelligent Joins in Discoverer Simple folders Complexfolders • Selecting items from Store and Sales Detail generates SQL that will only join the necessary base tables Sales facts Store and Sales Detail Video Analysis Stores If items in the report are based only on Sales Facts and Stores, then SQL will include join only on those tables. Products

17. 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.

18. Summary Management 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

19. Summary Management

20. Summary Management

21. Summary Management

22. Summary Management

23. Summary Management

24. Summary Management

25. Summary Management

26. Summary Management

27. Summary Management

28. Summary Management

29. Summary Management

30. Summary Management

31. Summary Management

32. Summary Management

33. Summary Management

34. Summary Management

35. Summary Management

36. Summary Management

37. Summary Management

38. Summary Management

39. 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

40. 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

41. Summary Table Redirection Summary awareness Summary tables Or Data tables

42. 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.

43. 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.

44. Analytical Functions • Ranking Functions • Window aggregate Functions • Reporting aggregate Functions • LAG and LEAD Functions • First and Last Functions

45. Creating Analytical Functions

46. Creating Analytical Functions

47. 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

48. 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

49. Registering PL/SQL Functions • Create PL/SQL function • Grant Execute to users • Register function in Discoverer Administrator • Is now usable in Discoverer Plus

50. Registering PL/SQL Functions 1 3 2