Oracle9i Warehouse Builder User's Guide Release 2 (v9.0.2) Part Number A95949-01 |
|
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.
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:
Table H-9 lists the object conversion fom an OMG CWM file system to the Warehouse Builder repository.
Object in OMG CWM | Imported into Warehouse Builder |
---|---|
Package |
Project |
Schema |
Module |
Table |
Table |
View |
View |
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.
Object in ERwin | Imported into Warehouse Builder |
---|---|
Table |
Table |
View |
View |
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.
Object in PowerDesigner | Imported into Warehouse Builder |
---|---|
Table |
Table |
View |
View |
Table H-12 lists the object conversion fom Oracle9i OLAP server to the Warehouse Builder repository.
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.
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.
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:
The following sections provide instructions for performing these tasks.
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.
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:
Create the dummy tables as follows using the Table editor:
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:
The Properties window for that table displays.
The Advanced Attribute Set Properties dialog displays.
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.
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:
DIMENSION LOGICAL NAME : RoleName
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
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.
Object in Warehouse Builder | Object Exported to OMG CWM |
---|---|
Project |
Package |
Module |
Schema |
Dimension |
Dimension |
Fact |
Cube |
Table |
Base Table |
View |
View |
After you export metadata from Warehouse Builder to Oracle Express, use the following steps to access the metadata within Express:
The RDBMS login displays automatically.
Before you look at the data, follow these configuration steps:
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.
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.
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.
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|