H
Warehouse Builder Bridges: Transfer Parameters and Considerations

Transfer Parameters

The Warehouse Builder Transfer Wizard allows you to export metadata from data warehousing tools and file systems, such as Object Management Group Common Warehouse Metamodel, Computer Associates ERwin, Powersoft PowerDesigner, and Oracle9i OLAP Server and import the metadata into Oracle warehousing tools, such as Oracle Discoverer, Oracle Express, and Oracle9i OLAP Server.

When you use the Warehouse Builder Transfer Wizard, you are required to enter transfer parameter information based upon the metadata source or target you have selected. The following tables list details about the transfer parameters you need to provide for each data source or target type.

Table H-1 Transfer Parameters for OMG CWM FIle Import  

Transfer Parameter Name	Description
OMG CWM Input File	Name of the file where you want to store the imported metadata in the Warehouse Builder repository. Click "..." to browse for the file location.
Warehouse Builder Project	Warehouse Builder name for the imported project--this project will be located under the Projects node within Warehouse Builder.
Name Matching Mode	Name matching mode you want to utilize during the import; physical name mode is the default.
Import Mode	Import mode: create, replace, update, incremental update.
Log Level	Log level to be assigned to the transfer: Errors, Information, and Trace. Error: list the errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support. Trace type provides the most detailed log information.

Table H-2 Transfer Parameters for Importing from CA ERwin  

Transfer Parameter Name	Description
Input Model File	Location of the ERwin Input file. Click "..." to browse for the file location.
Warehouse Builder Project	Warehouse Builder name for the imported project--this project will be located under the Projects node within Warehouse Builder.
Warehouse Builder Model Name	Name of the .erx file you are importing from ERwin.
Name Matching Mode	Name matching mode you want to utilize during the import. Physical name mode is the default.
Import Mode	Import mode: create, replace, update, incremental update.
Log Level	Log level to be assigned to the transfer: Errors, Information, and Trace. Error: list the errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support. Trace type provides the most detailed log information.

Table H-3 Transfer Parameters for importing from Powersoft PowerDesigner  

Transfer Parameter Name	Description
Input Model File	Location of the PowerDesigner Input file. Click "..." to browse for the file location.
Warehouse Builder Project	Warehouse Builder name for the newly imported Project. This Project will be located under the Projects node within Warehouse Builder.
Warehouse Builder Model Name	Name of the model you are importing from ERwin into Warehouse Builder.
CDM Name Mapping	Choose whether you want to use the logical (NAME) name or physical (CODE) name for the Conceptual Data Model (CDM). Default is NAME.
PDM Name Mapping	Choose whether you want to use the logical (NAME) name or physical (CODE) name for the Physical Data Model (CDM). Default is CODE.
Submodel Mapping	Name of the submodel you want to import into Warehouse Builder. The default choice (*) imports all the sub models within the model
Name Matching Mode	Name matching mode you want to utilize during the import. Physical name mode is the default.
Import Mode	Import mode: create, replace, update, incremental update.
Log Level	Log level to be assigned to the transfer: Errors, Information, and Trace. Error: list the errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support. Trace type provides the most detailed log information.

Table H-4 Transfer Parameters when OMG CWM is selected as the target  

Transfer Parameter Name	Description
Warehouse Builder Exported Business Areas	Select the Business Areas you want to export from Warehouse Builder. The default is All Business Areas; you can select individual Business Areas from the drop-down list.
OMG CWM Output File	The path and filename for the OMG CWM output file.
Log Level	Enter the log level to be assigned to the transfer: Errors, Information, Trace. Errors: produces just a list of errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support. .

Table H-5 Transfer Parameters when Discoverer is selected as target  

Transfer Parameter Name	Parameter Description
Warehouse Builder Exported Business Areas	Select the Business Areas you want to export out of Builder from the drop-down menu; the default is All Business Areas.
Discoverer EUL Owner	Name of the owner of the Discoverer EUL.
Discoverer Schema Owner	Name of the owner of the Discoverer schema.
Dimensional Reuse	True or False option for dimensional reuse (defined as two or more foreign key constraints in a fact that point to the same dimension). Select True if you changed the logical names of foreign key columns and you want them to appear as separate folders in Discoverer.
Discoverer Output File	Path and name of the Discoverer .EEX file (which will contain the metadata) that is generated by the Transfer Wizard to be imported into Discoverer. Enter the path or select it by clicking Browse in the Transfer Parameter Value column of the Discoverer Output File parameter. Create a name of your choosing for the .EEX file and enter it at the end of the path.
Log Level	The log displays data about successful and unsuccessful transfers. The Transfer Wizard provides two additional log levels, Errors and Trace, to assist you in debugging. Errors: produces just a list of errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support.

