1 / 85

A presentation by Jayashree Satapathy / Krishna Mohan

Oracle Discoverer. Oracle Discoverer Administration10g. A presentation by Jayashree Satapathy / Krishna Mohan. Discoverer Definitions :. WHAT IS DISCOVERER : Oracle Discoverer (like Oracle Reports) is part of Oracle's Business Intelligence set of tools.

ayita
Download Presentation

A presentation by Jayashree Satapathy / Krishna Mohan

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Discoverer Oracle Discoverer Administration10g A presentation by Jayashree Satapathy / Krishna Mohan

  2. Discoverer Definitions : WHAT IS DISCOVERER : • Oracle Discoverer (like Oracle Reports) is part of Oracle's Business Intelligence set of tools. • Oracle Discoverer is an adhoc query tool enabling users to develop reports to meet business needs. WHY DISCOVERER : • Oracle Discoverer is intended to provide end users easy access to data and allow them to do data analysis. • Oracle Discoverer provides business users with data analysis capabilities, regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse. • Discoverer is an ad hoc reporting tool It allows end users to quickly create reports without the need for knowledge of SQL.

  3. Discoverer Architecture

  4. Discoverer Versions • Discoverer 4i (4.1.48) • Discoverer 9i (9.0.2) • Discoverer 10g Release 1(9.0.4) • Discoverer 10g Release 2(10.1.2)

  5. Discoverer Moving On Why upgrade from Discoverer 4i ? • Discoverer 10g Relational – easy transition • Discoverer 10g OLAP – new approach • Discoverer 10g & EBS 11i – considerations • Future state alternatives • BI EE tool suite (nee Siebel Analytics) • EBS R12 – analytic applications, new DBI face

  6. Why upgrade from 4i ? • Discoverer 4i was de-supported in October 2006 • Upgrade to What ? • Discoverer 10g Relational is simplest upgrade • Safe – makes a copy of 4i EUL • Low impact – runs on separate server • Verifiable – test 4i and 10g workbooks in parallel • Discoverer 10g provides trueanalytics • BI (Disocverer10g) suite provides heterogeneous data access, new presentation styles, new usage functionality

  7. DiscovererComponents • Discoverer Administrator • Discoverer Desktop • Discoverer Plus • Discoverer Viewer

  8. Comparing Discoverer4i and Discoverer10g • Discoverer Administrator (Same look and feel )

  9. Contd.. • Discoverer Desktop (Same look and feel )

  10. Contd.. • Discoverer Plus 4i

  11. Contd.. • Discoverer Plus 10g R1

  12. Contd.. • Discoverer Plus 10g R2

  13. Contd.. • Discoverer Viewer 4i

  14. Contd.. • Discoverer Viewer 10g R1

  15. Contd.. • Discoverer Viewer 10g R2

  16. export to oracle reports support of analytic functions of 9i database enhanced data aggregation improved list of values. they are now searchable and can be restricted. “find” functionality in the item navigator available in discoverer plus enhanced graphing capability analytic function templates edit titles Discoverer10g (R1) Benefits

  17. folders can be imported and exported complex folder reach through displays readable SQL supports Microsoft Excel WebQuery conditional formatting drag and drop functionality Contd..

  18. Discoverer10g (R2) Benefits • OLAP support • drag and drop in discoverer plus • conditional formatting • drilling into graphs • cascading parameters • optional parameters • dynamically link to worksheets • print to PDF • searchable workbook list • share scheduled workbooks

  19. Prerequisites • Before using Discoverer Administrator the following are the Prerequisites : • A suitable database must be installed and available. An Oracle Enterprise Edition database will support the use of materialized views to improve the performance of summary folders. • Discoverer Administrator must have been installed on a PC, typically as part of a • full Oracle Developer Suite installation. • Before end users can use Discoverer, the following are the Prerequisites: • Either one or both of the following must have been installed: • Discoverer Plus and/or Discoverer Viewer must have been installed on an • application server machine and configured correctly as part of an Oracle Business • Intelligence installation • Discoverer Desktop must have been installed on the end users’ PCs.

  20. Discoverer support for Oracle Applications users • Ad-hoc query access to Oracle Applications databases using Oracle • Applications user names, passwords and responsibilities • Oracle Applications mode EULs • An Oracle Applications mode EUL is a Discoverer End User • Layer based on an Oracle Applications schema (containing • the Oracle Applications FND(Foundation) tables and views). • Oracle Applications multiple reporting currencies • Oracle Applications multiple organizations

  21. Discoverer10g Integration User Discoverer10g • E-Business Suite End-User Layer • Discoverer 10g End-User Layer resides in 11i database • APPS_MODE option enforces Applications security for all Discoverer users • Easy migration from Discoverer 4i • Installation upgrades a copy of 4i End-User Layer to 10g • Run 4i and 10g side-by-side for User Acceptance Tests • TIP: Run Discoverer 4i and 10g on different physical servers to avoid Visibroker conflicts

  22. Connect dialog in Oracle Applications mode

  23. Connectivity details.

  24. Connectivity details contd.. • Specify the type of EUL to connect to by selecting one of the following option: • Gateway User ID (GWYUID)/Password • You can enter your Gateway User ID and Password here (the default value used is 'applsyspub/pub' if you enter nothing here). • Foundation Name (FNDNAM) -- FND NAME • You can enter the Foundation Name (the default value used is 'apps' if you enter nothing here).

  25. How to configure the Connect dialog for Oracle Application Users? • Before you connect to Oracle Discoverer as an Oracle Applications user, you must configure the Connect dialog to default to Oracle Applications users. • Select Tools > Options to display the "Options dialog: Connection tab": • After connecting to Oracle Discoverer as an Oracle Applications user, you must find an check box as shown in the fig.

  26. How does Discoverer work? • Users’ requests for information from the database are in the form of worksheets. • When a user creates or opens a worksheet ,Discoverer : • converts the worksheet into the corresponding SQL statements (e.g. by converting folder names and item names to table names and column names respectively) • sends the SQL statements to the database • displays the result set that is returned from the database • In the case of Discoverer Plus, Discoverer Desktop and Discoverer Viewer, the SQL statements are routed to the database via Discoverer processes running on an application server machine

  27. Oracle Discoverer Administration What is OracleDiscoverer Administrator? • OracleDiscoverer10g Administrator is one of the components of Oracle Discoverer. • Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer. • Discoverer Administrator’s wizard-style interfaces enable you to: (i) set up and maintain the End User Layer (EUL) (ii) control access to information (iii) create conditions and calculations for Discoverer end users to include in their worksheets • Users of Discoverer Administrator are called Discoverer managers.

  28. Implementation Flow Chart For a Discoverer implementation to be successful, it must meet users’ requirements (mandatory if one does not exist already) Create a business area and load data into it (mandatory) The structure of the business area is to be refined so that users can view data in the most flexible and understandable way. Having identified users’ requirements, you will have a good idea of which users (and groups of users) need access to which information. . Users’ requirements will determine which of the Discoverer components to be deployed.

  29. Administration Task List

  30. Sample Report to be Developed ‘Order Details’ report from ‘Order Management’ has been identified to be developed in this training The report has two tables. • OE_ORDER_HEADERS_ALL • OE_ORDER_LINES_ALL

  31. End User Layer ( EUL) • What is End User Layer ? • The End User Layer (EUL) is the metadata (i.e. data about the actual data in a • database) that is simple and easy for Discoverer end users to understand. We use • Discoverer Administrator to create, customize, and maintain this view for our users • so they can easily access data in Discoverer. We must have access to at least one EUL • in order to use Discoverer. • Why End User Layer ? • The EUL insulates Discoverer end users from the complexity usually associated with • databases. It provides an intuitive, business-focused view of the database using terms • that Discoverer end users are familiar with and can easily understand. This enables • Discoverer end users to focus on business issues instead of data access issues. • What does the EUL contain ? • The EUL contains the metadata that defines one or more business areas. • Business areas can be set up to reflect the needs of the user or • group of users accessing the EUL.

  32. End User Layer ( EUL) Continued… • What are EUL tables ? • Discoverer creates the EUL tables. • The EUL Manager dialog enables you to create or delete the set of tables that make up an EUL. • When a Discoverer manager defines folders and items in a business area using Discoverer Administrator, Discoverer generates the appropriate SQL statements (that define the selections from a table, view, or column) and stores them in the EUL tables. • When a Discoverer end user executes a query (in Discoverer Plus or Discoverer Viewer), Discoverer generates the corresponding SQL statement and sends it to the database, which in turn returns the results to display in Discoverer. • The Discoverer end user does not have to understand any SQL to access, analyze and retrieve data. It is all handled by Discoverer.

  33. Privileges to create an End User Layer What are the Privileges needed to create an End User Layer in an Oracle database ? • To create an End User Layer in an Oracle database, the database user that the EUL is being created in must have the following database privileges : • CREATE SESSION • CREATE TABLE • CREATE VIEW • CREATE SEQUENCE • CREATE PROCEDURE • The database user must also have the following specified : A default tablespace (this must not be a temporary tablespace) • A quota set in the default tablespace (recommended minimum is 3MB)

  34. How to create End User Layer (EUL) Use the EUL Manager to create and maintain EULs for different users (user ids) in the database Discoverer Administrator> Tools> EUL Manager

  35. End User Layer (EUL) Discoverer Administrator> Tools> EUL Manager> Create EUL • The Create EUL Wizard walks you through the steps of creating a new EUL. • The first step is to choose the user who will own the new EUL. • ‘APPS’ database user has access to all the schema used for Oracle Apps

  36. End User Layer (EUL) Discoverer Administrator> Tools> EUL Manager> Create EUL • The user creating EUL for a schema should know the password of schema’s database user id

  37. End User Layer (EUL) Discoverer Administrator> Tools> EUL Manager> Create EUL • Select the default and temporary tablespace for the new user by clicking your choice in the list. • These settings are database settings • Default table space must be at least 3mb

  38. How to know the default End User Layer (EUL) Discoverer Administrator> Tools> Options

  39. Business Area What are business areas ? • Business areas are conceptual groupings of tables and/or views designed to match Discoverer end users specific data requirements. • Example : • An accounting department needs a business area that represents • data about budgets and finance. • Alternatively, managers in a human resources department need a • business area specifically for departments and employee information. • Discoverer Administrator displays a business area as a file cabinet on the Data tab of the Workarea. • A business area can be opened to display its folders and items

  40. Business Area Contd.. Business Area Concepts

  41. Business Area Creation: Create a business area and load data into it (mandatory). • Business areas are conceptual groupings of tables and/or views • Discoverer Administrator displays a business area as a file cabinet on the Data tab of the Workarea. • You create a business area in Discoverer Administrator using the Load Wizard (User-friendly Interface) • Identify the data source and have a clear understanding of its design. • Identify which tables, views, and columns are required. Identify those that are likely to be included in multiple business areas. • Map out the necessary joins and determine whether they exist in the database or will have to be created by you using Discoverer Administrator. • Identify security issues and access privileges.

  42. Business Area- Load Wizard Discoverer Administrator> File> New The Load Wizard is where you open existing business areas or create a new one. It is the first step to working in the Administration Edition.

  43. Business Area- Load Wizard Discoverer allows you to load metadata that is present in the database, but in non-Oracle format. Discoverer Administrator> File> New

  44. Business Area- Load Wizard Discoverer Administrator> File> New Define the objects that will be loaded into the new business area. You choices depend on where you chose to get your meta data. ‘ONT’ schema is the owner of two tables in the report

  45. Business Area- Load Wizard Discoverer Administrator> File> New Select the specific objects to load into the business area. Each table and view that you select will be a folder in the business area. Two tables to be used in the report are selected

  46. Business Area- Load Wizard Discoverer Administrator> File> New These options let you make your business area and its folders easy to find and access

  47. Business Area- Load Wizard Discoverer Administrator> File> New Finally, you must name your new business area.

  48. Business Area- other features • Features provided for Business Area • Opening an Existing Business Area (File> Open) • Exporting a Business Area to a File (File> Export) • Importing EUL elements from a File (e.g.. Business Area, Folders, Functions etc) (File> Export) • Editing Business Area Properties (Double click on Business Area Icon) • Deleting a Business Area (Right Click > Delete Business Area) • Synchronizing the Business Area with the Database (File> Refresh) • Data migration issues (Analytic Functions)

  49. Access Privileges • Use Discoverer access permissions to control who can see and use the data in business areas • Use Discoverer task privileges to control the tasks each user is allowed to perform • You can grant Discoverer access permissions and task privileges to database roles as well as to database users. • Data access rights to the database tables remain under the control of the database administrator.

  50. Granting Access Permission for Business Area Tools> Security This section describes how to grant (or deny) access permission for business areas to specific users or roles. Apps and Scott Users will be able to access the business area.

More Related