18
EUL status workbooks

EUL status workbooks

This chapter explains how to use Discoverer Administrator's EUL status workbooks and contains the following topics:

• "What are EUL status workbooks?"

• "About the Discoverer EUL V5 business area"

• "How to install the standard EUL status workbooks"

• "How to install Oracle Applications EUL status workbooks"

• "How to run Oracle Applications EUL status workbooks"

• "How to uninstall the Discoverer EUL V5 business area"

• "About the EUL Data Definition workbook"

• "About the Query Statistics workbook"

• "Notes on EUL status workbooks and the table DBA_JOBS_RUNNING"

What are EUL status workbooks?

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

• Query Statistics - this workbook contains query statistics collected by Discoverer

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 are running Discoverer against a standard EUL, follow the instructions in "How to install the standard EUL status workbooks"

• if you are running 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

Note that you can only run the EUL status workbooks against Oracle databases.

About the Discoverer EUL V5 business area

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
Joins	The structures of joins defined 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.

How to install the standard EUL status workbooks

Follow these steps if you are running Discoverer against a standard EUL.

Note: You must have an EUL installed (for more information, see Chapter 3, "Creating and maintaining End User Layers").

To install the standard EUL status workbooks:

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

   Where jchan is the EUL owner and tiger is the EUL owner password.

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

3. Exit the SQL*Plus session.

4. Start Discoverer Administrator.

5. Import the file eul5.eex as follows:
   1. Choose File | Import to display the "Import Wizard: Step 1 dialog"

   2. Click Add to display the Open dialog

   3. Select the file d:\<ORACLE_HOME>\discoverer\eul5.eex, where d:\<ORACLE_HOME> is where Discoverer Administrator is installed.

   4. Click Open to return to the "Import Wizard: Step 1 dialog".

      The selected SQL file eul5.eex appears in the Import file list.

   5. Click Next to display the "Import Wizard: Step 2 dialog".

      The default settings are acceptable.

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

   7. Click Start to begin importing the EUL status workbooks.

      When the import is complete, the 'Import Successful' message is displayed.

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

Notes

• 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 delete it. 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

  Hint: 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").

How to install Oracle Applications EUL status workbooks

Follow these steps if you are running Discoverer against an Oracle Applications EUL.

Note: You must have an Oracle Applications EUL installed (for more information, see Chapter 16, "Using Discoverer with Oracle Applications").

To install the Oracle Applications EUL status workbooks:

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

   Where jchan is the EUL owner and tiger is the EUL owner password.

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

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

4. Make sure the username you entered is correct.

   If the name is not correct, re-run the script.

5. Start Discoverer Administrator.

6. Import the file eul5.eex as follows:
   1. Choose File | Import to display the "Import Wizard: Step 1 dialog"

   2. Click Add to display the Open dialog

   3. Select the file d:\<ORACLE_HOME>\discoverer\eul5.eex, where d:\<ORACLE_HOME> is where Discoverer Administrator is installed.

   4. Click Open to return to the "Import Wizard: Step 1 dialog".

      The selected SQL file eul5.eex appears in the Import file list.

   5. Click Next to display the "Import Wizard: Step 2 dialog".

      The default settings are acceptable.

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

   7. Click Start to begin importing the EUL status workbooks.

      When the import is complete, the 'Import Successful' message is displayed.

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

How to run Oracle Applications EUL status workbooks

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:

1. Start Discoverer Administrator as the EUL owner

2. 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 6, "Controlling access to information".

3. Start Discoverer Plus or Discoverer Viewer as the User or Responsibility to which you granted access.

Notes

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

How to uninstall the Discoverer EUL V5 business area

To uninstall the Discoverer EUL V5 business area:

1. Connect to Discoverer Plus as the EUL Owner and delete the following workbooks:
   • EUL Data Definition

   • Query Statistics

   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>/<eulowner @ 
   password>@<database> /delete /workbook "<workbook name>" 
   
   

   where "<workbook name>" is either "EUL Data Definition" or "Query Statistics".

2. Start Discoverer Administrator as the owner of the EUL containing the Discoverer EUL V5 business area

3. Open the Discoverer EUL V5 business area.

4. Highlight the Discoverer EUL V5 Business Area in the workarea.

5. Choose Edit | Delete to display the Confirm Business Area Delete dialog.

6. Select Delete this Business Area and its Folders.

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

8. Choose Tools | Register PL/SQL Functions to display the PL/SQL Functions dialog.

9. Highlight the following functions:

   EUL5_GET_ANALYZED
   EUL5_GET_APPS_USERRESP
   EUL5_GET_COMPLEX_FOLDER
   EUL5_GET_HEIRLVL
   EUL5_GET_HIERORD
   EUL5_GET_ISITAPPS_EUL
   EUL5_GET_ITEM
   EUL5_GET_ITEM_NAME
   EUL5_GET_OBJECT
   EUL5_GET_OBJECT_NAME
   EUL5_GET_SIMPLE_FOLDER

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

About the EUL Data Definition workbook

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.
Workbook Management	List of workbooks saved to the database and who can access them.

About the Query Statistics workbook

The Query Statistics workbook displays statistical information about the queries that end users have run. You would use the 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.

Notes on EUL status workbooks and the table DBA_JOBS_RUNNING

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:

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

   Where dba_user is a user with the dba privilege and dba_pw is the password.

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

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

   Where jchan is the EUL owner and tiger is the EUL owner password.

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