Table H-6 Transfer Parameters when Express is selected as target  

Transfer Parameter Name	Parameter Description
Warehouse Builder Exported Business Areas	From the drop-down list, select the business areas you want to export out of Warehouse Builder. The default is All Business Areas.
Express User	User id for connecting to the Express Repository.
Express User Password	Password for the Express User.
Express Connect String	Connecting odbc string for Express, using the following syntax: host_machine_name:port_number:database_sid
Express Table Owner	Name of the Express Table Owner.
RAA Version Number	Relational Access Administrator version.
Log Level	The log displays data about successful and unsuccessful transfers. The Transfer Wizard provides two additional log levels, Errors and Trace, to assist you in debugging. Errors: produces just a list of errors, if any, generated by the transfer. Information: lists the transfer details about the metadata objects, including any errors. Trace: produces a trace for debugging purposes, which also includes the errors and information log details. If you need assistance with the trace, contact Oracle World Wide Support.

Table H-7 Transfer Parameters for Importing from Oracle 9i OLAP server  

Transfer Parameter Name	Parameter Description
User Name	User name for connecting to the Oracle9i OLAP server.
Password	Password for connecting to the Oracle9i OLAP server.
Host Name	The host name for the Oracle9i OLAP server.
Port	The port number for the Oracle9i OLAP server.
SID	The database SID for the Oracle9i OLAP server.
Measure Folder	The measure folder to import the OLAP definitions from. The measure folder will be used to drive the information imported. So for a measure, the associated fact and dimensions will be imported.
Project	Name for the imported project.
Default module type for relational schemas.	Select Warehouse module, since you will import OLAP definitions.
Process OLAP Physical Representation	True or False. Select whether to read the physical mapping of the fact to table, and dimension to table. There are certain structures which Warehouse Builder does not currently support, such as snowflake dimension schemas, or facts based on views, so utilizing the physical mapping is not always possible. If it is not possible or False is selected, the import will generate a default physical representation.
Name matching mode	<your standard description goes here>
Import mode	<your standard description goes here>
Log Level	<your standard description goes here>

Table H-8 Transfer Parameters when Oracle 9i OLAP server is selected as target  

Transfer Parameter Name	Parameter Description
Warehouse Builder Exported Business Areas	<your standard description goes here>
User name	The user name for the Oracle9i OLAP server.
Password	The password for the Oracle9i OLAP server.
Host name	The host name for the Oracle9i OLAP server.
Port	The port number for the Oracle9i OLAP server.
SID	The database SID for the Oracle9i OLAP server.
PL/SQL Output File	File name for the generated PL/SQL file. The PL/SQL file has one parameter which must be passed when executed, the parameter is the schema in which you are creating the OLAP objects into. So when executing the file 'sales_history.sql' into the schema 'SH', you can execute as 'sqlplus OLAPDBA/<passwd>@sales_history.sql SH'
Deploy PL/SQL in Database	Yes or No. Execute the generated PL/SQL in the database, or save the PL/SQL for scheduled execution.
Log Level	<your standard description goes here>

Transfer Considerations

Before you transfer metadata into the Warehouse Builder repository or out of the Warehouse Builder repository, you need to perform tasks within the source and target tools to ensure that the metadata can be transferred successfully.

This appendix provides instructions for preparing to transfer metadata from a source tool to the Warehouse Builder repository or from the Warehouse Builder repository to a target tool. It also lists the objects that are extracted from the source tool during the transfer and their corresponding Warehouse Builder objects.

The Warehouse Builder Transfer Wizard exports metadata from the Warehouse Builder repository as business areas. Before you export metadata, you must create business areas within the Warehouse Builder repository. See Chapter 4, "Defining Dimensional Targets" for instructions.

This section includes the following topics:

• Importing Metadata from an Object Management Group Common Warehouse Metamodel Standard System

• Importing Metadata from Computer Associates ERwin 3.5.1

• Importing Metadata from Powersoft PowerDesigner 6.0

• Importing Metadata from Oracle9i OLAP server

• Exporting Metadata to Oracle Discoverer 3.1 and 4i

• Exporting Metadata to an Object Management Group CWM Standard System

• Exporting Metadata to Oracle Express

• Exporting Metadata to Oracle9i OLAP server

Importing Metadata from an Object Management Group Common Warehouse Metamodel Standard System

Table H-9 lists the object conversion fom an OMG CWM file system to the Warehouse Builder repository.

