Oracle9i Discoverer Administrator Administration Guide Version 9.0.2 Part Number A90881-02 |
|
This chapter explains how you create and maintain End User Layers using Discoverer Administrator, and contains the following topics:
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. 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 in order to use Discoverer. Access is granted using the Privileges dialog, described in Chapter 7, "Controlling access to information".
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.
The EUL contains the metadata that defines one or more business areas. A business area is a conceptual grouping of tables and/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.
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.
You can export EUL objects (e.g. 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:
Note: 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).
An End User Layer 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 a 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:
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:
For more information, see Chapter 7, "Controlling access to information".
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:
The database user must also have the following specified:
For more information about the default tablespace, see "Create EUL Wizard: Step 2 dialog".
If you are running Discoverer against an Oracle 8.1.6 and later 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:
The EUL Manager dialog enables you to:
If you create an EUL for a new Oracle database user, Discoverer grants the necessary privileges and sets the default tablespace and quota.
To create an End User Layer for a non-Oracle database user, the database user must have the following database privileges:
Discoverer Administrator does not enable you to create new users for non-Oracle databases.
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 Chapter 7, "How to specify a user or role (responsibility) to perform a specific task".
The EUL Gateway provides a way for Discoverer to populate an EUL with metadata from another source, such as Oracle Designer. 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>\discv902\kits directory.
For information about loading a business area from the EUL Gateway see Chapter 4, "How to create a business area using the Load Wizard".
To create an EUL for an existing database user:
Discoverer Administrator displays the Commit Status 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 tutorial in the new EUL. For more information, see the Oracle9i Discoverer Administrator Tutorial.
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 need to create an End User Layer in an Oracle database?".
To create an EUL in a new database user:
If the Create a new user radio button is unavailable, contact your database administrator to grant the CREATE USER privilege.
If you are unsure which to choose, see your database administrator. For more information, see the "Create EUL Wizard: Step 2 dialog".
Discoverer Administrator displays the Commit Status 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 tutorial in the new EUL. For more information, see the Oracle9i Discoverer Administrator Tutorial.
You might want to delete an EUL for a number of 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:
Discoverer prompts you to confirm that you want to delete the current EUL.
If the EUL name displayed is incorrect, go back to Step 2 and use the correct connect string to connect as the EUL owner.
When you click OK, Discoverer prompts you to confirm that you want to delete all EUL tables, all EUL information and workbooks in the database and all summary data and information.
Discoverer deletes the EUL objects for the current database user.
You can view or change which EUL is the default EUL for the current database user (i.e. 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:
Typically, you will want to:
There are a number of different ways to copy EULs and EUL objects, depending on whether you want to:
In all these cases, you perform an export operation followed by an import operation. For more information, see "Which export/import method do you 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:
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
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.
Refer to the following topics to find out how to export or import EUL objects using the Discoverer user interface:
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".
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 inteface, see Chapter 21, "What is the Discoverer command line interface?".
Refer to the following topics to find out how to export and import EUL objects using the Discoverer command line interface:
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 import an EUL using the standard database import utility".
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 EUL objects between databases:
How you export or import an EUL depends on:
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 (e.g. if the EUL is on an Oracle8i database and Oracle9i 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.
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 Export Wizard:
You use the above dialog to specify a name and location for the export file.
This enables you to browse for a location to save the EUL export file.
This enables you to view workbook definitions in an XML browser.
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.
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 in conjunction with the command line interface (for further information about the command line interface, see Chapter 21, "Discoverer command line interface").
The Export Log displays information about what has been exported.
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:
You can select more than one business area at a time by holding down the Ctrl key and clicking another business area.
This enables you to browse for a location to save the business area export 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 in conjunction with the command line interface (for further information about the command line interface, see Chapter 21, "Discoverer command line interface").
The Export Log displays information about what has been exported.
Note: If you are copying business areas between EULs, continue with step 2 of the following task, see Chapter 4, "How to copy business areas between EULs".
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:
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 need to select them explicitly.
You can select more than one object at a time by holding down the Ctrl key and clicking another object.
This enables you to browse for a location to save the export file.
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.
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 in conjunction with the command line interface (for further information about the command line interface, see Chapter 21, "Discoverer command line interface").
The Export Log displays information about what has been exported.
You can 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. This is the first of two tasks that enable you to copy EUL objects from one database to another. You must complete this export task before you can import the EUL objects into another database.
To export EUL objects from a database using the Discoverer command line interface:
For example, to export the Video Store Tutorial business area and two workbooks ("Vistr4 - Video Tutorial Workbook" and "Vidaf4 - Analytic Function Examples") to the Vidstr.eex file, you might type the following:
D:\orant\Discv902\Dis5adm.exe /connect eulowner/eulowner@orcl.world /export "D:\Vidstr.eex" "Video Store Tutorial" /workbook "Vistr4 - Video Tutorial Workbook" /workbook "Vidaf4 - Analytic Function Examples"
For more information about exporting EUL objects using the Discoverer command line interface, see Chapter 21, "/export (EUL objects)".
To copy the EUL objects to another database, see "How to import End User Layer objects to a database (after export from another database) using the Discoverer command line interface".
You can import EUL objects 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 (to import to another database) using the Discoverer command line interface"). This is the second of two tasks enabling you to copy EUL objects from one database to another.
To import End User Layer objects into a database using the Discoverer command line interface:
For example, to import the Vidstr.eex file, created above you might type the following:
D:\orant\Discv902\Dis5adm.exe /connect eulowner/eulowner@orcl.world /import "D:\Vidstr.eex"
For more information about importing EUL objects using the Discoverer command line interface, see Chapter 21, "/import (EUL objects)".
This moves all business areas, EUL tables and saved workbooks to a .dmp file.
To export an EUL using the standard database export utility (assuming the EUL resides on an Oracle9i database and you are using a machine on which you have installed Oracle9iDS):
For example, if the <ORACLE_HOME> directory is D:\ORACLE9i and the MS-DOS window currently displays C: , you would:
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 <ORACLE_HOME>\discv902 directory.
When the export is complete, the following message is displayed:
Export terminated successfully without warnings.
Having backed up the EUL, you are now ready to import it into a new database user.
This imports 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 Oracle9i database and you are using a machine on which you have installed Oracle9iDS):
For example, if the <ORACLE_HOME> directory is D:\ORACLE9i and the MS-DOS window currently displays C: , you would:
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 to the specified .dmp file in the <ORACLE_HOME>\discv902 directory.
When the import is complete, the following message is displayed:
Import terminated successfully without warnings.
You use the Import Wizard to import EUL objects (e.g. business areas, folders, hierarchies, calculations) from one EUL in order to re-use 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:
Selected files appear in the Import File list.
This dialog enables you to specify how Discoverer Administrator processes conceptually identical objects from another EUL. For more information about conceptually identical objects, see "What are identifiers?".
An object means any EUL object, for example, folder, item, calculated item.
This dialog enables you to start the import and monitor its status as each EUL object is processed.
This option might be necessary if there are warning messages in the Import Log.
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 (known as a conceptually identical object) in the EUL you are importing into. This enables 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.
Note: To locate the identifier of a workbook/worksheet in Discoverer Plus, see the Oracle9iAS Discoverer Plus User's Guide.
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:
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 (e.g. 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:
STORAGE (
INITIAL 40960
NEXT 1024000
PCTINCREASE 0
)
STORAGE (
INITIAL 40960
NEXT 81920
PCTINCREASE 0
)
The database index storage parameters for EUL indexes are as follows:
There are two factors that might cause the EUL to use up more space:
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.
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.
When you migrate EUL data using Discoverer Administrator's import and export facilities, you need to be aware of the following restrictions that relate to analytic functions (for more information about analytic functions, see the Oracle 9iAS Discoverer Plus User's Guide):
Note that there are also issues to be aware of when upgrading EULs that contain analytic functions from one Discoverer release to another (e.g. when upgrading from Discoverer Release 4.1 to Version 9.0.2). For more information, see Chapter 24, "Notes about upgrading EULs that contain analytic functions".
|
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|