Oracle9i Discoverer
End User LayerTM Gateway
Introduction
The End User LayerT Gateway is a mechanism to transfer information from external metadata repositories to the Discoverer End User Layer. This document contains information that enables users to build, install and use an End User Layer gateway.
End User Layer Gateway Architecture
An End User Layer Gateway (EUL Gateway) consists of a number of database tables or views built to a specification. These objects reside as a layer between an external metadata repository and the Discoverer metadata repository(End User Layer). The EUL Gateway functions as an intermediate store of metadata from which Discoverer imports information. Discoverer uses the metadata stored in the gateway objects to create a Business Area in its End User Layer.
If the gateway is implemented as a set of tables, the tables will need to be populated by a program that copies metadata from the external repository to the gateway tables. Alternately, if the gateway is implemented as a set of views, the views will map directly to the external metadata repository tables. Implementing the EUL Gateway as a set of views is more efficient. There is no extra code needed to move metadata from one set of tables to another. The views also reflect changes to the underlying metadata instantaneously.
Constructing an EUL Gateway
There are 3 scripts shipped as part of Discoverer that can be used as templates to construct an EUL Gateway. These scripts are eulgwtbl.sql, eulgwvue.sql and eulgwreg.sql. The first script contains S 424c25e QL commands to create the gateway as a set of tables. The second script contains SQL commands to create the gateway as a set of views. The third script contains sample gateway registration code. Together, these scripts provide a starting point to build an EUL Gateway. Along with the information in this document, users can rapidly construct a prototype gateway with basic functionality. This gateway can then be modified to support additional features.
The following sequence of steps can be used as a guideline to build a gateway.
Identify the metadata to be transferred.
Decide if the EUL Gateway is to be implemented as views or tables.
If tables are to be used, write scripts to
Create the tables
Populate the tables
If views are to be used, write a script to create the views.
Execute the above scripts.
Register the gateway.
Use Discoverer Administrator to create a business area from the gateway.
Step 1 Identify metadata to be transferred
The EUL Gateway supports the transfer of several metadata components. As the first step, determine what metadata components exist in the source metadata repository. Then, decide what components you want to transfer through the gateway. The following is a list of metadata components that the EUL Gateway can handle:
Objects - Tables, Views
Complex Objects
Object Columns
Derived Columns
Object and Column Descriptions
Filters
Joins
Complex Joins
Externally Managed Summaries
Step 2 Decide if the EUL Gateway is to be implemented as views or tables.
This decision is to be made based on how easy it is to map the gateway views on top of the source metadata repository. Building the gateway as a set of views is the preferred approach since it reduces the code that needs to be written to support the gateway. With this approach, the views are basically created as a 'wrapper' around the metadata to be transferred. Any changes in the underlying metadata are immediately reflected in the views and hence in the gateway.
In some cases, the structure of the source metadata repository makes it difficult to take the view approach. An intermediate step might be needed before the metadata can be formatted to suit the specification of the gateway. In this case the gateway has to be built as a set of tables. The tables need to be populated with information from the source metadata repository using a script or program. This approach has advantages in the form of more programmatic control over what the gateway contains.
For each metadata component identified in Step 1, identify how information about this component is stored in the source metadata. Then determine if the corresponding gateway view can be defined as a 'select' statement based on the source metadata tables. If all the intended components can be represented as views conforming to the gateway specifications, then the gateway can be implemented as a set of views. If not, then the gateway has to be implemented as a set of tables.
The table below contains mappings of some of the key metadata components and how they map to corresponding gateway objects(tables or views).
NOTE: This table is not a comprehensive list of metadata components and/or gateway objects. A comprehensive list is available in the EUL Gateway Specification document (egwspec.doc). The purpose of this table is to highlight key relationships between metadata to be transferred and the gateway objects.
|
Metadata Component |
Attribute |
Gateway Object |
Column |
|
Table |
EUL_GW_OBJECTS | ||
|
Name |
O_OBJ_NAME |
||
|
Description |
O_DESCRIPTION |
||
|
Owner(Schema) |
|
O_S_SCHEM_NAME |
|
|
Type[1] |
O_OBJ_TYPE |
||
|
Column |
EUL_GW_COLUMNS | ||
|
Name |
C_COL_NAME |
||
|
Datatype |
C_DATATYPE |
||
|
Description |
C_DESCRIPTION |
||
|
Formula[2] |
C_SQL_DERIVATION |
||
|
Filters |
EUL_GW_FILTERS | ||
|
Object |
F_O_OBJ_NAME |
||
|
Description |
F_DESCRIPTION |
||
|
Expression[3] |
F_SQL |
||
|
Unique Key |
EUL_GW_UKS | ||
|
Name |
UK_CONSTR_NAME |
||
|
Object |
UK_O_OBJ_NAME |
||
|
Unique Key Column |
EUL_GW_UK_COLS | ||
|
Key Name |
UKC_UK_CONSTR_NAME |
||
|
Column Name |
UKC_C_COL_NAME |
||
|
Foreign Key |
EUL_GW_FKS | ||
|
Name |
FK_CONSTR_NAME |
||
|
Object |
FK_O_OBJ_NAME |
||
|
Master Object |
FK_REM_OBJ_NAME |
||
|
SQL Predicate[4] |
FK_SQL_PREDICATE |
||
|
Foreign Key Column |
EUL_GW_FK_COLS | ||
|
Key Name |
FKC_FK_CONSTR_NAME |
||
|
Column Name |
FKC_C_COL_NAME |
Step 3 Develop Scripts
View based approach
Use the eulgwvue.sql script as a starting point. For each of the CREATE VIEW statements, modify the statement to appropriately map the view to the source metadata view or table. Execute the modified script in the database where the source metadata exists. The gateway views are now created.
Table based approach
Execute the script - eulgwtbl.sql in the database where the source metadata exists. The gateway tables are now created. Write a script to insert data into these tables from the source metadata tables. The script should copy data relevant to each of the metadata components identified in Step 1.
Step 4 Register the gateway
Each gateway needs to be registered with the Discoverer End User Layer before the gateway can be used. The registration process consists of inserting one row into a specified table - 'eul_gateways' in the Discoverer End User Layer. This process serves to let Discoverer know that a particular gateway is available. The registration process specifies the name of the gateway and this is the name displayed in the Discoverer Administrator Load Wizard.
The script - eulgwreg.sql has a sample insert statement. Modify this script to reflect your specific gateway as follows:
Change the name of the gateway
Change the owner of the gateway objects
Change the gateway product name
Change the gateway description
Change the owner of the eul_gateways table in the 'insert into' part of the statement
Execute the modified script to complete the registration process. Discoverer has to be installed before you can register the gateway.
Step 5 Use Discoverer Administrator to create a business area from the gateway
At this point, the gateway has been created and is ready to be used. Start Discoverer Administrator and invoke the Load Wizard to create a new business area. In the first window of the wizard, select the 'Gateways' option. In the drop list, you will see the names of all the gateways available. Select the gateway you created by following the steps above. Click the Next button. The wizard then guides you through the rest of the process - the steps are the same ones you go through when creating a business area from the online dictionary. The only difference is that the schema names and the object names displayed are from the gateway instead of the dictionary.
Summary
The EUL Gateway is a powerful feature that can be used in situations where external metadata, either previously existing metadata or metadata that is generated, can be used in Discoverer to create Business Areas for ad hoc querying, analysis and reporting. This approach outlined in this document functions as a quick start guide to build, install and use an EUL Gateway.
|