Table H-9 Object Conversion for Import from OMG CWM into Warehouse Builder  

Object in OMG CWM	Imported into Warehouse Builder
Package	Project
Schema	Module
Table	Table
Column	Columns
Foreign Key	Foreign Keys
Unique Constraint/Primary Key	Unique Keys
View	View
Column	Column

Importing Metadata from Computer Associates ERwin 3.5.1

While importing a file from ERwin into the Warehouse Builder repository, save the file containing the metadata for transfer as an .ERX file.

Table H-10 lists the object conversion from ERwin to the Warehouse Builder repository.

Table H-10 Object Conversion for Import from ERwin into Warehouse Builder  

Object in ERwin	Imported into Warehouse Builder
Table	Table
Column	Columns
Foreign Key	Foreign Keys
Primary Key	Unique Keys
View	View
Column	Columns

Importing Metadata from Powersoft PowerDesigner 6.0

While importing a file from PowerDesigner into the Warehouse Builder repository, save the file containing the metadata for transfer as a .PDM file.

Table H-11 lists the object conversion fom PowerDesigner to the Warehouse Builder repository.

Table H-11 Object Conversion for Import from PowerDesigner into Warehouse Builder  

Object in PowerDesigner	Imported into Warehouse Builder
Table	Table
Column	Columns
Index	Foreign Keys
Index	Unique Keys
View	View
Column	Columns

Importing Metadata from Oracle9i OLAP server

Table H-12 lists the object conversion fom Oracle9i OLAP server to the Warehouse Builder repository.

Table H-12 Object Conversion for Import from Oracle9i OLAP server into Warehouse Builder

OLAP Object in Oracle9i	Imported into Warehouse Builder
Schema	Module
Cube	Fact
Measure	Measure
Dimension	Dimension
Level	Level
Hierarchy	Hierarchy
Attribute	LevelAttribute
MeasureFolder	BusinessArea

There are certain structures that are not supported in Warehouse Builder that are possible in the Oracle9i OLAP server. For example, snowflake modelling and facts based upon views are not supported. If you have OLAP definitions which use these structures, the logical definition of the objects can be imported into Warehouse Builder, but not the physical representation.

Exporting Metadata to Oracle Discoverer 3.1 and 4i

When you export a file from the Warehouse Builder repository to a Discoverer target using the Warehouse Builder Transfer Wizard, you can refer to the following terminology matrix to check how objects in Warehouse Builder repository are mapped in Discoverer. Table H-13 lists the object conversion fom Warehouse Builder repository to Discoverer.

Table H-13 Object Conversion from Warehouse Builder to Discoverer  

Object in Warehouse Builder	Object Exported to Discoverer
Dimension	Folder
Level	Hierarchy Node
Level Attributes	Item
Hierarchies	Hierarchy
Fact	Folder
Fact Attributes	Item
Table	Folder
Columns	Item
Unique Keys	Key
View	Folder (View)
Columns	Item
Attributes flagged as Item Classes	Item Classes
Business Tree	Business Area

Before you transfer metadata to Discoverer, you need to perform some additional tasks within Warehouse Builder to ensure that the metadata transfers successfully and displays appropriately in Discoverer. These tasks include:

• Creating business areas in a Warehouse Builder warehouse module so that the transferred metadata displays as business areas within Discoverer.

• Hiding some Warehouse Builder table columns so that they are grayed in Discover Administration Edition and unavailable in Discoverer User Edition.

• Specifying attributes to use as Item Classes in Discoverer.

• Using names to indicate which attributes to place in the hierarchy nodes within Discoverer.

The following sections provide instructions for performing these tasks.

Configuring Warehouse Builder for Dimensional Reuse

Within Warehouse Builder, a fact can contain two or more foreign key constraints pointing to a single dimension, such as ordered date and shipped date for a calendar dimension. This is referred to as dimensional reuse. Within Discoverer, each of these constraints needs its own folder so the dimension roles display properly. Dimension roles are required in Discoverer to facilitate query building. In the calendar example, one folder should display for ordered date and another one for shipped date.

Note: Follow these steps only if you are going to set the Dimensional Reuse parameter value to TRUE in the Warehouse Builder Transfer Wizard.

To enable dimensional roles to display correctly in Discoverer, you need to create dummy tables in Warehouse Builder. This dummy table then becomes the dimension role and the columns in the table become the columns that the cube dimensions use.

The recommended naming convention for the dummy table is <dimension reference>##<role reference>, where the dimension and role references help you remember what role on which dimension this table represents. The logical name of the table is the role name. To further identify the dummy table, mark it as Deployable false.

