This chapter explains how to use Discoverer Administrator's EUL status workbooks and contains the following topics:
EUL status workbooks are Discoverer workbooks that provide useful reports for managing and documenting Discoverer End User Layers (EULs). The EUL status workbooks query information from folders in the Discoverer EUL V5 business area. You can analyze EUL status workbooks in Discoverer Plus or Discoverer Viewer.
You can give any Discoverer user access to the EUL status workbooks. However, the EUL status workbooks are intended primarily for administrative purposes.
The following EUL status workbooks are supplied with Discoverer:
EUL Data Definition - this workbook contains information about data in the EUL
EUL Query Statistics - this workbook contains query statistics collected by Discoverer
EUL Access - this workbook contains information on users, access to database objects, security
EUL Workbook management - this workbook contains information about the workbooks and worksheets saved to the EUL
Instructions explaining how to use each workbook are contained within individual worksheets.
Although the EUL status workbooks are supplied with Discoverer, they are not installed by default. How to install the workbooks depends on the type of EUL as follows:
if you run Discoverer against a standard EUL, follow the instructions in "How to install the standard EUL status workbooks"
if you run Discoverer against an Oracle Applications EUL, follow the instructions in "How to install Oracle Applications EUL status workbooks"
The EUL status workbooks are installed into the <ORACLE_HOME>\discoverer directory.
Having installed the EUL status workbooks, you can:
edit the Discoverer EUL V5 business area
edit the supplied EUL status workbooks
create your own EUL status workbooks
You can edit the workbooks or the Discoverer EUL V5 business area to suit your requirements. You can also create your own EUL status workbooks.
The Discoverer EUL V5 business area is similar to any other business area in the EUL. The folders within Discoverer EUL V5 business area include:
| Folder | Description |
|---|---|
| BA & Folders | Business areas and folders defined in the EUL |
| Folders & Items | Folders and items defined in the EUL |
| Users queries | Users queries recorded in the EUL |
| Joins | The structure of joins defined in the EUL |
| Conditions | The structure of mandatory and optional conditions in the EUL |
| Hierarchies | The structure of hierarchies |
| Item Classes | The structure of Item Classes or LOVs |
| Summary Mappings | Mapping of summary tables to folders |
| Security | Users access to business areas |
| Privileges | Users Discoverer privileges |
| Workbook Management | List of workbooks saved to the database and who can access them |
You can use the folders in the Discoverer EUL V5 business area to build your own workbooks to analyze and document the structure of your Discoverer business areas.
Note that certain folders in the Discoverer EUL V5 require the EUL owner to have SELECT access to the DBA_JOBS_RUNNING database table. For more information, see "Notes on EUL status workbooks and the table DBA_JOBS_RUNNING".
If you want to modify the supplied EUL status workbooks, we recommend you save a copy with a different name and modify the copy. Otherwise, any changes you make could be lost because the supplied EUL status workbooks are replaced with each new version of Discoverer.
Follow these steps if you run Discoverer against a standard EUL.
Note: You must have an EUL installed (for more information, see Chapter 4, "Creating and Maintaining End User Layers").
To install the standard EUL status workbooks:
Start SQL*Plus (if it is not already running) and connect as the EUL owner.
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> connect jchan/tiger@database;
Where jchan is the EUL owner and tiger is the EUL owner password.
Run the SQL file eul5.sql
For example you might type the following at the command prompt:
SQL> start d:\<ORACLE_ HOME>\discoverer\util\eul5.sql
Where <ORACLE_HOME> is where Discoverer Administrator is installed.
The eul5.sql file creates some custom PL/SQL functions required by the Discoverer EUL V5 business area.
Exit the SQL*Plus session.
Start Discoverer Administrator.
Import the file eul5.eex as follows:
Choose File | Import to display the "Import Wizard: Step 1 dialog"
Click Add File to display the Open dialog
Select the file <ORACLE_HOME>\discoverer\eul5.eex
Click Open to return to the "Import Wizard: Step 1 dialog".
The selected SQL file eul5.eex appears in the Import file list.
Click Next to display the "Import Wizard: Step 2 dialog".
The default settings are acceptable.
Click Next to display the "Import Wizard: Step 3 dialog,".
This dialog enables you to start the import and monitor its status as each EUL object is processed.
Click Start to begin importing the EUL status workbooks.
When the import is complete, the 'Import Successful' message is displayed.
Click Finish to close the "Import Wizard: Step 3 dialog,".
Having imported the EUL status workbooks you can now open them using Discoverer Plus or Discoverer Viewer.
If you have upgraded from Discoverer Release 4, the EUL might contain a business area called EUL for Discoverer V4. This business area is no longer required, so it can be deleted. Having deleted the EUL for Discoverer V4 business area remove the associated PL/SQL functions:
EUL4_GET_ANALYZED EUL4_GET_COMPLEX_FOLDER EUL4_GET_HEIRLVL EUL4_GET_HIERORD EUL4_GET_ITEM EUL4_GET_ITEM_NAME EUL4_GET_OBJECT EUL4_GET_OBJECT_NAME EUL4_GET_SIMPLE_FOLDER
Tip: For more information about how to remove PL/SQL functions, see the appropriate steps contained in "How to uninstall the Discoverer EUL V5 business area").
Follow these steps if you run Discoverer against an Oracle Applications EUL.
Note: You must have an Oracle Applications EUL installed (for more information, see Chapter 17, "Using Discoverer with Oracle Applications").
To install the Oracle Applications EUL status workbooks:
Start SQL*Plus (if it is not already running) and connect as the EUL owner.
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> connect jchan/tiger@database;
Where jchan is the EUL owner and tiger is the EUL owner password.
Run the SQL file eul5.sql
For example you might type the following at the command prompt:
SQL> start d:\<ORACLE_ HOME>\discoverer\util\eul5.sql
Where <ORACLE_HOME> is where Discoverer Administrator is installed.
The eul5.sql file creates some custom PL/SQL functions required by the Discoverer EUL V5 business area.
Run the SQL file eul5_apps.sql
For example you might type the following at the command prompt:
SQL> start d:\<ORACLE_ HOME>\discoverer\util\eul5_apps.sql
Where <ORACLE_HOME> is where Discoverer Administrator is installed.
The eul5_apps.sql file creates some custom PL/SQL functions required by the Discoverer V5 EUL business area.
You are prompted for the username of the Oracle Applications FNDNAM schema.
Enter the username of the FNDNAM schema.
Note: Only enter the FNDNAM schema's username. Do not enter a password
You are prompted to check the username you entered.
Make sure the username you entered is correct.
If the name is not correct, re-run the script.
Start Discoverer Administrator.
Import the file eul5.eex as follows:
Choose File | Import to display the "Import Wizard: Step 1 dialog"
Click Add File to display the Open dialog
Select the file <ORACLE_HOME>\discoverer\eul5.eex.
Click Open to return to the "Import Wizard: Step 1 dialog".
The selected SQL file eul5.eex appears in the Import file list.
Click Next to display the "Import Wizard: Step 2 dialog".
The default settings are acceptable.
Click Next to display the "Import Wizard: Step 3 dialog,".
This dialog enables you to start the import and monitor its status as each EUL object is processed.
Click Start to begin importing the EUL status workbooks.
When the import is complete, the 'Import Successful' message is displayed.
Click Finish to close the "Import Wizard: Step 3 dialog,".
Having imported the EUL status workbooks you can now open them using Discoverer Plus or Discoverer Viewer.
To run the EUL status workbooks with an Oracle Applications EUL in Discoverer Plus, you must connect as an Oracle Applications User or Responsibility. You cannot run the EUL status workbooks as the EUL owner.
To run Oracle Applications EUL status workbooks:
Start Discoverer Administrator as the EUL owner
Grant access to the EUL5 business area to either an Oracle Applications User or an Oracle Applications Responsibility.
For more information about granting access to business areas, see Chapter 7, "Controlling Access to Information".
Start Discoverer Plus or Discoverer Viewer as the User or Responsibility to which you granted access.
The EUL status workbooks are shared with the PUBLIC user and are owned by the EUL owner. In other words, all users who can access the Discoverer EUL V5 business area can successfully run both the EUL Data Definition and Query Statistics workbooks.
To uninstall the Discoverer EUL V5 business area:
Connect to Discoverer Plus as the EUL Owner and delete the following workbooks:
EUL Data Definition
EUL Query Statistics
EUL Access
EUL Workbook Management
Note: If the EUL status workbooks are installed in an Oracle Applications EUL, you cannot connect to Discoverer Plus as the EUL Owner to delete the workbooks. Instead, use the Discoverer Administrator command-line interface to delete the workbook by typing the following at the command prompt:
<ORACLE_HOME1>\bin\dis51adm.exe /connect <eulowner>/password>@<database> /delete /workbook "<workbook name>"
where "<workbook name>" is either "EUL Data Definition", "EUL Query Statistics", "EUL Access" or "EUL Workbook Management".
Start Discoverer Administrator as the owner of the EUL containing the Discoverer EUL V5 business area
Open the Discoverer EUL V5 business area.
Highlight the Discoverer EUL V5 Business Area in the workarea.
Choose Edit | Delete to display the Confirm Business Area Delete dialog.
Select Delete this Business Area and its Folders.
Click Yes to delete the Discoverer EUL V5 business area and its folders.
You can now remove the PL/SQL functions associated with the Discoverer EUL V5 business area.
Choose Tools | Register PL/SQL Functions to display the PL/SQL Functions dialog.
Highlight functions that have the following Function Names:
EUL5_GET_ANALYZED EUL5_GET_APPS_USERRESP EUL5_GET_AUTOGEN_ITEM_NAME EUL5_GET_COMPLEX_FOLDER EUL5_GET_DATEHIER_TMPLT_NAME EUL5_GET_EUL_DETAILS EUL5_GET_FOLDERNAME EUL5_GET_FORJ_ITEMID EUL5_GET_HIER_EXPID EUL5_GET_HEIRLVL EUL5_GET_HIERNODE_EXPID EUL5_GET_HIERORD EUL5_GET_ISITAPPS_EUL EUL5_GET_ITEM EUL5_GET_ITEM_NAME EUL5_GET_JOIN EUL5_GET_JOIN_EXPID EUL5_GET_LINURL EUL5_GET_OBJECT EUL5_GET_OBJECT_NAME EUL5_GET_SIMPLE_FOLDER eul5_post_save_document
Click Delete for each of the functions to delete the Custom PL/SQL Functions associated with the Discoverer EUL V5 business area.
You have now successfully removed the EUL status workbooks.
The EUL Data Definition workbook provides reports on the objects contained in the EUL. You would use the EUL Data Definition workbook to learn more about how EUL objects are configured. The workbook consists of the following worksheets:
| Worksheet | Description |
|---|---|
| EUL Version | The version of the Discoverer EUL you are currently using. |
| Business Areas and Folders | The folders in each business area |
| Folders and Items | The items in each folder in each business area |
| Joins | The joins defined in each business area. |
| Conditions | The conditions defined in each business area. |
| Hierarchies | The hierarchies defined in each business area. |
| Item Classes - LOVs | The List of Values defined in each business area. |
| Security | Access to business areas, by Users/Roles/Responsibilities. |
| Privileges | Access privileges, by Users/Roles/Responsibilities. |
| Summary Mappings | Mapping of folders to summaries, by business area. |
The EUL Query Statistics workbook displays statistical information about the queries that end users have run. You would use the EUL Query Statistics workbook to learn more about the nature of end user queries. The workbook consists of the following worksheets:
| Worksheet | Description |
|---|---|
| Query Statistics | The version of the Discoverer EUL to which you are currently connected. |
| Workbooks Queried by User | Workbooks queried by user. |
| Workbook Query Statistics | Query statistics about each workbook. |
| Workbooks that have used summaries. | Which workbooks have used summaries. |
| Ad-hoc Queries by User | Shows the User and the Folders and Items queried but not saved to the database. |
| Ad-hoc Queries by Workbook | Shows the workbooks queried but not saved to the database. |
| Ad-hoc Queries that used a Summary | Shows the queries not saved to the database that used a summary. |
| Advanced Query Statistics | Allows users to tune workbooks and worksheets.
Note: This worksheet is aimed at the more advanced user of the EUL Query Statistics workbook. |
The EUL Access workbook provides the following information about a user's database access privileges to database objects:
the EULs and schemas this user has access to
the database objects this user can access
the privileges given on the database objects owned by this users
the access rights this user has over other users objects
The workbook consists of the following worksheets:
| Worksheet | Description |
|---|---|
| Access to EULs | EULs in this and other database schemas that this user can access. |
| Access to Database Objects | Schemas and objects accessible by this user. |
| Access Privileges given On My Objects | Access given to other users on the database objects that this user owns. |
| Access Privileges on Other Users Objects | Access privileges on other users objects. |
The EUL Workbook Management workbook provides information about the workbooks and worksheets saved to the EUL. The workbook consists of the following worksheets:
| Worksheet | Description |
|---|---|
| EUL version | The version of the Discoverer EUL to which you are currently connected. |
| Workbook Access | Workbooks saved to the database and who can access them. |
| Worksheet SQL Inspector | Worksheets SQL saved to the database. |
| Workbook Dependency | Workbooks and worksheets items, joins and conditions dependency. |
| Workbook Dependency - Folders and Items lookup | Look up the Identifier of the folders and items that currently exist in your EUL. |
| Workbook Dependency - Joins lookup | Look up the Identifier of the joins that currently exist in your EUL. |
| Workbook Dependency - Conditions lookup | Look up the Identifier of the conditions that currently exist in your EUL. |
Certain folders (specifically the EUL5_DBA_JOBS_RUNNING folder) in the Discoverer EUL V5 business area require the EUL owner to have SELECT access to the DBA_JOBS_RUNNING database table. Without SELECT access to this table, the ORA-01031 message regarding insufficient privileges is displayed.
To grant SELECT access to DBA_JOBS_RUNNING:
Start SQL*Plus (if it is not already running) and connect as a user with dba privilege.
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> connect dba_user/dba_password@database;
Where dba_user is a user with the dba privilege and dba_pw is the password.
Grant SELECT access to DBA_JOBS_RUNNING to the EUL owner by typing the following at the command prompt:
SQL> grant select on dba_jobs_running to <EUL owner>; SQL> commit;
To confirm that the EUL owner has select access to the DBA_JOBS_RUNNING database table:
Start SQL*Plus (if it is not already running) and connect as the EUL owner.
For example, if SQL*Plus is already running, you might type the following at the command prompt:
SQL> connect jchan/tiger@database;
Where jchan is the EUL owner and tiger is the EUL owner password.
Describe the DBA_JOBS_RUNNING database table by typing the following at the command prompt:
SQL> desc DBA_JOBS_RUNNING
If the EUL owner has select access to DBA_JOBS_RUNNING, a list of columns in the table is displayed. Otherwise, a message indicating the object does not exist is displayed.