4 Creating and Maintaining End User Layers

This chapter explains how you create and maintain End User Layers using Discoverer Administrator, and contains the following topics:

• "What is an End User Layer?"

• "Why is the End User Layer useful?"

• "What are End User Layer owners?"

• "What privileges do you require to create an End User Layer in an Oracle database?"

• "What privileges do you require to create an End User Layer in a non-Oracle database?"

• "What privileges do you require to maintain an End User Layer?"

• "What is the EUL Gateway?"

• "How to create an End User Layer for an existing database user"

• "How to create an End User Layer in a new database user"

• "How to delete an End User Layer"

• "How to view or change the default End User Layer"

• "About copying EULs and EUL objects by exporting and importing"

• "Which export/import method to use"

• "How to export the entire EUL using the Discoverer Export Wizard"

• "How to export selected business areas using the Export Wizard"

• "How to export selected EUL objects using the Export Wizard"

• "How to export End User Layer objects using the Discoverer command-line interface"

• "How to import End User Layer objects using the Discoverer command-line interface"

• "How to export an EUL using the standard database export utility"

• "How to import an EUL using the standard database import utility"

• "How to import EUL objects from a file using the Import Wizard"

• "Notes about EUL creation and maintenance"

4.1 What is an End User Layer?

The End User Layer (EUL) is the metadata (that is, data about the actual data in a database) that is simple and easy for Discoverer end users to understand. You use Discoverer Administrator to create, customize, and maintain this view for your users so they can easily access data in Discoverer. You must have access to at least one EUL to use Discoverer. Access is granted using the Privileges dialog, described in Chapter 7, "Controlling Access to Information".

4.2 Why is the End User Layer useful?

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 understand easily. The EUL 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. A business area is a conceptual grouping of tables or views that apply to a user's specific data requirements. Business areas can be set up to reflect the needs of the user or group of users accessing the EUL.

  For example, an accounting department might have an accounting business area that represents data about budgets and finance, while project leaders in an engineering department might have a business area specifically for projects requiring budget information. Although some of the columns may be the same, the exact combination of tables and views for each department may be different.

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

• Discoverer exports/imports EUL objects

  You can export EUL objects (for example, business areas, workbooks, folders, items) from one database and import them into another database. For example, you might want to move EUL objects when transitioning from a development environment to a production environment.

  You can import and export EULs and EUL objects in the following ways:

  • using the Discoverer Export Wizard and Import Wizard

  • using the Discoverer command-line interface

  • using the standard database export/import commands

  For more information, see "Which export/import method to use".

Notes

• The EUL preserves the database's data integrity. Nothing that you or the Discoverer end user does with Discoverer affects the application data in the database; Discoverer only affects the metadata held in the EUL. There are however, certain PL/SQL functions that can affect the data in the database (for more information, see your database administrator).

• Although it is possible to edit EUL tables (for example, by using SQL*Plus), Oracle is unable to support such edits, and therefore recommends that you do not attempt to make edits to EUL tables.

• You can migrate Discoverer EUL metadata for use in Oracle Business Intelligence Enterprise Edition (version 10.1.3.4 and later). For more information, see the Oracle Business Intelligence Enterprise Edition documentation on Oracle Technology Network at http://www.oracle.com/technology.

4.3 What are End User Layer owners?

An End User Layer (EUL) owner is the database user that an EUL is created for.

A database user can only own one EUL. If you are connected to your own EUL and you attempt to create a new one, Discoverer Administrator prompts you to delete your existing EUL. If you create an EUL for a database user that already owns an EUL, Discoverer Administrator prompts you to delete the existing EUL before creating the new one.

The EUL owner maintains and modifies their own EUL and can grant access to the EUL to other users. Depending on the privileges given to the other users, those users can use and make changes to the EUL.

When you create an EUL, you specify who has access to it, as follows:

• every user in the database (PUBLIC access)

• only the EUL owner (PRIVATE access)

Note: It is recommended that you specify PRIVATE access, where security is an issue. You can subsequently define business area access and privileges for users and roles as required.

To change access to an existing EUL, you must be logged in as the owner of the EUL or as a user who has the following Discoverer task privileges:

• Administrator

• Set Privileges

For more information, see Chapter 7, "Controlling Access to Information".

4.4 What privileges do you require 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)

  For more information about the default tablespace, see "Create EUL Wizard: Step 2 dialog".

If you run Discoverer against an Oracle Enterprise Edition database, the database user requires further privileges to make use of Discoverer's manual summary management and Automated Summary Management (ASM) functionality. For more information, see:

• "What are the prerequisites for creating summary folders with ASM?"

• "What are the prerequisites for creating summary folders manually in Discoverer?"

The EUL Manager dialog enables you to:

• create an EUL for an existing Oracle database user (if the database user meets the above conditions)

• create an EUL for a new Oracle database user, provided you (the user creating the new user) have the following database privileges:

  • CREATE SESSION

  • CREATE USER

  • GRANT ANY PRIVILEGE

  • ALTER USER

  Note: The above privileges are all you require to create a new EUL for a new database user. You can connect to Discoverer Administrator as a database user with only these privileges and create a new EUL in a new database user.

  If you create an EUL for a new Oracle database user, Discoverer grants the necessary privileges and sets the default tablespace and quota.

  When you create a new database user (EUL owner) in Discoverer Administrator, Discoverer also grants the CREATE ROLE privilege, to enable the database user to create database roles (outside Discoverer). The database roles created outside Discoverer will enable the Administrator (within Discoverer) to define security configurations using those roles. The DBA will associate security access to the underlying tables through the Roles you have created. Hence security is not compromised by allowing a Discoverer EUL owner the privilege to create roles.