The actual dummy table to real dimension association relies on any one of the contained columns. The dummy column to fact association is determined by making the column's physical name the same as the physical name of the foreign key constraint in the fact.

The following example illustrates how dimensional roles can be set up for exporting to Discoverer:

Defining Dimensions and Facts in Warehouse Builder

1. Create dimensions-DAY, PRODUCT and OPERATOR-in the normal way.

2. Create facts-SALES and FLIGHTS-in the normal way.

3. Create the following constraints:
   • SALES to DAY (for ORDER_DATE) Constraint Physical Name is SALES_DAY_FK

   • SALES to DAY (for SHIP_DATE) Constraint Physical Name is SALES_DAY2_FK

   • SALES to PRODUCT (for PURCHASE) Constraint Physical Name is SALES_PROD_FK

   • FLIGHTS to DAY (for ORDER_DATE) Constraint Physical Name is FLIGHTS_DAY_FK

   • FLIGHTS to PRODUCT (for TICKET) Constraint Physical Name is FLIGHTS_PROD_FK

   • FLIGHTS to OPERATOR Constraint Physical Name is FLIGHTS_OPERATOR_FK

Defining the Dummy Tables

Create the dummy tables as follows using the Table editor:

1. For the DAY (ORDER_DATE) role, define the table as follows:
   • Physical Name DAY##ORD (logical name ORDER_DATE)

   • Column Physical Name SALES_DAY_FK

   • Column Physical Name FLIGHTS_DAY_FK

2. For the DAY (SHIP_DATE) role, define the table as follows;
   • Physical Name DAY##SHIP (logical name SHIP_DATE)

   • Column Physical Name SALES_DAY2_FK

3. For the PRODUCT (PURCHASE) role, define the table as follows:
   • Physical Name PROD##PUR (logical name PURCHASE)

   • Column Physical Name SALES_PROD_FK

4. For the PRODUCT (TICKET) role, define the table as follows:
   • Physical Name PROD##TIC (logical name TICKET)

   • Column Physical Name FLIGHTS_PROD_FK

5. Right-click the dummy table, select Configure from the pop-up menu, and set the Deployable value to False.

Hiding Data Prior to Transfer

You can hide specific Warehouse Builder table columns (such as obsolete or unused columns) so they are grayed in the Discoverer Administration Edition and unavailable in the Discoverer User Edition. To hide these columns, you create a custom entity attribute set in Warehouse Builder.

To hide table columns in Warehouse Builder:

1. From the main Warehouse Builder navigator tree, double-click the warehouse target module in which you want to hide table columns prior to transfer to Discoverer. The Warehouse Module Editor displays.

2. Expand the Warehouse Module Editor navigation tree.

3. Right-click the fact or dimension table in which you want to hide columns and select Properties from the pop-up menu.

   The Properties window for that table displays.

4. Select the Attribute Sets tab on the Properties window.

5. Create a new Attribute Set for the export by clicking Add and setting the Attribute Type to be BRIDGE_TYPE.

6. Select the attributes to include within this set and click Advanced.

   The Advanced Attribute Set Properties dialog displays.

7. Choose the columns to hide in Discoverer by checking the corresponding box under the Hidden field.

8. You can also use this dialog to define Item Class and set default aggregation and default position of table columns within Discoverer.

Importing Transferred Data into Discoverer

After transferring the selected metadata using the Transfer Wizard, you import the .EEX file into Discoverer.

For detailed information on accessing the projects imported from Warehouse Builder in Discoverer 3.1 and 4i, refer to the Oracle Discoverer Administration Guide.

Dimensional Reuse Naming Conventions in Discoverer

When you transfer metadata from Warehouse Builder to Discoverer using the Transfer Wizard and you select TRUE for the Dimensional Reuse parameter, a flag is set so dimensional roles display correctly in Discoverer. (Refer to the section "Configuring Warehouse Builder for Dimensional Reuse").

Dimension roles are required to make query building easier in Discoverer. When two tables have multiple joins between them, Discoverer asks which join to use the first time the two folders representing the tables are referred to in a query, and then always uses that join in further parts of the query. If the query requires different joins at different times, the only way to support this is to provide another folder based on the same table. This multi-join scenario is common in dimensional models.

The support for dimension roles provides Discoverer with a separate folder for each role of the dimension. For example, if a DAY dimension is joined to the SALES fact, once for ORDER_DATE and once for SHIP_DATE, two dimension role folders are created in Discoverer. If the same role is required for a different fact, the original role is reused. For example, if a FLIGHTS fact was also using the ORDER_DATE role, it would reference the same folder as the one referenced by the SALES fact.

Dimensional reuse (two or more foreign key constraints pointing to the same dimension in Warehouse Builder) is denoted in the Discoverer navigation tree in two ways:

• Warehouse Builder dimension folder names may appear in the following format:

  DIMENSION LOGICAL NAME : RoleName
  

• When no role is defined, a default role is generated in Discoverer:

  DIMENSION LOGICAL NAME : Default
  
  

Examples (following from the example in "Configuring Warehouse Builder for Dimensional Reuse") of the appearance of the folders in Discoverer:

DAY (hidden)

DAY : ORDER_DATE

DAY : SHIP_DATE

FLIGHTS

OPERATOR (hidden)

OPERATOR : Default (see default role above)

PRODUCT (hidden)

PRODUCT : PURCHASE

PRODUCT : TICKET

SALES

Exporting Metadata to an Object Management Group CWM Standard System

When you export metadata from the Warehouse Builder repository to an OMG CWM standard system, the Warehouse Builder Transfer Wizard converts it to a file that conforms to the OMG CWM standard. Table H-14 lists the object conversion from the Warehouse Builder repository to the OMG CWM standard.

Table H-14 Object Conversion from Warehouse Builder to OMG CWM

Object in Warehouse Builder	Object Exported to OMG CWM
Project	Package
Module	Schema
Dimension	Dimension
Level	Level
Level Attributes	Attribute
Hierarchy	Hierarchy
Fact	Cube
Fact Attributes	Measure
Table	Base Table
Column	Column
Foreign Key	Foreign Key
Unique Key	Unique Constraint/Primary Key
View	View
Column	Column

Exporting Metadata to Oracle Express

After you export metadata from Warehouse Builder to Oracle Express, use the following steps to access the metadata within Express:

1. Open RAA.

2. Enter the user name and password for your RAA repository.

3. Open the project that you just transferred. After you run the bridge, the project will appear in the drop-down list.

4. Once the project is open, select Express Database Maintenance and enter the user name, password, and service name for your RAA repository.

5. Choose Create Maintenance Procedure. Provide the procedure and select general maintenance.

   The RDBMS login displays automatically.

6. Select Generate Qualified Selects at Runtime.

7. Select defaults for dimension processing.

8. Name your Express database and log file (these will be saved on the OES server machine under d:\orant\database\express_info).

9. Choose OES and then OES Batch Manager to monitor this job (Jobs -> Monitor).

10. You have now created your raw Express database.

OSA Configuration

Before you look at the data, follow these configuration steps:

1. Open the OSA Application Manager.

2. Select Database Setup and then Create.

   You can now create a .dsc file (give it a name which reflects the project that you transferred). After you enter a name, choose edit and select remote thin-client. For the configuration file, go to the directory where you saved the database and log file and choose the .rdc file that relates to the project that you just transferred.

3. Select Communication Setup, then Define, and then Create.

4. Name your setup and choose thin-client, remote system (this is the server machine). For RPC, choose TCP/IP.

5. You can now open OSA and choose New, and then Reports or Graphs to view your data. You'll need to first select the correct dimensions, hierarchies, and measures based on your Warehouse Builder repository.

Exporting Metadata to Oracle9i OLAP server

To deploy Oracle9i OLAP objects, you must first deploy warehouse objects from Warehouse Builder to the database server. To export additional OLAP metadata use a Metadata Export Bridge.

Table H-15 lists the object conversion from the Warehouse Builder repository objects to OLAP objects in Oracle9i.

Table H-15 Object Conversion for Export from Warehouse Builder into Oracle9i OLAP server

Object into Warehouse Builder	Exported OLAP Object
Fact	Cube
Measure	Measure
Dimension	Dimension
Level	Level
Hierarchy	Hierarchy
LevelAttribute	Attribute
BusinessArea	MeasureFolder

When the PL/SQL file is generated after a metadata export to the Oracle9i OLAP Server, the file can be deployed on your server by using SQL*Plus. For example, to execute the file sales_history.sql into the schema SH, you can execute the following:

sqlplus OLAPDBA/<passwd> @sales_history.sql SH

This defines the OLAP cube, additional dimension metadata and measure folders in the SH schema. The warehouse database objects must be deployed before running this script, otherwise the dependencies are not satisfied.

To create the OLAP short and long description attributes for a dimension, you must perform a best practice. The best practice is when creating a level attribute to edit the column name that implements the attribute. Add the suffix _SHORT_NAME to the column name to represent a short description attribute or add _LONG_NAME for a long description.