4.5 What privileges do you require to create an End User Layer in a non-Oracle database?

To create an End User Layer for a non-Oracle database user, the database user must have the following database privileges:

• CREATE SESSION (use your database's equivalent for this Oracle term)

• CREATE TABLE

• CREATE VIEW

Discoverer Administrator does not enable you to create new users for non-Oracle databases.

4.6 What privileges do you require to maintain an End User Layer?

You can maintain EULs in Discoverer Administrator if your database user has the Administration privilege.

To apply the Administration privilege to a database user, see "How to specify a user or role (responsibility) to perform a specific task".

4.7 What is the EUL Gateway?

The EUL Gateway provides a way for Discoverer to populate an EUL with metadata from another source. The EUL Gateway allows metadata defined in another tool or application to be loaded directly into the EUL.

To set up an EUL Gateway see the document eulgatew.doc located in the <ORACLE_HOME>\discoverer\kits directory.

For information about loading a business area from the EUL Gateway see "How to create a business area using the Load Wizard".

4.8 How to create an End User Layer for an existing database user

To create an EUL for an existing database user:

1. Choose Tools | EUL Manager to display the "EUL Manager dialog".

   Figure 4-1 EUL Manager dialog

   
   

2. Click Create an EUL to display the "Create EUL Wizard dialog".

   Figure 4-2 Create EUL Wizard Step 1 dialog

   
   

3. Select the Select an existing user option.

4. Select or clear the Grant access to PUBLIC check box as required:

   • select this check box to enable all users in the current database to access the new EUL

   • clear this check box to enable only the EUL owner to view data through the new EUL

   Note: It is recommended that you clear this check box where security might be an issue.

5. Select or clear the New EUL is for use by Oracle Applications users ONLY check box as required:

   • select this check box to restrict the new EUL to Oracle Applications users only (for more information about Applications Mode EULs, see Chapter 17, "Using Discoverer with Oracle Applications")

   • clear this check box to create a standard EUL

   Note: The choice that you make here is final. You cannot change a standard EUL into an Oracle Applications EUL (and vice versa).

6. Click the Select button to display the "Select User/Role dialog" where you search for, and select, the database user that you want to own the new EUL.

7. If you specified a database user other than the current user to be the EUL owner, you must specify the database user's password.

   Passwords are case-sensitive in Oracle databases (Enterprise Edition release 11.1 or later).

8. Click Finish.

   Discoverer Administrator displays the Creating EUL progress bar and creates a new EUL for the specified database user.

   When the new EUL has been created, Discoverer gives you the option to install the Discoverer sample data in the new EUL. For more information, see Chapter 27, "Installing the Discoverer Sample Data EUL, Data, and Workbook"

4.9 How to create an End User Layer in a new database user

Note: This feature is not available with non-Oracle databases. If you are using a non-Oracle database, ask your database administrator to create the necessary user IDs on the database.

For more information about the privileges required to create an EUL in a new database user, see "What privileges do you require to create an End User Layer in an Oracle database?".

To create an EUL in a new database user:

1. Choose Tools | EUL Manager to display the "EUL Manager dialog".

2. Click Create an EUL to display the "Create EUL Wizard dialog".

3. Select the Create a new user option.

   If the Create a new user option is unavailable, contact your database administrator to grant the CREATE USER privilege (for more information, see "What privileges do you require to create an End User Layer in a non-Oracle database?").

4. Select or clear the Grant EUL access to PUBLIC check box as follows:

   • select this check box to enable all users in the current database to access the new EUL

   • clear this check box to enable only the EUL owner to view data through the new EUL

   Note: It is recommended that you clear this check box where security might be an issue.

5. Select or clear the New EUL is for use by Oracle Applications users ONLY check box as follows:

   • select the check box to restrict the new EUL to Oracle Applications users only (for more information about Applications Mode EULs, see "What are Oracle Applications?")

   • clear the check box to create a standard EUL

   Note: The choice that you make here is final. You cannot change a standard EUL into an Oracle Applications EUL (and vice versa).

6. Specify a name for the new database user in the User field.

7. Specify a password for the new database user in the Password field.

   Passwords are case-sensitive in Oracle databases (Enterprise Edition release 11.1 or later).

8. Specify the new database user's password again in the Confirm Password field.

9. Click Next to display the "Create EUL Wizard: Step 2 dialog".

   Figure 4-3 Create EUL Wizard Step 2 dialog

   
   

10. Specify the default and temporary tablespaces for the new database user.

    If you are unsure which to choose, see your database administrator. For more information, see the "Create EUL Wizard: Step 2 dialog".

11. Click Finish.

    Discoverer Administrator displays the Creating EUL progress bar and creates a new database user and new EUL in that database user.

    When the new database user and EUL have been created, Discoverer gives you the option to install the Discoverer sample data in the new EUL. For more information, see Chapter 27, "Installing the Discoverer Sample Data EUL, Data, and Workbook".

4.10 How to delete an End User Layer

You might want to delete an EUL for several reasons. For example, it might be an old or a test EUL.

Note: You must connect as the owner of the EUL that you want to delete.

To delete an EUL:

1. Choose File | Connect to display the "Connect dialog".

2. Specify the username, password, and TNS alias for the owner of the EUL that you want to delete, and click Connect to display the "Load Wizard: Step 1 dialog".

3. Click Cancel to close the Load Wizard.

4. Choose Tools | EUL Manager.

5. Click Delete an EUL to display the "Delete EUL dialog".

   Discoverer prompts you to confirm that you want to delete the current EUL.

6. Make sure that the EUL specified in the EUL field is the one you want to delete and click OK.

   If the EUL name displayed is incorrect, go back to Step 2 and use the correct TNS alias to connect as the EUL owner.

   When you click OK, Discoverer prompts you to confirm that you want to delete all EUL tables. This will delete all EUL information and workbooks in the database. This will also remove all summary data and information.

7. Click Yes to continue.

8. Click OK to delete the EUL.

   Discoverer deletes the EUL objects for the current database user.

4.11 How to view or change the default End User Layer

You can view or change which EUL is the default EUL for the current database user (that is, the EUL used when the current database user connects to Discoverer Administrator).

Note: You can only select an alternative EUL if the database user has access to more than one EUL.

To view or change the default EUL:

1. Choose Tools | Options | Default EUL tab to display the "Options dialog: Connection tab".

2. (optional) Click the EUL that you want to use next time you connect as the current database user and click OK.

4.12 About copying EULs and EUL objects by exporting and importing

Typically, you will want to:

• backup or archive EULs

• distribute EULs to other locations

• copy business areas between EULs (for more information, see "How to copy business areas between EULs")

• copy EUL objects (for example, business areas, folders, hierarchies, calculations) from one EUL to another

• apply patches to a production environment

There are several different ways to copy EULs and EUL objects, depending on whether you want to:

• copy specified EUL objects into another EUL

• copy an entire EUL and re-create it as a new EUL

• copy the database user that is the EUL owner

In all these cases, you perform an export operation followed by an import operation. For more information, see "Which export/import method to use".

If you use Discoverer Administrator to export EULs or EUL objects, you will create a Discoverer EUL export file (with an EEX suffix). Having created an EEX file, you can then use Discoverer Administrator to import the EEX file.

Note that you can import EEX files:

• that have been exported from Discoverer Release 3.1 and earlier (these EUL export files are standard text files)

• that have been exported from Discoverer Release 4.1 and later (these EUL export files are in XML format, although they still have an EEX extension)

4.13 Which export/import method to use

To copy EUL objects between EULs you first export the EUL objects to a file, then import them into a new EUL or new database.

You copy EUL objects between EULs using

• the Discoverer Export Wizard and Import Wizard (for more information, see "About using the Discoverer Export Wizard and Import Wizard")

• the Discoverer command-line interface (for more information, see "About using Discoverer command-line interface to export/import EUL objects")

• the standard database export/import commands (for more information, see "About using database export/import commands to export/import complete EUL")

4.13.1 About using the Discoverer Export Wizard and Import Wizard

You use the Discoverer Export Wizard to export EUL objects to an EUL export file (with a suffix EEX). Having exported the objects, you can then import the EEX file using the Discoverer Import Wizard.

The EUL objects that you export (to an EEX file) include business areas, folders, item hierarchies, date hierarchies, item classes, workbook definitions (created in Discoverer Desktop and Discoverer Plus), PL/SQL function registration information, summary folders, and the automated summary management (ASM) policy.

See the following topics for information about exporting and importing EUL objects by using the Discoverer user interface:

• "How to export the entire EUL using the Discoverer Export Wizard"

  Use this option to export an entire EUL to a file when you want to copy objects from the EUL into a new EUL or to create a backup.

• "How to export selected business areas using the Export Wizard"

  Use this option when you want to use the export file to update an existing EUL with selected business areas.

• "How to export selected EUL objects using the Export Wizard"

  Use this option when you want to use the export file to update an existing EUL with selected EUL objects. For example, when applying a minor change to a production business area.

• "How to import EUL objects from a file using the Import Wizard"

  Use this option to import EUL objects (for example, business areas, folders, hierarchies, calculations) from one EUL, to reuse them in another EUL.

Note: The Export Wizard does not export the database, EUL tables, or database objects referenced by the EUL. To export these objects, you must follow the steps in "How to export an EUL using the standard database export utility".

4.13.2 About using Discoverer command-line interface to export/import EUL objects

You use the Discoverer command-line interface to export/import EUL objects without using the Discoverer user interface. For more information about the Discoverer command-line interface, see "What is the Oracle Business Intelligence Discoverer command-line interface?".

See the following topics for information about exporting and importing EUL objects by using the Discoverer command-line interface:

• "How to export End User Layer objects using the Discoverer command-line interface"

  Use this option to copy EUL objects from one database to another by exporting them to a Discoverer export file (.EEX) and then importing the EEX file to another database using the command-line interface.

• "How to import End User Layer objects using the Discoverer command-line interface"

  Use this option to import EUL objects into a new database.

Note: The Discoverer command-line interface export facility does not export the database, EUL tables, or database objects referenced by the EUL definitions. To export these objects, you must follow the steps in "How to export an EUL using the standard database export utility".

4.13.3 About using database export/import commands to export/import complete EUL

You use standard database export/import commands to export/import the database, EUL tables and database objects referenced by the EUL definitions.

Use the following tasks to export/import EULs between users:

• "How to export an EUL using the standard database export utility"

  Use this option to export an entire EUL to a database dump file (.DMP) when you want to an EUL into a new user or to create a backup.

• "How to import an EUL using the standard database import utility"

  Use this option to import the EUL business areas, EUL tables and saved workbooks from a database dump file (.DMP) into a new EUL owner.

How you export or import an EUL depends on:

• the version of the Oracle client software installed on your machine

• the version of the Oracle database on which the EUL resides

We recommend that the version of the Oracle database and the version of the Oracle database client software installed on your machine are the same. If the versions are not the same (for example, if the EUL is on an Oracle9i database and Oracle10g client software is installed on your machine), you might not be able to follow the instructions below. If you are unable to export the EUL, contact your database administrator and ask them to export the EUL for you.

4.14 How to export the entire EUL using the Discoverer Export Wizard

Use this option to export an entire EUL to a file when you want to copy objects from the EUL into a new EUL or to create a backup.

To export the entire EUL using the Discoverer Export Wizard:

1. Choose File | Export to display the "Export Wizard Step 1 dialog".

   Figure 4-4 Export Wizard: Step 1 dialog

   
   

2. Select the The entire End User Layer option and click Next to display the "Export Wizard: Step 2/3 dialog".

   Figure 4-5 Export Wizard: Step 2 dialog

   
   

   You use the above dialog to specify a name and location for the export file.

3. Click Browse to display the Save As dialog.

   Browse for the location to save the EUL export file.

4. Enter a suitable name for the exported EUL.

5. (optional) Select the Generate XML for exported workbooks check box to save any workbook definitions in XML format.

   Note: The export file (*.EEX) is always in XML format but workbooks by default are saved inside it as a binary to save space and time. Select this check box to save the workbook definitions additionally as XML inside the export file.

6. (optional) Select the Save export commands to a text file (*.txt) check box to save the export commands that created this export to a text file.

   This creates an additional file containing the commands used to create this export and applies the file extension txt. This file can then be used with the command-line interface (for further information about the command-line interface, see Chapter 22, "Discoverer Command-line Interface").

7. Click Finish to create the export file (and any additional files selected) in the specified location and display the Export Log.

   Figure 4-6 Export Log dialog

   
   

   The Export Log displays information about what has been exported.

8. (optional) Click Save to specify a name and location to save the Log file.

9. Click Close to finish.

4.15 How to export selected business areas using the Export Wizard

Use this option when you want to use the export file to update an existing EUL with selected business areas.

To export the selected business areas using the Export Wizard:

1. Choose File | Export to display the "Export Wizard Step 1 dialog".

   Figure 4-7 Export Wizard Step 1 dialog

   
   

2. Select the Selected business areas option and click Next to display the "Export Wizard: Step 2 dialog (to export selected business areas)".

3. Move the business areas that you want to export from the Available list to the Selected list.

   You can select more than one business area at a time by holding down the Ctrl key and clicking another business area.

4. Click Next to display the "Export Wizard: Step 2/3 dialog".

5. Click Browse to display the Save As dialog.

   Browse for the location to save the business area export file.

6. Enter a suitable name for the exported business area.

7. (optional) Select the Generate XML for exported workbooks check box to save any workbook definitions in XML format.

8. (optional) Select the Save export commands as a text file (*.txt) check box to save the export commands that created this export to a text file.

   This creates an additional text file containing the commands used to create this export and applies the file extension txt. This file can then be used with the command-line interface (for further information about the command-line interface, see Chapter 22, "Discoverer Command-line Interface").

9. Click Finish to create the export file (and any additional files selected) in the specified location and display the Export Log.

   Figure 4-8 Export Log dialog

   
   

   The Export Log displays information about what has been exported.

10. (optional) Click Save to specify a name and location to save the Log file.

11. Click Close to finish.

    Note: If you are copying business areas between EULs, continue with step 2 of the task, "How to copy business areas between EULs".

4.16 How to export selected EUL objects using the Export Wizard

Use this option when you want to use the export file to update an existing EUL with selected EUL objects. For example, when applying a minor change to a production business area.

To export the selected objects using the Export Wizard:

1. Choose File | Export to display the "Export Wizard Step 1 dialog".

2. Select the Selected Objects in the End User Layer option and click Next to display the"Export Wizard: Step 2 dialog (to export selected objects)".

   Figure 4-9 Export Wizard: Step 2 dialog

   
   

3. Use the Which objects do you want to export? drop down list to display the EUL object types that you want to export to a file.

   For example, folders, item classes and item hierarchies.

   Note: If you select a business area above, Discoverer exports just the definition of the business area and not its folders and items. To export business area folders and items, you must select them explicitly.

4. Move the object that you want to export from the Available list to the Selected list.

   You can select more than one object at a time by holding down the Ctrl key and clicking another object.

5. Click Next to display the "Export Wizard: Step 2 dialog (to export selected business areas)".

6. Click Browse to display the Save As dialog.

   Browse for the location to save the export file.

7. Enter a suitable name for the exported objects.

8. (optional) Select the Generate XML for exported workbooks check box to save any workbooks in XML format.

9. (optional) Select the Save export commands as a text file (*.txt) check box to save the export commands that created this export to a text file.

   This creates an additional text file containing the commands used to create this export and applies the extension txt. This file can then be used with the command-line interface (for further information about the command-line interface, see Chapter 22, "Discoverer Command-line Interface").

10. Click Finish to create the export file (and any additional files selected) in the specified location and display the Export Log.

    Figure 4-10 Export Log dialog

    
    

    The Export Log displays information about what has been exported.

11. (optional) Click Save to specify a name and location to save the Log file.

12. Click Close to finish.

4.17 How to export End User Layer objects using the Discoverer command-line interface

Use this option to copy EUL objects typically from one database to another, by exporting them to a Discoverer export file (.EEX) and then importing the EEX file (possibly to another database) using the command-line interface. This is the first of two tasks that enable you to copy EUL objects. You must complete this export task before you can import the EUL objects.

To export EUL objects using the Discoverer command-line interface:

1. Use the Discoverer command-line interface /export option to export the required EUL objects to a Discoverer export file (.EEX).

   For example, to export the Video Store Tutorial business area and two workbooks ("Vidstr5 Video Tutorial Workbook" and "Vidaf5 Analytic Function Examples") to the vidstr.eex file, you might type the following:

   D:\orant\bin\dis51adm.exe /connect eulowner/[e-mail] /export
    "D:\vidstr.eex" "Video Store Tutorial" /workbook "Vidstr5 - Video Tutorial
    Workbook" /workbook "Vidaf5 - Analytic Function Examples"
   

   For more information about exporting EUL objects using the Discoverer command-line interface, see "/export (EUL objects)".

Note: To copy the EUL objects back into a database, see "How to import End User Layer objects using the Discoverer command-line interface".

4.18 How to import End User Layer objects using the Discoverer command-line interface

Use this option to import EUL objects, possibly into a new database. Before you can complete this task you must export the EUL objects to a Discoverer export file (.EEX) (for more information, see "How to export End User Layer objects using the Discoverer command-line interface"). This is the second of two tasks enabling you to copy EUL objects.

To import End User Layer objects using the Discoverer command-line interface:

1. Use the Discoverer command-line interface /import option to import the EUL objects into the new EUL owner.

   For example, to import the vidstr.eex file, created above you might type the following:

   D:\orant\bin\dis51adm.exe /connect eulowner/[e-mail] /import
    "D:\vidstr.eex"
   

   For more information about importing EUL objects using the Discoverer command-line interface, see "/import (EUL objects)".

4.19 How to export an EUL using the standard database export utility

Use this option to export all business areas, EUL tables and saved workbooks to a database dump file (.DMP).

To export an EUL using the standard database export utility (assuming the EUL resides on an Oracle database (version 9.2.0.7 or later), and you are using a machine on which you have installed Oracle Developer Suite):

1. Display a command-line window (for example, by choosing Command Prompt from the Windows Start menu).

2. Navigate to the location where you want to export the EUL.

3. Type the following command:

   exp <eulowner>/<password>@<dbname> file=<filename.dmp> owner=<eulowner>
   

   For example, to export an EUL owned by a database user hrmgr to a file hreul.dmp, you would type the following:

   exp hrmgr/hrpswrd@HRDB file=hreul.dmp owner=hrmgr
   

   The EUL tables (and associated synonyms, views, and other definitions) are exported to the specified dmp file in the D:\ORACLE10 directory.

   When the export is complete, Discoverer displays the following message:

   Export terminated successfully without warnings.

4. Close the command-line window.

Having exported the EUL, you are now ready to import it into a new database user.

4.20 How to import an EUL using the standard database import utility

Use this option to import the EUL business areas, EUL tables and saved workbooks from a database dump file (.DMP) into a new EUL owner on the new database.

Note: It is recommended that this database user owns no other tables.

To import an EUL using the standard database import utility (assuming the EUL resides on an Oracle database (version 9.2.0.7 or later) and you are using a machine on which you have installed Oracle Developer Suite):

1. Display a command-line window (for example, by choosing Command Prompt from the Windows Start menu).

2. Navigate to the location where you want to import the EUL.

3. Type the following command:

   imp <eulowner>/<password>@<dbname> file=<filename.dmp> fromuser=<old_eul
   _owner> touser=<new_eul_owner>
   

   For example, to import an EUL owned by a database user hrmgr from a file hreul.dmp into a new user hrmgr2, you would type the following:

   imp hrmgr2/hrpswrd@HRDB file=hreul.dmp fromuser=hrmgr touser=hrmgr2
   

   The EUL tables (and associated synonyms, views, and other definitions) are imported from the specified dmp file in the D:\ORACLE10 directory.

   When the import is complete, Discoverer displays the following message:

   Import terminated successfully without warnings.

4. Close the command-line window.

   Note: Discoverer recognizes EULs using unique reference numbers. However, if you use the database export and import utilities to copy an EUL, the new EUL (including its reference number) will be identical to the original EUL. When EULs have the same reference number, EUL consistency issues can arise if you do both of the following:

   • if you modify objects in both the original EUL and in the new EUL

   • having modified objects in both EULs, if you then attempt to copy objects between the two EULs using the Discoverer Export Wizard and Import Wizard (or the Discoverer /export and /import commands)

   To avoid potential EUL consistency issues, run the eul5_id.sql script as the owner of the new EUL. The eul5_id.sql script gives a new reference number to the new EUL and thereby avoids any potential EUL consistency issues.

5. Follow the instructions below to run the eul5_id.sql script:

   1. Start SQL*Plus (if it is not already running) and connect as the owner of the new EUL.

      For example, if SQL*Plus is already running, you might type the following at the command prompt:

      SQL> connect hrmgr2/hrmgr2@database;
      

      where hrmgr2 is the EUL owner and hrmgr2 is the EUL owner's password.

   2. Run the eul5_id.sql script.

      For example, you might type the following at the command prompt:

      SQL> start d:\<ORACLE_ HOME>\discoverer\util\eul5_id.sql
      

      where <ORACLE_HOME> is where Discoverer Administrator is installed.

      The eul5_id.sql script gives a new unique reference number to the new EUL and thereby avoids any potential EUL consistency issues.

   3. Exit the SQL*Plus session.

Notes

• Information in the EUL identifies the owner of summary tables. When you export an EUL containing summary tables from one database user and import it into a different database user, the second database user will become the owner of the summary tables. Therefore, you will need to update the EUL summary table ownership information.

  To update summary table ownership information, run the eulsown.sql script located in the <ORACLE_HOME>\discoverer\util directory. When you run the script, you will be prompted for the name of the previous summary table owner and the name of the new summary table owner.

4.21 How to import EUL objects from a file using the Import Wizard

Use this option to import EUL objects (for example, business areas, folders, hierarchies, calculations) from one EUL, to reuse them in another EUL. You use an export file (.EEX) to update an existing EUL with selected EUL objects. For example, when applying a change to a production business area. For more information about exporting EUL objects, see "About copying EULs and EUL objects by exporting and importing".

To import EUL objects from a file using the Import Wizard:

1. Choose File | Import to display the "Import Wizard: Step 1 dialog".

   Figure 4-11 Import Wizard: Step 1 dialog

   
   

2. Click Add to display the Open dialog.

3. Locate and select one or more import files and click Open to return to the "Import Wizard: Step 1 dialog".

   Selected files appear in the Import File list.

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

   Figure 4-12 Import Wizard: Step 2 dialog

   
   

   This dialog enables you to specify how Discoverer Administrator processes matching objects from another EUL. For more information about matching objects from another EUL, see "What are identifiers?".

5. Specify what action should occur if two objects match.

   An object means any EUL object, for example, folder, item, calculated item.

6. Specify whether to preserve display related properties (only available if the Refresh the object option is selected).

7. Specify how you would like objects to be matched up.

8. Specify whether the current user should take ownership of imported workbooks.

9. Click Next to display the "Import Wizard: Step 3 dialog,".

   Figure 4-13 Import Wizard: Step 3 dialog

   
   

   This dialog enables you to start the import and monitor its status as each EUL object is processed.

10. Click Start to begin the import.

    Tip: You can stop the import at any point by clicking Cancel. For example, you might want to stop the import if there are warning messages in the Import Log.

11. (optional) After a completed import, click Save log... to save the status information to a text file in a specified location.

12. Click Finish to close the "Import Wizard: Step 3 dialog,".

Notes

• If you perform a complete import, the Data tab in the Workarea is updated to reflect the EUL objects imported, according to the match options that you have chosen.

• During the import of an EEX file where Discoverer Administrator cannot find joined folders a warning message is displayed in the Import Log dialog (for more information about exporting EUL objects and joins see "/export (EUL objects)".

4.22 Notes about EUL creation and maintenance

This section contains the following notes about EUL creation and maintenance:

• "What are identifiers?"

• "What are the space requirements and storage parameters for a typical EUL?"

• "What is a suggested workflow for initial EUL development and ongoing maintenance?"

• "Using the Discoverer workbook dump utility"

• "About creating EULs in Logical and Physical Standby (or read-only) databases"

4.22.1 What are identifiers?

Identifiers are unique names that Discoverer Administrator uses to identify unique EUL objects (and workbook objects in Discoverer Plus and Discoverer Viewer).

Discoverer Administrator uses identifiers to recognize when an object imported from another EUL refers to the same business object in the EUL you are importing into. Identifiers enable customized (or patched) EUL objects to be preserved. For example, a folder named Sales in EUL A may refer to the same folder named Sales Figures in EUL B. Both folders have the same identifier and can therefore be recognized as referring to the same EUL object.

Identifiers are visible in Discoverer Administrator but are hidden from Discoverer Plus users.

Typically, you will never need to alter an identifier. Indeed, changing identifiers is not advisable because it can affect the relationships between EUL objects. However, in some cases you might want to modify identifiers. For example, you might want to modify identifiers:

• to comply with strict naming conventions

• to re-create an object with a specific identifier because the object was incorrectly deleted from the EUL and must be re-created with the same identifier

In a future release of Discoverer, there will be a change to the valid characters that can be used in identifiers. The following characters will continue to be supported for use in identifiers in future releases of Discoverer:

• the letters A to Z in upper case

• the letters a to z in lowercase

• the numbers 0 to 9

• the underscore character ( _ )

However, the following characters will be de-supported in future releases of Discoverer:

• the exclamation mark (!)

• the tilde symbol (~)

• the asterisk symbol (*)

• the left and right parentheses characters: ( and )

• the single quotation mark (' )

• the hyphen character (-)

For this release:

• if you create an identifier that contains a de-supported character, a warning will appear

• if you import an EEX file that has identifiers that contain de-supported characters, a warning will appear in the import log

• if you upgrade an EUL that has identifiers that contain de-supported characters, messages will be displayed indicating the identifiers that contain the invalid characters

You do not need to modify any identifiers that use de-supported characters, Discoverer will automatically modify these identifiers to make them valid in future releases.

Note: To locate the identifier of a workbook or worksheet in Discoverer Plus, see the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.

4.22.2 What are the space requirements and storage parameters for a typical EUL?

The minimum default tablespace of 3MB suggested in the "Create EUL Wizard: Step 2 dialog" is based upon storage parameters that the EUL specifies when creating database objects for the EUL. If you install the tutorial, the figure of 3MB will be exceeded.

A newly created EUL has the following space requirements:

• approximately 2.8MB without tutorial data

• approximately 3.8MB with tutorial data

The amount of space that is actually used will depend on other factors, including the block size specified for the tablespace in which the EUL is created.

As an approximate guide, adding a typical business area to an EUL will increase the size of the EUL by 1MB. The more complicated the business area (for example, the more summary folders and complex items it contains), the more space the business area will require.

A default tablespace of 10-20MB will be adequate under normal conditions, but the amount of default tablespace required is determined by the amount of metadata that defines your EUL. Therefore, the default tablespace required could be larger, or smaller than the amount available in the tablespace that you select.

The database table storage parameters for EUL tables are as follows:

• EUL5_DOCUMENTS and EUL5_QPP_STATS are created with the following storage parameters:

  STORAGE  ( 
      INITIAL  1064960 
      NEXT  1024000 
      PCTINCREASE  0 
      )
  

• all other EUL tables are created with the following storage parameters:

  STORAGE  ( 
      INITIAL  1064960 
      NEXT  81920 
      PCTINCREASE  0 
      )
  

The database index storage parameters for EUL indexes are as follows:

STORAGE  ( 
    INITIAL  4096
    NEXT  8192
    PCTINCREASE  0 
    )

There are two factors that might cause the EUL to use up more space:

• block size

  Discoverer specifies an initial extent of 4096 for indexes, but the server will always allocate a minimum of two blocks for any segment. Therefore, if the block size is greater than 2k the indexes will take up more space than Discoverer calculates.

• tablespace minimum extent setting

  When a tablespace is created it is possible to specify the minimum extent for any extent created. This minimum size overrides the initial extent size if it is greater and therefore causes the EUL objects to use up more space than Discoverer calculates.

4.22.3 What is a suggested workflow for initial EUL development and ongoing maintenance?

There are typically three steps in the EUL creation and maintenance lifecycle:

• "Step 1: Prototype and design a new EUL in a development environment"

• "Step 2: Move the new EUL from a development environment to a production environment"

• "Step 3: Modify the EUL to meet new requirements"

Step 1: Prototype and design a new EUL in a development environment

As the Discoverer manager, you would usually want to create the first cut of a new EUL in a development environment rather than on the production database. The development environment must contain the same data tables that are to be used on the production database.

Creating the EUL in a development environment enables you to develop business area design without compromising the performance of production systems.

Having created business areas, folders, and items, you can create prototype workbooks. Get these prototypes reviewed by end user representatives in the development environment. When you have agreed on the workbook definitions, save them to the development environment database.

Step 2: Move the new EUL from a development environment to a production environment

When you and the end user representatives are satisfied with the EUL, you are ready to move the EUL from the development environment to the production database.

The first time you move the EUL from the development environment to the production database, we recommend you use the database export/import tools. Take a database export of the EUL schema from the development database and import the EUL schema into the production database

When you have imported the EUL to the production database, run the eul5_id.sql script to give the new EUL a unique reference number. For more information about how to run the eul5_id.sql script, see "How to import an EUL using the standard database import utility".

Having run the eul5_id.sql script, you can grant the entire Discoverer end user community access to the EUL.

Step 3: Modify the EUL to meet new requirements

Having rolled out the EUL to the organization, typically you will start receiving requests to modify the EUL definitions.

If you want to make changes to the EUL, we recommend you make them in the development environment and then use Discoverer's export and import tools to move the changes to the production environment.

Although the above is the recommended way to maintain the EUL, updates might have been made in the production environment that you do not want to lose. If this is the case, follow the steps below:

1. Use Discoverer's export and import tools to move the updates from the production environment to the development environment.

   Note: Changes made to the production environment from this point on will be lost. It is recommended therefore that the production environment be used in read-only mode during this time.

2. Make the necessary changes in the development environment.

3. Use Discoverer's export and import tools to move the changes back to the production environment.

4.22.4 Using the Discoverer workbook dump utility

Discoverer Administrator includes a workbook dump utility (d51wkdmp.exe) that enables you to inspect the EUL elements used by a workbook without having to open the workbook in Discoverer Desktop. This utility is particularly useful if you are unable to open a workbook (for example, because of missing joins).

To analyze a workbook, run the d51wkdmp.exe file from a command prompt window. The d51wkdmp.exe file is located in the %ORACLE_HOME%\bin directory.

The syntax for using the workbook dump utility is:

d51wkdmp <Workbook_Name> <Output_File> <DB|FS> <Connect_String> <Eul_Schema> -f

The parameters are described in more detail in the table below:

Parameter	Mandatory or Optional	Description
<Workbook_Name>	Mandatory	Name of the workbook to be examined (including the path, if the workbook is stored in the file system).
<Output_File>	Mandatory	The file to write the output to.
<DB|FS>	Mandatory	DB = opens the workbook from the database FS = opens the workbook from the file system
<Connect_String>	Mandatory	A username, password and database SID combination (for example, user/passwd@database)
<Eul_Schema>	Mandatory	The name of the database user that owns the EUL tables.
-f	Optional	Use this parameter to output further details about EUL objects that the workbook uses, including whether the elements can be found in the specified EUL.

Note the following:

• If a connect string or workbook name includes spaces, enclose the connect string or workbook name in double quotation marks.

• Specify the parameters in the order shown above.

• You can only analyze a workbook stored in the database if the workbook is owned by the username you specify in the connect string.

• The case of the eul_schema parameter must be correct. In other words, if the EUL is owned by user SDC, you must enter "SDC". If you enter "sdc", the following error message is displayed:

  "Connect Error - EUL schema is not accessible. Default or specified schema containing EUL tables is inaccessible"

The examples below use the Video Tutorial Workbook that is shipped as part of the Discoverer tutorial to illustrate the output of the workbook dump utility.

Example 1: The following command outputs the content of the Video Tutorial Workbook to the video.txt file. In this example, the -f option is not used.

d51wkdmp "Video Tutorial Workbook" video.txt DB disco/[e-mail] disco

The content of the video.txt file is as follows:

////////////////////////////////////////////////////////////////////////////// 
Sheet Number 1 
/////////////////////////////////////////////////////////////////////////////// 
 Sheet Name = Tabular Layout 
 Sheet Unique Name = {8690F66A-B9C8-11D1-ADB2-0080C7CDEA89} 
 Query(s) used =  
 Query 1 
  Items :- 
  EUL Item - Video Analysis Information.Calendar Year 
  EUL Item - Video Analysis Information.Department 
  EUL Item - Video Analysis Information.Region 
  Calculation - Profit SUM 
  Sort On   EUL Item - Video Analysis Information.Region 
  Filters :- 
  EUL Filter - Video Analysis Information.Department is Video Rental or Video
 Sale 
/////////////////////////////////////////////////////////////////////////////// 
/////////////////////////////////////////////////////////////////////////////// 
Sheet Number 2 
/////////////////////////////////////////////////////////////////////////////// 
 Sheet Name = Crosstab Layout 
 Sheet Unique Name = {8690F66B-B9C8-11D1-ADB2-0080C7CDEA89} 
 Query(s) used =  
 Query 2 
  Items :- 
  EUL Item - Video Analysis Information.Calendar Year 
  EUL Item - Video Analysis Information.Department 
  EUL Item - Video Analysis Information.Region 
  Calculation - Profit SUM 
  Filters :- 
  EUL Filter - Video Analysis Information.Department is Video Rental or Video
 Sale 
///////////////////////////////////////////////////////////////////////////////

Example 2: The following command outputs the content of the Video Tutorial Workbook to the video.txt file. In this example, the -f option is used.

d51wkdmp "Video Tutorial Workbook" video.txt DB disco/[e-mail] disco -f

The content of the video.txt file is as follows:

EUL Item Reference 
   IoId = 16 
   Id = 100177 
   Identifier = REGION 
   Name = Region 
   Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION 
   Folder Name = Video Analysis Information 
   *** Found in EUL by Identifier *** 
  EUL Item Reference 
   IoId = 24 
   Id = 100153 
   Identifier = DEPARTMENT 
   Name = Department 
   Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION 
   Folder Name = Video Analysis Information 
   *** Found in EUL by Identifier *** 
  EUL Item Reference 
   IoId = 32 
   Id = 100175 
   Identifier = PROFIT 
   Name = Profit 
   Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION 
   Folder Name = Video Analysis Information 
   *** Found in EUL by Identifier *** 
  EUL Private Item 
   Id = -105 
   Name = Profit SUM 
   Identifier = 1 
   Desc = Total profit for one store, one product in one day 
   DataType = 2 
   Placement = 1 
   Hidden = 0 
   IsACalc = 0 
   IOFormula = [1,1]([6,32])    DisplayFormula = SUM(Profit) 
  EUL Item Reference 
   IoId = 41 
   Id = 100139 
   Identifier = CALENDAR_YEAR 
   Name = Calendar Year 
   Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION 
   Folder Name = Video Analysis Information 
   *** Found in EUL by Identifier *** 
  EUL Filter Reference 
   Id = 100218 
   Identifier = DEPARTMENT_IS_VIDEO_RENTAL_OR_VIDEO_SALE 
   Name = Department is Video Rental or Video Sale 
   Folder Identifier = DC_VIDEO_ANALYSIS_INFORMATION 
   Folder Name = Video Analysis Information 
   *** Found in EUL by Identifier *** 
  EUL Sort Item Reference 
   Item =   EUL Item - Video Analysis Information.Region 
   Identifier = 14 
   Direction = 1 
  Query Request QR1 
   Distinct = 1 
   Axis Item Usage -   Name =   EUL Item - Video Analysis Information.Calendar
 Year 
   Axis Item Usage -   Name =   EUL Item - Video Analysis Information.Department
 
   Axis Item Usage -   Name =   EUL Item - Video Analysis Information.Region 
   Measure Item Usage -  Name =   Calculation - Profit SUM 
   Sort Item Usage -  Name =   Sort On   EUL Item - Video Analysis
 Information.Region 
   Filter Usage -  Name =   EUL Filter - Video Analysis Information.Department
 is Video Rental or  Video Sale 
   Identifier = 58 
 ////////////////////////////////////////////////////////////////////////////// 
 Sheet Number 1 
 ////////////////////////////////////////////////////////////////////////////// 
  Sheet Name = Tabular Layout 
  Sheet Unique Name = {8690F66A-B9C8-11D1-ADB2-0080C7CDEA89} 
  Sheet Identifier = 7 
  Query(s) used =  
  Query 1 
   Items :- 
   EUL Item - Video Analysis Information.Calendar Year 
   EUL Item - Video Analysis Information.Department 
   EUL Item - Video Analysis Information.Region 
   Calculation - Profit SUM 
   Sort On   EUL Item - Video Analysis Information.Region 
   Filters :- 
   EUL Filter - Video Analysis Information.Department is Video Rental or Video
 Sale 
 ////////////////////////////////////////////////////////////////////////////// 
  Query Request QR2 
   Distinct = 1 
   Axis Item Usage -   Name =  EUL Item - Video Analysis Information.Calendar
 Year 
   Axis Item Usage -   Name =  EUL Item - Video Analysis Information.Department 
   Axis Item Usage -   Name =   EUL Item - Video Analysis Information.Region 
   Measure Item Usage -  Name =   Calculation - Profit SUM 
   Filter Usage -  Name =  EUL Filter - Video Analysis Information.Department is
 Video Rental or
 Video Sale 
   Identifier = 95 
 ////////////////////////////////////////////////////////////////////////////// 
 Sheet Number 2 
 ////////////////////////////////////////////////////////////////////////////// 
  Sheet Name = Crosstab Layout 
  Sheet Unique Name = {8690F66B-B9C8-11D1-ADB2-0080C7CDEA89} 
  Sheet Identifier = 12 
  Query(s) used =  
  Query 2 
   Items :- 
   EUL Item - Video Analysis Information.Calendar Year 
   EUL Item - Video Analysis Information.Department 
   EUL Item - Video Analysis Information.Region 
   Calculation - Profit SUM 
   Filters :- 
   EUL Filter - Video Analysis Information.Department is Video Rental or Video
 Sale 
 ///////////////////////////////////////////////////////////////////////////////

4.22.5 About creating EULs in Logical and Physical Standby (or read-only) databases

To improve performance, Discoverer stores statistics about end user queries in the EUL. For this reason, Discoverer requires that the database containing the EUL is writeable. Specifically, Discoverer does not support the creation of EULs in Logical and Physical Standby (or read-only) databases. To prevent Discoverer end users having write access to:

• business data, store the data in a read-only database and use database links to access the data from the writeable database that contains the EUL

• the EUL itself, do not grant the Create/Edit Workbook privilege to any users, and only grant the Administration privilege to the EUL owner