| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
Warehouse Builder provides several utilities for sharing its metadata with other Business Intelligence products. This chapter first shows you how to create collections that store definitions for a group of Warehouse Builder objects you can export to other systems using the Warehouse Builder Transfer Wizard. Next, this chapter outlines the steps to enable Business Intelligence (BI) integration and Online Analytical Processing (OLAP) using Warehouse Builder metadata. This chapter includes the following topics:
The following sections in this chapter address Business Intelligence (BI) integration:
Warehouse Builder Transfer Wizard: Use to synchronize, integrate, and use metadata stored in a variety of BI tools. Exchange metadata with CWM compliant applications, Oracle Discoverer, Oracle Express, and Oracle Database OLAP Server. Instructions for using the Transfer Wizard are discussed in this chapter. See "Business Intelligence Integration Using the Transfer Wizard".
Online Analytical Integration with Warehouse Builder: Warehouse Builder is the single tool that enables you to design, deploy, and load multidimensional OLAP objects from different data sources to your Oracle Database database. After the data is loaded, you can use tools and applications to run complex analytical queries that answer your business questions. Using Warehouse Builder, you can now create and manage both your relational and analytical data stores, from the same metadata. See "Online Analytical Processing (OLAP) with Warehouse Builder".
Collections are areas in Warehouse Builder that store the metadata you want to export to other tools and systems. You can use the Warehouse Builder Transfer Wizard to deploy a collection. Use collections to perform the following tasks:
Organize a large logical warehouse.
Validate and generate a group of objects in a collection.
Export metadata to other tools using the Warehouse Builder Transfer Wizard.
You can use the Warehouse Builder Transfer Wizard to export collections from Warehouse Builder to Oracle Discoverer, OLAP Server, and CWM. For more information on exporting collections to other tools, see "Exporting Metadata from Warehouse Builder".
When you create a collection, you do not create new objects or copies of existing objects. You create shortcuts pointing to objects already existing in the project. Use a shortcut to quickly access a base object and make changes to it.
You can define more than one collection within a project and an object can be referenced by more than one collection. For example, each user that accesses a project can create their own collection of frequently used objects. Each user can add the same objects (such as mappings, tables, or process flows) to their separate collections.
Each user can also delete either the shortcut or the base object. Shortcuts to deleted objects are deleted or greyed out in the collection. To remove greyed out shortcuts from a collection, right-click the shortcut and select delete.
Once you open an object in a collection, you obtain a lock on that object. Warehouse Builder prevents other users from editing the same object from another collection.
Use the New Collection Wizard to define a collection.
To define a new collection:
Select and expand a project node on the navigation tree.
Right-click the Collections node and select Create Collection.
Warehouse Builder displays the Welcome page for the New Collections Wizard.
Click Next.
The wizard displays the Name page.
Type in a name for the collection and an optional description.
Click Next.
The wizard displays the Contents page .
Select and expand the project node in the left panel.
The wizard displays a list of objects you can add to the collection.
Select objects from Available Objects in the left panel.
Use the Ctrl key to select multiple objects. You can select objects at the object level or the module level. For example, under the Files node, you can add a specific file or add all the files in a given flat file module.
If you add a module or another collection, Warehouse Builder creates references to the module or collection and also creates references to objects contained in the module or collection.
Click the left to right arrow.
The wizard displays the list of objects under Selected Objects on the right panel. You can remove objects from the list by selecting objects and clicking the right to left arrow.
Click Next.
The wizard displays the Finish page.
Use the Finish page to view the objects you selected for the collection.
Select Back to make changes to your selections.
When you select Finish, Warehouse Builder creates the collection and adds it to the navigation tree. All the objects under the collection are designated with a shortcut symbol in their associated icons.
To edit a collection, right-click the collection from the navigation tree and select Properties. Warehouse Builder displays the properties window as shown in Figure 22-1.
You can edit the following tabs in the properties window:
Name
Contents
Specify the following on the Name Tab:
Name: You can rename the collection. Type in a name for the collection that is unique within the project. In physical naming mode, type a name between 1 to 200 characters. Spaces are not allowed. In logical mode, the maximum number of characters is 200 and spaces are allowed.
Description: You can edit the optional text description with a maximum of 4000 characters.
Specify the following on the Contents Tab:
Use the Contents page to select objects that you want to reference in the collection.
To complete the Contents page:
Select and expand the project node in the left panel.
The wizard displays a list of objects you can add to the collection.
Select objects from Available Objects in the left panel.
Use the Ctrl key to select multiple objects. You can select objects at the object level or the module level. For example, under the Files node, you can select to add a specific flat file or add all the flat files in a given flat file module.
Click the left to right arrow.
The wizard displays the list of objects under Selected Objects on the right panel. You can remove objects from the list by selecting objects and clicking the right to left arrow.
The Warehouse Builder Transfer Wizard enables you to synchronize, integrate, and use metadata stored in multiple sources and formats. The Warehouse Builder Transfer Wizard enables you to import metadata from and export metadata to various tools.
The Transfer Wizard performs two major tasks:
Exporting selected metadata from the Warehouse Builder repository using a bridge to a variety of targets. The target can be:
CWM (Common Warehouse Metamodel) compliant applications version 1.0
Oracle Discoverer versions 4i and 9i
Oracle Express
Oracle Database OLAP Server
The Oracle Discoverer and Oracle Express bridges are only available if you are running Oracle Warehouse Builder client on a Windows platform.
Importing selected metadata from source tools into the Warehouse Builder repository using a bridge. The source can be:
Oracle Database OLAP Server
OMG CWM (Common Warehouse Metamodel) compliant applications version 1.0
Computer Associates ERwin (3.5.1)
Powersoft PowerDesigner (version 6)
The ERwin bridge is only available if you are running Oracle Warehouse Builder client on a Windows platform.
For CWM applications, the Transfer Wizard creates an intermediate XML file conforming to the XML Metadata Interchange (XMI) standard. This process is transparent when you use the Transfer Wizard. You provide the source and target parameters and the Transfer Wizard performs the exporting, conversion, and downloading tasks.
Figure 22-2 shows an example of the transfer process for exporting metadata from Warehouse Builder into Discoverer.
Warehouse Builder enables you to integrate with Meta Integration Model Bridges (MIMB) that translate metadata from a proprietary metadata file or repository to the standard CWM format that can be imported into Warehouse Builder using the Transfer Wizard. After integrating with MIMB, you can also import metadata from CA ERwin, Sybase PowerDesigner, and many other sources through these bridges.
MIMB integration enables you to import metadata into Warehouse Builder from the following sources:
OMG CWM 1.0: Database Schema using JDBC 1.0/2.0
OMG CWM 1.0: Meta Integration Repository 3.0
OMG CWM 1.0: Meta Integration Works 3.0
OMG CWM 1.0: XML DTD 1.0 (W3C)
OMG CWM 1.0: XML DTD for HL7 3.0
Acta Works 5.x
Adaptive Repository
ArgoUML
Business Objects Data Integrator
Business Objects Designer 5.1.3 to 5.1.5
CA COOL
CA ERwin
CA ParadigmPlus
Hyperion Application Builder
IBM
Informatica PowerMart 5.1
Merant App Master Designer 4.0
Miscrodoft Visio
Oracle Designer
Popkin System Architect
ProActivity 3.x & 4.0
Rational Rose
Select SE 7.0
Silverrun
Sybase Power Designer
Unisys Rose
Visible IE Advantage 6.1
Various XML/XMI versions
Follow these steps to integrate Warehouse Builder with MIMB.
After you download the MIMB on your system, Warehouse Builder automatically recognizes the installation and displays the new import options on the Warehouse Builder Transfer Wizard: Metadata Source and Target Identification page.
To download MIMB:
Download the Model Bridge (personal) product from the following Web site: http://www.metaintegration.net/Products/Downloads/
Install the MIMB by running the setup on your system.
During installation, choose Typical with Java Extensions as the installation type from the Setup Type page.
If the set up program is not able to find a JDK on your machine, you must provide the JNI library directory path name. Your path environment variable must contain the metaintegration directory. If not, you need to add it to the path: c:\program files\metaintegration\win32.
Enable MIMB through your Warehouse Builder client by starting the Warehouse Builder Transfer Wizard (Import). For more information on importing metadata using the Warehouse Builder Transfer Wizard, see "Importing Metadata into Warehouse Builder".
This section contains instructions for using the Warehouse Builder Transfer Wizard to import metadata into Warehouse Builder:
To launch the Transfer Wizard for an import:
From the Project menu, select MetaData Import, and then Bridge.
The Oracle Transfer Wizard Welcome window displays, identifying the steps you perform while using the Transfer Wizard.
If you want to display version information about the Transfer Wizard, click About Oracle WB Transfer Tool. For version information about the individual bridges, press the Bridge Versions button from the About Oracle WB Transfer Tool dialog.
Click Next.
The Metadata Source and Target Identification window displays as shown in Figure 22-3
In the From field, identify your metadata source (Oracle9i OLAP, OMG CWM 1.0, CA ERwin, PowerDesigner). You can import metadata from many other sources if you choose to integrate with the Meta Integration Model Bridge (MIMB). For details, see "Integrating with the Meta Integration Model Bridges (MIMB)".
If you integrate with MIMB, an Info button displays next to the From field. Choose a source type and click Info to view details about using the bridge for that source type.
Optionally, enter a Description of the metadata to be transferred.
This description displays in the progress bar during the transfer process.
Click Next.
The Transfer Parameter Identification window displays as shown in Figure 22-4.
The Transfer Parameters window displays a different list of parameters based upon the metadata source you selected. For details, see "Transfer Parameters".
If you are running the Meta Integration Model Bridge (MIMB), the descriptions of the parameters display in the box under on the wizard page. For details, see "Integrating with the Meta Integration Model Bridges (MIMB)".
Click Next. The Summary window displays as shown in Figure 22-4.
Review your entries.
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
Click Finish on the Confirmation window.
The transfer window displays with a status bar as shown in Figure 22-6.
The title of the transfer window is the description you provided. If you did not provide a description, a title does not display.
The transfer can require several minutes or an hour or more to complete, depending on the amount of metadata you transfer.
Do one of the following:
If the transfer completes successfully (100% displays), click OK.
If a transfer failure message displays, click View Log File and review the log. (You can also view the log of a successful transfer.)
To save the log for reference, click Save As to open the Save dialog. Select the folder where you want to store the log and click Save.
If you determine the cause of the failure from the Information Log, note the information requiring update. Close the log by clicking OK. On the transfer window, click Return to Wizard and update the incorrect information on the Transfer Parameters window. Then transfer the data again.
If you cannot determine the cause of the failure from the Information Log, you can create a Trace log. Close the current log by clicking OK. On the transfer window, click Return to Wizard and change the Log Level to Trace on the Transfer Parameters window. Then transfer the data again.
If the transfer is successful, the Transfer Wizard creates an output file and stores it in the location you specified.
The Warehouse Builder Transfer Wizard enables you to export metadata to the following types of targets:
A file that conforms to the OMG CWM standard
Oracle Discoverer versions 4i and 9i
Oracle Express
Oracle Database OLAP
To export metadata to any of these targets, you must first define collections in Warehouse Builder. See "Defining Collections".
To export metadata using the Warehouse Builder Transfer Wizard:
From the Project menu, select MetaData Export, and then Bridge.
The Oracle Transfer Wizard Welcome window displays, identifying the steps you perform while using the Transfer Wizard.
Click Next.
The Metadata Source and Target Identification window displays as shown in Figure 22-7.
In the To field, select your target (OMG, Express, Discoverer, or the OLAP server) to identify the target for your export.
Optionally, enter a Description of the metadata to be transferred.
This description displays in the progress bar during the transfer process.
Click Next.
The Transfer Parameter Identification window displays.
The Transfer Parameters window table lists parameters that you must enter or select. This window displays a different set of parameters depending on the target you selected in the previous step. For details, see "Transfer Parameters".
Click Next.
The Confirmation of Warehouse Builder Transfer window displays.
Review your entries.
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
Click Finish.
The transfer window displays with a status bar.
The title of the transfer window is the description you assigned to the transfer on the Choose Data Source and Target Types window. If you did not provide a description, a title does not display.
The transfer can require several minutes or an hour or more to complete, depending on the amount of metadata you transfer.
Do one of the following:
If the transfer completes successfully, click OK.
If a transfer failure message displays, click View Log File and review the log. You can also view the log of a successful transfer.
To save the log for reference, click Save As to open the Save dialog. Select the folder where you want to store the log and click Save.
If you determine the cause of the failure from the Information Log, note the data requiring update. Close the log by clicking OK. On the transfer window, click Return to Wizard and update the erroneous data on the Transfer Parameters window. Then transfer the data again.
If you cannot determine the cause of the failure from the Information log, you can create a Trace log. Close the current log by clicking OK. On the transfer window, click Return to Wizard and change the Log Level to Trace on the Transfer Parameters window. Then transfer the data again.
During a successful transfer, the Transfer Wizard creates the output file and stores it in the location you specified.
Business organizations typically have complex analytic, forecast, and planning requirements. Analytic Business Intelligence (BI) applications provide a solution by answering critical business questions using the data available in your database. These applications run analytical queries that include time series analysis, inter-row calculations, access to aggregated historical and current data, and forecasts, some of which cannot be performed using SQL queries.
The OLAP option of the Oracle Database database enables you to perform all analytical tasks in the same database system that supports your Business Intelligence applications. For more information, see the OLAP User's Guide.
Warehouse Builder is the single tool that enables you to design, deploy, and load multidimensional OLAP objects from different data sources to your Oracle Database database. After the data is loaded, you can use tools and applications to run complex analytical queries that answer your business questions.
Using Warehouse Builder, you can now create and manage both your relational and analytical data stores from the same metadata.
The analytic workspace (AW) is a container within the Oracle database that stores multidimensional data objects and procedures written in the OLAP DML. AWs are central to analytic processing. You can create multiple Analytic Workspaces within a single database. They are owned by a relational schema and can be shared among multiple users. You can use Warehouse Builder to populate AWs with data from another AW, a flat file, or from relational tables.
Warehouse Builder enables you to prepare your data stores for Oracle Database OLAP processing. Using Warehouse Builder, you can design, deploy, and load online analytical processing (OLAP) objects that provide complex analytic power to your data warehouse.
To run an analytical query without using Warehouse Builder, you need to manually perform these tasks in a MOLAP database:
Define Objects in the Analytic Workspace using PL/SQL or OLAP DDL commands.
Define the loading of the Analytic Workspace.
Create scripts for dependencies.
Map OLAP metadata to relational views.
Create materialized views.
Warehouse Builder enables you to perform all these activities to create an OLAP environment from a relational star schema, as shown in Figure 22-8. First, you can use the Warehouse Builder design environment to create your OLAP metadata. You can define multidimensional objects, such as dimensions and cubes, necessary to create the AW in your database and enable analytical processing.
Using the Warehouse Builder Transfer Bridge, you can deploy this metadata to your database to create the AW, the relational views on top of it, and OLAP metadata based on these views. Next, you can define mappings or process flows in Warehouse Builder to load the data from different data sources to the AW. After you load the OLAP data in the AW, you are ready to perform complex analytical queries on it using BI tools such as Oracle Discoverer and BI Beans.
This section shows you how to perform the following tasks to design and create a Relational Online Analytical Processing (ROLAP) environment using Warehouse Builder:
Create the relational warehouse.
Create the OLAP metadata.
Deploy the OLAP metadata.
Load the data into the Analytic Workspace.
You can use the Warehouse Builder wizards and editors to define your dimensions, hierarchies, attributes, and cubes. For detailed instructions on creating dimensions and cubes, see "Creating a Dimension Definition" and "Creating Cube Definitions". To create OLAP compliant metadata, use the guidelines when described in the following sections.
When you create a dimension, use the extensions listed in Table 22-1 while defining the implementing column for the dimension level attributes. Table 22-1 lists the level attribute names that are generated as OLAP compliant level descriptors when you deploy the dimension to the Oracle Database OLAP catalog using the Warehouse Builder Transfer Wizard.
The generated Warehouse Builder level attribute named ID (created when a level is created) should not be used for the dimension attribute. Because no database dimension level attribute is created for this column, make sure that this level attribute does not follow this naming practice.
Table 22-1 Dimension Attribute Suffixes
| Physical Level Attribute Name Suffixes in Warehouse Builder | Dimension Attribute Created |
|---|---|
|
_NAME or NAME |
Short_Description or Long_Description |
|
_END_DATE or END_DATE |
End_Date |
|
_TIME_SPAN or TIME_SPAN |
Time_Span |
|
_PRIOR_PERIOD or PRIOR_PERIOD |
Prior_Period |
|
_YEAR_AGO_PERIOD or YEAR_AGO_PERIOD |
Year_Ago_Period |
Warehouse Builder creates a dimension attribute for each dimension level attribute that contains an implementing column with a known extension, as shown in Figure 22-9. For example, if an end date is specified for each level, Warehouse Builder also creates a dimension attribute for each level. If a fiscal end date and a calendar end date are specified for each level, then Warehouse Builder creates two dimension attributes using the names of the level attributes. For more information on creating dimensions in Warehouse Builder, see "Creating a Dimension Definition".
An example of a time dimension is provided for you with your Warehouse Builder installation in the form of a Metadata Loader (.MDL) file. Navigate to OWB_Home_Directory\owb\misc\time directory. The readme.txt file in that directory explains how to use the prepackaged .MDL and .SQL files.
To create a time dimension in Warehouse Builder, you must follow these guidelines:
Create the dimension name with the extension _TIME in upper case, for example, T_TIME. This enables the Transfer Bridge to create a descriptor of the type Time Dimension for the dimension. Figure 22-10 shows an example of a time dimension created using these rules.
Create time dimension levels with extensions listed in Table 22-2.
Note that a Week-level suffix (_WEEK) is not included. A week cannot roll up neatly into a calendar month, quarter, or year because weeks span these time periods. If you use fiscal instead of calendar time periods for your time dimension, then weeks are neatly folded into the other time periods, and you can work in a Week level.
The cube is a key object in OLAP processing that represents data in an organized and intuitive way. Warehouse Builder enables you to define a cube using the New Cube Wizard. For more information on creating cubes, see "Creating Cube Definitions".
Figure 22-11 shows a sample cube created in Warehouse Builder.
Collections are areas in Warehouse Builder that store the metadata you want to deploy, for example, to your OLAP database. You can use the Warehouse Builder Transfer Wizard to deploy a collection and create the OLAP metadata that populates the OLAP catalog and creates the AW.
You must first define a collection in Warehouse Builder containing all the OLAP compatible dimensions and cubes.
After you define your metadata, you are ready to deploy the definitions to your Oracle Database OLAP database. To create your OLAP environment, follow these steps in Warehouse Builder:
Generate and deploy the DDL scripts (for dimensions, tables, and cubes) to create the relational storage objects in your OLAP database. You can later run a mapping or a process flow to load these structures with data. See "Loading the Analytic Workspace". For more information on generation and deployment, see Chapter 13, "Deploying Target Systems".
Run the Warehouse Builder Transfer Wizard to deploy metadata to the Oracle Database OLAP server. The Transfer Wizard populates the OLAP catalog, creates the Analytic Workspace objects within and the relational views on top of the Analytic Workspace. It also creates materialized views, if required.
To deploy OLAP metadata:
Start the Warehouse Builder Transfer Wizard from the Project menu by selecting MetaData Export, then Bridge.
The Warehouse Builder Transfer Wizard Welcome page displays.
Click Next.
Provide the following information on the Metadata Source and Target Identification page:
From: Oracle Warehouse Builder Export
To: Oracle9i OLAP Server
Description: Optional
Figure 22-12 shows the Warehouse Builder Transfer Wizard: Step 1 of 3.
Click Next.
Provide the following information on the Transfer Parameter Identification page:
OWB Exported Collections: Specify the collection containing the multidimensional metadata you want to export.
Deploy to AW: Specify whether you want to deploy the metadata to the Analytic Workspace or not.
AW Name: Provide the name of the Analytic Workspace where you want to deploy the metadata.
AW Object Prefix: Prefix for the dimensions and cubes in the AW.
Load Cube Data: Loads the cube data into the AW. If this option is not selected, only the cube loading program will be loaded.
Generate Surrogate Keys for Dimensions: Generate surrogate keys for dimensions if your dimension values are not unique across all levels. This option can be used to generate surrogate keys.
Generate View Definitions: Specify whether you want to generate view definitions for the Analytic Workspace. This option will also generate OLAP metadata for the cube based on the SQL view for the Analytic Workspace.
Generate Materialized Views: Indicate whether you want to generate materialized views for the relational implementation of the cube.
Generated View Directory: Specify the name of a directory to store the generated view scripts. If you choose the Deploy PL/SQL in Database option, this directory is located on the client side. Otherwise this is a server side directory object.
Deploy PL/SQL in Database: Specify if you want to deploy PL/SQL to the OLAP database. This option deploys the OLAP metadata and, if selected, the materialized views and views in the Analytic Workspace.
OLAP Instance Information: Specify the host name, Port, Service Name, Username, and Password to access the OLAP target instance.
PL/SQL Output File: Specify a location for the PL/SQL output file.
Log Level: Specify the level of detail you want to obtain from your log file after the transfer is complete.
Figure 22-13 shows the Warehouse Builder Transfer Wizard: Step 2 of 3.
Click Next.
The Transfer Wizard Summary page displays.
Review the information you provided in the previous steps. Click Finish to start the deployment.
The Transfer Bridge generates a script to perform the following:
Create OLAP metadata for cubes in the collection.
Optionally create metadata in an AW based on the ROLAP metadata.
Optionally generate a script to create SQL views and OLAP metadata based on the AW. For details on these views, refer to the OLAP User's Guide.
Optionally generate a script to create Materialized Views based on the relational data for performance of BI Beans and OLAPI. For details on these views, refer to the OLAP User's Guide.
When you use the Deploy PL/SQL in Database option for the bridge, the diagnostics are provided in a log file. If you execute the generate PL/SQL manually, then the following helps you in debugging.
To debug the OLAP export, you can save the generated PL/SQL code to a file and execute it using SQL*Plus. To see additional error details, the server output must be switched on in SQL*Plus. For example, before executing the script in SQL*Plus, you must execute the following:
SET SERVEROUTPUT ON SIZE 99999
When there are errors, you will see detailed error messages outlining the types of errors. A common error for the OLAP bridge is when you fail to deploy dependent objects, such as dimensions, to the relational part of the server. For example, if you run the bridge before you deploy the relational dimension, the following error displays as shown in Table 22-3.
Table 22-3 Error Messages When the Dimension Has Not Been Deployed
| Error Details | Description |
|---|---|
|
ERROR: dimension_not_found |
Error type from OLAP |
|
Object Type: DIMENSION |
The object type concerned. |
|
Object Owner: RTS60 |
The schema name. |
|
Object Name: DD |
The object name. |
|
Secondary Name |
Second class object name (column name). |
|
Tertiary Name |
Third class object name (level attribute name). |
After you deploy the metadata to your OLAP database system, you can load your relational source data into the analytic workspace using one of the following methods:
Design a mapping with a post-mapping process that uses a PL/SQL routine provided with Warehouse Builder.
Design a process flow containing a PL/SQL routine provided with Warehouse Builder.
Warehouse Builder provides PL/SQL packages that enable you to create and load (or refresh) relational data into the analytic workspace. These packages form a wrapper on top of the DBMS-OLAP procedures provided in Oracle Database Release 2. For detailed information, see the OLAP User's Guider for documentation on DBMS_AWM routines. This package provides routines for performing incremental loads and customized aggregations behavior.
To locate the PL/SQL packages in Warehouse Builder:
From the Warehouse Builder tree, expand the PUBLIC TRANSFORMATIONS node.
Expand the Pre-Defined node and then expand the OLAP node as shown in Figure 22-14.
Warehouse Builder displays the wrapper packages: WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_DIMENSION_GENUK.
Create a mapping that joins different dimension tables, transforms them, and loads them into the dimension or cube. To load the data into the Analytic Workspace, you must define a Post-Mapping Process with a Constant containing the load values.
To add a Post-Mapping Process to a mapping to create and load the Analytic Workspace:
Drag and drop a Post-Mapping Process operator from the Toolbox onto the Mapping Editor canvas.
The Add Mapping Transformation dialog displays.
Choose Select from existing repository Transformation and bind.
Locate the OLAP packages from the Warehouse Builder navigation tree. See the steps listed in the previous section for more information on locating the packages.
Click OK.
The Post-Mapping Process transformation displays on the canvas.
If you select the WB_OLAP_LOAD_CUBE procedure, you see the following attributes:
OLAP_AW_OWNER: Owner of the Analytic Workspace to be created or maintained.
OLAP_AW_NAME: Name of the Analytic Workspace to be created or maintained
OLAP_CUBE_OWNER: Owner of the cube.
OLAP_CUBE_NAME: Name of the cube.
OLAP_TGT_CUBE_NAME: Name of the cube in the target.
If you select the WB_OLAP_LOAD_DIMENSION or WB_OLAP_LOAD_DIMENSION_GENUK procedure, you see the following attributes:
OLAP_AW_OWNER: Owner of the Analytic Workspace to be created or maintained.
OLAP_AW_NAME: Name of the Analytic Workspace to be created or maintained.
OLAP_DIMENSION_OWNER: Owner of the dimension.
OLAP_DIMENSION_NAME: Name of the dimension.
OLAP_TGT_DIMENSION_NAME: Name of the dimension in the target system.
Drag and drop a Constant operator from the Toolbox onto the Mapping Editor canvas.
Right-click the Constant operator and select Edit from the pop-up menu.
From the Constant Editor, select the Output tab.
Define the same attributes for the Constant as defined for the Post-Mapping Process.
Map the attributes from the Constant operator to the attributes in the Post-Mapping Process as shown in Figure 22-15.
From the Constant operator, right-click each attribute and select Attribute Properties.
The Attribute Properties dialog displays.
Click in the Expression field and click the ... button.
The Expression Builder displays.
Type the value for the attribute in the Expression Builder. For example, for the attribute OLAP_DIMENSION_NAME as shown in Figure 22-16, type 'CUSTOMERS', which is the name of the dimension to be deployed.
Continue this process until you assign expressions to all the attributes in the Constant operator.
You are now ready to validate the mapping and to generate PL/SQL code. After you generate the PL/SQL, you can run the mapping to load the cubes and dimensions in your OLAP instance. Warehouse Builder loads a fully resolved cube, if you choose, in the Analytic Workspace.
You can also create a process flow to load your OLAP dimensions and cubes into the AW. Design a process flow containing the PL/SQL routines provided with Warehouse Builder.
To locate the PL/SQL packages in Warehouse Builder:
Create a process flow. For more information, see Chapter 10, "Designing Process Flows".
Drag and drop the Transform activity onto the canvas.
Warehouse Builder displays a dialog for you to select a transformation.
Expand the PUBLIC node.
Expand the Pre-Defined node and then expand the OLAP node.
Warehouse Builder displays the wrapper procedures: WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_GENUK.
Select one of these packages.
Define the parameters for the dimensions and cubes in the Activity View panel.
If you selected the WB_OLAP_LOAD_CUBE package, you will see the following parameters. Type the value for each of these parameters in the Value column. Optionally, type a description for each parameter in the Description column.
OLAP_AW_OWNER: Owner of the Analytic Workspace to be created or maintained.
OLAP_AW_NAME: Name of the Analytic Workspace to be created or maintained
OLAP_CUBE_OWNER: Owner of the ROLAP cube.
OLAP_CUBE_NAME: Name of the ROLAP cube.
OLAP_TGT_CUBE_NAME: Name of the cube in the target Analytic Workspace.
If you selected the WB_OLAP_LOAD_DIMENSION procedure, you will see the following parameters. Type the value for each of these parameters in the Value column. Optionally, type a description for each parameter in the Description column.
OLAP_AW_OWNER: Owner of the Analytic Workspace to be created or maintained.
OLAP_AW_NAME: Name of the Analytic Workspace to be created or maintained.
OLAP_DIMENSION_OWNER: Owner of the ROLAP dimension.
OLAP_DIMENSION_NAME: Name of the ROLAP dimension.
OLAP_TGT_DIMENSION_NAME: Name of the dimension in the target Analytic Workspace.
You are now ready to validate the process flow and to generate XPDL code. After you generate the code, you can run the process flow to load the cubes and dimensions in your OLAP instance. Warehouse Builder loads a cube in the Analytic Workspace and the OLAP routines create dynamic aggregator maps for resolving the cube.
If you need to perform any level of precalculated aggregation or incremental refreshes, see the OLAP User's Guide for details on operations in the DBMS_AWM package.
To access the information required to debug the cloning transformation, you can build a PL/SQL wrapper to customize the required logging.
To create a PL/SQL wrapper, create public transformations containing the same signatures as the ones provided by Warehouse Builder: WB_OLAP_LOAD_DIMENSION and WB_OLAP_LOAD_CUBE. The implementation of these transformations can use the OLAP routines to enable logging;
CWM2_OLAP_MANAGER.BEGIN_LOG(<directory_alias>, <filename>); CWM2_OLAP_MANAGER.END_LOG;
These transformations can be used to start or end a log file. During these calls, you can invoke the appropriate Warehouse Builder routine to load the dimension or cube. For example, to trace the dimension load you can create a procedure with the following block of code using the directory object
'MY_OLAP_TRACE';
CWM2_OLAP_MANAGER.BEGIN_LOG('MY_OLAP_TRACE', olap_dimension_name || '.log'); OWB_OLAP_LOAD_DIMENSION(olap_aw_owner, olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name);
CWM2_OLAP_MANAGER.END_LOG;
This code provides a level of trace useful for debugging issues in the data load.
The Warehouse Builder OLAP best practices integrate the design definitions in Warehouse Builder, the Warehouse Builder OLAP bridge, and the OLAP metadata it generates. The minimal requirement for an OLAP dimension is that the lowest level in the hierarchy have a level attribute nominated for the description. This can be achieved by using the best practices.
There are two types of execution modes available for the Warehouse Builder OLAP bridges: Standard Mode and Debug Mode. These modes are discussed in the following sections.
If you deploy the PLSQL scripts directly from the bridge, it will::
Load the ROLAP catalog metadata in the OLAP catalog.
Clone the ROLAP cube in the AW (MOLAP solution - optional).
Enable the MOLAP cube stored in AW with OLAPI by executing the OLAP generated scripts to create metadata in CWM2 (optional).
Create materialized views for the ROLAP cube by executing the OLAP generated scripts to generate Materialized Views (optional).
Selecting No for AW clone, OLAP API generation, and MV generation will generate only the ROLAP OLAP catalog metadata. Switch off these options to obtain the ROLAP model before cloning.
Specify a valid directory on the client for the directory parameter. The OLAP API enabler scripts will be generated on the client under this directory. The bridge will execute these scripts and a script will be generated for each dimension and cube in the collection. In the script, the physical name of the object will be suffixed with .sql and will reside in the directory on the client file system. A drop script will also be generated for each dimension and cube in the collection, with the physical name of the object suffixed with _drop.sql. This script will also reside in the directory on the client file system. This can be used for removing the SQL object types and views.
Specify a valid directory in the client for the directory parameter. The MV scripts will be generated on the client under this directory. The bridge will execute these scripts and a script will be generated for each dimension and cube in the collection. The script will suffix the physical name of the object with _mv.sql and will be located in the directory on the client file system.
When you execute the generated PLSQL script from SQL*Plus, it will:
Load the ROLAP catalog metadata in the OLAP catalog
Clone the ROLAP cube in the AW (MOLAP solution - optional).
Generate scripts to enable the MOLAP cube stored in the AW with OLAPI (optional).
Generate scripts to define materialized views for the ROLAP cube (optional).
If you select No for AW cloning, OLAP API generation, and MV generation, the bridge will generate only the ROLAP OLAP catalog metadata. This step must be completed before AW cloning. Switching off these options is useful for obtaining the right ROLAP model before cloning.
Specify a valid directory object for the directory parameter. The OLAP API enabler scripts will be generated on the server under this directory object. You are responsible for executing these scripts. A script will be generated for each dimension and cube in the collection, containing the physical name of the object suffixed with .sql. These scripts will be stored in the directory object location on the server file system. A drop script will also be generated for each dimension and cube in the collection. This drop script will contain the physical name of the object suffixed with _drop.sql and will be stored in the directory object location on the server file system. This can be used for removing the SQL object types and views.
The user must specify a valid directory object for the directory parameter. The MV scripts will be generated on the server under the directory object. You are responsible for executing these scripts. A script will be generated for each dimension and cube in the collection, containing the physical name of the object suffixed with _mv.sql, located in the directory object location on the server file system.
You can customize the bridge according to the cloning of your AW. The customization can be done on a cube by cube basis using special tags inside the description for the cube in Warehouse Builder. These tags and their content will be removed from the final cube description in the OLAP catalog. The preload block can be used to define the segwidths of dimensions or to define composite dimensions in the AW. These structures can be used to improve performance from the AW. The postload block can be used to define calculated measures in the AW. The following two sets of tags can be used for customization:
<PRELOAD>
anonymous_plsql_block
</PRELOAD>
and
<POSTLOAD>
anonymous_plsql_block
</POSTLOAD>
The bridge generates an OLAP load specification with the name of the cube. The preload block can be used to augment this load specification (using the OLAP apis) to define a composite specification which can then be added to the load specification.
The following example uses the preload block that defines a new composite specification to create a composite dimension named CMP1 in the AW containing the dimensions CUSTOMERS and PRODUCTS with segwidth 555555.
<PRELOAD>
declare
cnam varchar2(32);
cub varchar2(30);
cst varchar2(30);
BEGIN
cnam := 'ORDERS_CST';
cub := 'ORDERS';
cst := 'CMP1';
begin
dbms_awm.delete_awcomp_spec(cnam, USER, cub);
EXCEPTION when others then null;
end;
dbms_awm.create_awcomp_spec(cnam, USER, cub);
dbms_awm.add_awcomp_spec_member(cnam, USER, cub, 'T_TIME', 'DIMENSION', USER, 'T_TIME');
dbms_awm.set_awcomp_spec_member_seg(cnam, USER, cub, 'T_TIME', 7777777);
dbms_awm.add_awcomp_spec_member(cnam, USER, cub, cst, 'COMPOSITE');
dbms_awm.add_awcomp_spec_comp_member(cnam, USER, cub, cst, cst||'_CUSTOMERS',
'DIMENSION', USER, 'CUSTOMERS');
dbms_awm.add_awcomp_spec_comp_member(cnam, USER, cub, cst, cst||'_PRODUCTS', 'DIMENSION',
USER, 'PRODUCTS');
dbms_awm.set_awcomp_spec_member_seg(cnam, USER, cub, cst, 555555);
dbms_awm.add_awcubeload_spec_comp(cub, USER, cub, cnam);
EXCEPTION
WHEN OTHERS THEN raise;
END;
</PRELOAD>
This will add the composite specification 'ORDERS_CST' to the load specification 'ORDERS' created by the bridge. This code is invoked before the OLAP refresh cube routine. See the OLAP User Guide for details on the OLAP apis and their functionality.
The postload block is executed after the cube has been created in the AW. It can be used for to define new calculated measures in the AW based on the objects created during the bridge run. The AW is then updated and committed at the end of the bridge run.
The OLAP cloning routines used by Warehouse Builder and the ones exposed as transformations are the base routine for cloning ROLAP cubes in the AW and for refreshing the data. The refresh defined in these routines is a complete load of the dimension or fact table. The following sections discuss how to perform incremental loading and pre-calculated aggregations.
The default cloning and load behavior of the Warehouse Builder OLAP integration loads entire sets of data. To perform incremental loading of the AW, you need to use OLAP routines and define filters (SQL WHERE clauses) for refining the actual rows to load from the dimension or cube. The transformation ORDERS_LOAD_FILTER enables you to use the OLAP filter routines for loading only a select set of rows (where month_id > 33) from the ROLAP fact table. The follow example shows the use of the OLAP APIs.
procedure ORDERS_LOAD_FILTER
BEGIN
dbms_awm.create_awcubeload_spec ('ORDERS_FIL', USER, 'ORDERS', 'LOAD_DATA');
-- Define the where clause for the load specification
dbms_awm.Add_AWCubeLoad_Spec_Filter('ORDERS_FIL',USER,'ORDERS',USER,'ORDERS',' month_id>33');
dbms_awm.refresh_awcube (USER, 'AWS', 'AWORDERS', 'ORDERS_FIL');
EXCEPTION
WHEN OTHERS THEN
NULL; -- enter any application specific exception code here
END;
By default, during cloning the Warehouse Builder OLAP bridge creates runtime aggregation maps (AGGMAPs) in the AW that are used to fully resolve the cube. If you want to optimize the cube by performing any customized pre-calculated aggregations, then the OLAP APIs can be used to build such a definition. The custom aggregation can be executed as an activity in a process flow.
In the following example, the transformation ORDERS_BUILD uses the OLAP routines to pre-calculate to a certain level in the cube in the AW. The following example shows you how to use the OLAP APIs.
procedure ORDERS_BUILD
aggspec VARCHAR2(32);
AW_NAME VARCHAR2(32);
CUBE_NAME VARCHAR2(32);
BEGIN
aggspec := 'AGGORDERS';
AW_NAME := 'TESTAW';
CUBE_NAME := 'AWORDERS';
dbms_awm.create_awcubeagg_spec(aggspec, USER, AW_NAME, CUBE_NAME);
-- Only aggregate city and customer for the CUSTOMERS dimension
dbms_awm.add_awcubeagg_spec_level(aggspec, USER, AW_NAME, CUBE_NAME, 'AWCUSTOMERS', 'CITY');
dbms_awm.add_awcubeagg_spec_level(aggspec, USER, AW_NAME, CUBE_NAME, 'AWCUSTOMERS',
'CUSTOMER');
-- Only aggregate day and month for the time dimension
dbms_awm.add_awcubeagg_spec_level(aggspec, USER, AW_NAME, CUBE_NAME, 'AWT_TIME', 'DAY');
dbms_awm.add_awcubeagg_spec_level(aggspec, USER, AW_NAME, CUBE_NAME, 'AWT_TIME', 'MONTH');
dbms_awm.add_awcubeagg_spec_measure(aggspec, USER, AW_NAME, CUBE_NAME, 'AMOUNT');
-- Now build the cube. This may take some time on large cubes.
dbms_awm.aggregate_awcube(USER, AW_NAME, CUBE_NAME, aggspec);
EXCEPTION
WHEN OTHERS THEN
NULL; -- enter any application specific exception code here
END;
This section includes the following topics:
Importing Metadata from an Object Management Group CWM Standard System
Exporting Metadata to an Object Management Group CWM Standard System
The Warehouse Builder Transfer Wizard enables you to import metadata intoWarehouse Builder from data warehousing tools and file systems such as Object Management Group Common Warehouse Metamodel, Computer Associates ERwin, Powersoft PowerDesigner, and Oracle9i OLAP Server. It also enables you to export the metadata from Warehouse Builder to Oracle tools such as Oracle Discoverer, Oracle Express, and Oracle9i OLAP Server.
When you use the Warehouse Builder Transfer Wizard, you must provide transfer parameter information based upon the metadata source or target you select. Table 22-4 lists the transfer parameters for each data source or target type.
Table 22-4 Transfer Parameters for OLAP Server Import
| Transfer Parameter Name | Description |
|---|---|
|
Username |
Username to access the OLAP server instance from where you want to import metadata. |
|
Password |
Password to access the OLAP server instance from where you want to import metadata. |
|
Hostname |
Host system for the OLAP server instance from where you want to import metadata. |
|
Port |
Port number for the OLAP server instance from where you want to import metadata. |
|
Service Name |
Service Name for the OLAP server instance from where you want to import metadata. |
|
Measure Folder |
Provide a name for the folder containing the measures that you want to import into Warehouse Builder. When you import the measures, Warehouse Builder creates a collection containing the cubes corresponding to these measures. |
|
Project |
Type a name for the new project to be created in Warehouse Builder during the import. This parameter is based on your import mode. If you are replacing or updating the metadata, then you need to specify an existing project into which you want to import the metadata. |
|
Default Module Type for Relational Schemas |
Choose the type of module into which you want to import the metadata, source or warehouse. The transfer wizard will create the correct type of module and import the metadata into it. |
|
Process OLAP Physical Representation |
If you are importing metadata from a star schema, then you can choose Yes if you want to maintain its physical representation or No to only import its logical definitions. If you are importing a snowflake schema, its physical implementation is lost and only the logical definitions are imported into Warehouse Builder. |
Table 22-5 Transfer Parameters for OMG CWM FIle Import
| Transfer Parameter Name | Description |
|---|---|
|
OMG CWM Input File |
Specify the file containing the OMG CWM model you want to import. Click ... to browse for the file location. |
|
CWM Project |
Specify the file containing the OMG CWM model you want to import. Click ... to browse for the file location. |
|
Default Module Type for Relational Schemas |
Choose the type of module into which you want to import the metadata, source or warehouse. The transfer wizard will create the correct module and import the metadata into it. |
|
Process OLAP Physical Representation |
If you are importing metadata from a star schema, then you can choose Yes if you want to maintain its physical representation or No to only import its logical definitions. If you are importing a snowflake schema, its physical implementation is lost and only the logical definitions are imported into Warehouse Builder. |
|
Import Mode |
Select an import mode: create, replace, update, or incremental update. |
|
Log Level |
Log level to be assigned to the transfer: Errors, Information, and Trace. Error: lists 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 22-6 Transfer Parameters for Importing from CA ERwin
| Transfer Parameter Name | Description |
|---|---|
|
Input Model File |
Select the file that was exported from ERwin. Click ... to browse for the file location. |
|
Warehouse Builder Project |
Type a name for the newly imported Project that will be located under the Project node in Warehouse Builder. This parameter is based on your import mode. If you are replacing or updating the metadata, then you need to specify an existing project into which you want to import the metadata. |
|
Warehouse Builder Module Name |
Choose the type of module into which you want to import the metadata, source or warehouse. The transfer wizard will create the correct module and import the metadata into it. |
|
Default Module Type for Relational Schemas |
Name matching mode you want to utilize during the import. Physical name mode is the default. |
|
Process OLAP Physical Representation |
If you are importing metadata from a star schema, then you can choose Yes if you want to maintain its physical representation or No to only import its logical definitions. If you are importing a snowflake schema, its physical implementation is lost and only the logical definitions are imported into Warehouse Builder. |
|
Import Mode |
Select an 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 22-7 Transfer Parameters for Importing from Powersoft PowerDesigner
| Transfer Parameter Name | Description |
|---|---|
|
Input Model File |
Select the file that was exported from PowerDesigner. Click ... to browse for the file location. |
|
Warehouse Builder Project |
Type a name for the newly imported Project that will be located under the Project node in Warehouse Builder. This parameter is based on your import mode. If you are replacing or updating the metadata, then you need to specify an existing project into which you want to import the metadata. |
|
Warehouse Builder Module Name |
Type the name of the Warehouse Builder module that will be created during import. |
|
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 |
|
Default Module Type for Relational Schemas |
Name matching mode you want to utilize during the import. Physical name mode is the default. |
|
Process OLAP Physical Representation |
If you are importing metadata from a star schema, then you can choose Yes if you want to maintain its physical representation or No to only import its logical definitions. If you are importing a snowflake schema, its physical implementation is lost and only the logical definitions are imported into Warehouse Builder. |
|
Import Mode |
Select an 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 22-8 Transfer Parameters for Exporting Metadata to OMG CWM
| Transfer Parameter Name | Description |
|---|---|
|
Warehouse Builder Exported Collections |
Select the collections you want to export from Warehouse Builder. The default is All Collections. You can also select individual Collections from the drop-down list. |
|
OMG CWM Output File |
Type an output file for the exported Collection. Click the field and the ... button to browse and select a 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. |
|
Warehouse Builder Translated Language |
Select the translated language to export. The default will be the base MLS language in the repository. You can choose to select a specific language, or you can select All to export all supported languages. |
Table 22-9 Transfer Parameters when Exporting Metadata to Discoverer
| Transfer Parameter Name | Parameter Description |
|---|---|
|
Warehouse Builder Exported Collections |
Select the collections you want to export from Warehouse Builder. The default is All Collections. You can also select individual Collections from the drop-down list |
|
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 cube 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. |
|
OWB Translated Language |
Select the translated language to export. The default will be the base MLS language in the repository. You can choose to select a specific language, or you can select All to export all supported languages. An EEX file will be created for each language. |
Table 22-10 Transfer Parameters when Exporting Metadata to Express
| Transfer Parameter Name | Parameter Description |
|---|---|
|
Warehouse Builder Exported Collections |
Select the collections you want to export from Warehouse Builder. The default is All Collections. You can also select individual Collections from the drop-down list |
|
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. |
|
OWB Translated Language |
Select the translated language to export. The default will be the base MLS language in the repository. You can choose to select a specific language, or you can select All to export all supported languages. |
Table 22-11 Transfer Parameters when Exporting Metadata to Oracle9i OLAP server
| Transfer Parameter Name | Parameter Description |
|---|---|
|
Warehouse Builder Exported Collections |
Select the collections you want to export from Warehouse Builder. The default is All Collections. You can also select individual Collections from the drop-down list |
|
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 |
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. |
|
OWB Translated Language |
Select the translated language to export. The default will be the base MLS language in the repository. You can choose to select a specific language, or you can select All to export all supported languages. |
|
Deploy to AW |
Specify whether you want to deploy the metadata to the Analytic Workspace or not. |
|
AW Name |
Name of the Analytic Workspace to be created or maintained. |
|
AW Object Prefix |
Provide the name of the Analytic Workspace where you want to deploy the metadata. |
|
Load Cube Data |
Loads the cube data into the AW. If this option is not selected, only the cube loading program will be loaded. |
|
Generate Surrogate Keys for Dimensions |
Generate surrogate keys for dimensions if your dimension values are not unique across all levels. This option can be used to generate surrogate keys. |
|
Generate View Definitions |
Specify whether you want to generate view definitions for the Analytic Workspace. This option will also generate OLAP metadata for the cube based on the SQL view for the Analytic Workspace. |
|
Generate Materialized Views |
Indicate whether you want to generate materialized views for the relational implementation of the cube. |
|
Generated View Directory |
Specify the name of a directory to store the generated view scripts. If you choose the Deploy PL/SQL in Database option, this directory is located on the client side. Otherwise this is a server side directory object. |
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 section provides instructions for transferring 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 collections. Before you export metadata, you must create collections within the Warehouse Builder repository.
This section includes the following topics:
Table 22-12 lists the object conversion from an Object Management Group (OMG) Common Warehouse Metamodel (CWM) file system to the Warehouse Builder repository.
Table 22-12 Object Management Group CWM Standard Conversions
| Object in Warehouse Builder | Object Exported to OMG CWM |
|---|---|
|
Project |
Package |
|
Module |
Schema |
|
Dimension |
Dimension |
|
Level |
Level |
|
Level Attributes |
Attribute |
|
Hierarchy |
Hierarchy |
|
Cube |
Cube |
|
Cube Attributes |
Measure |
|
Table |
Table |
|
Column |
Column |
|
Foreign Key |
Foreign Key |
|
Unique Key |
Unique Constraint/Primary Key |
|
View |
View |
|
Column |
Column |
While importing a file from ERwin into the Warehouse Builder repository, save the file containing the metadata for transfer as an .ERX file.
Table 22-13 lists the object conversion from ERwin to the Warehouse Builder repository.
While importing a file from PowerDesigner into the Warehouse Builder repository, save the file containing the metadata for transfer as a .PDM file.
Table 22-14 lists the object conversion from PowerDesigner to the Warehouse Builder repository.
Table 22-15 lists the object conversion from Oracle9i OLAP server to the Warehouse Builder repository.
Table 22-15 Object Conversion for Import from Oracle9i OLAP server into Warehouse Builder
| OLAP Object in Oracle9i | Imported into Warehouse Builder |
|---|---|
|
Schema |
Module |
|
Cube |
Cube |
|
Measure |
Measure |
|
Dimension |
Dimension |
|
Level |
Level |
|
Hierarchy |
Hierarchy |
|
Attribute |
LevelAttribute |
|
MeasureFolder |
Collection |
There are certain structures that Warehouse Builder does not support that are possible in the Oracle9i OLAP server. For example, Warehouse Builder does not support snowflake modelling and cubes based upon views. If you have OLAP definitions that use these structures, you can import the logical definition of these objects, but not their physical representation.
This section describes guidelines for importing cubes and dimensions from an OLAP server.
The level identifier columns that have been generated from Warehouse Builder with a prefix are imported with that prefix. Because these columns are not modelled as relational dimension level attributes, there are no names in the database without the prefix. Therefore, when a you import dimension, the level identifying columns which have Warehouse Builder 'level attributes' defined will have also have the prefix in their names.
You can manually remove these prefixes. For example, if you define a dimension CUSTOMERS in Warehouse Builder with a level COUNTRY and level prefix of CT, then this level has a level attribute identifying column called COUNTRY_ID. The generated code maps COUNTRY to CT_COUNTRY_ID and the prefix is incorporated in the column name. When you import it, the level attribute defined in Warehouse Builder is named CT_COUNTRY_ID.
If set to True, the bridge parameter Process OLAP Physical Representation imports the physical structure of the relational dimension for star schemas. If the cube is a snowflake, then Warehouse Builder imports the logical definition of the cube and discards the physical implementation is discarded regardless of the setting. If a cube refers to dimensions across schemas, then those dimensions are ignored.
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 22-16 lists the object conversion from Warehouse Builder repository to Discoverer.
Table 22-16 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 |
|
Cube |
Folder |
|
Cube Attributes |
Item |
|
Table |
Folder |
|
Columns |
Item |
|
Unique Keys |
Key |
|
View |
Folder (View) |
|
Columns |
Item |
|
Attributes flagged as Item Classes |
Item Classes |
|
Collection |
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:
Configuring Warehouse Builder for dimensional reuse.
Creating collections 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.
Within Warehouse Builder, a cube 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. Discoverer requires these dimension roles 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 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 dummy table to real dimension association relies on any one of the contained columns. The dummy column to cube association is determined by making the column's physical name the same as the physical name of the foreign key constraint in the cube.
The following example illustrates how you can set up dimensional roles for exporting to Discoverer:
Create dimensions–DAY, PRODUCT and OPERATOR–in the normal way.
Create cubes–SALES and FLIGHTS–in the normal way.
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
Create the dummy tables as follows using the Table editor:
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
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
For the PRODUCT (PURCHASE) role, define the table as follows:
Physical Name PROD##PUR (logical name PURCHASE)
Column Physical Name SALES_PROD_FK
For the PRODUCT (TICKET) role, define the table as follows:
Physical Name PROD##TIC (logical name TICKET)
Column Physical Name FLIGHTS_PROD_FK
Right-click the dummy table, select Configure from the pop-up menu, and set the Deployable value to False.
In order to use a specific level attribute in a hierarchy, the physical name of the level attribute must be one of the following:
<level name>_NAME
NAME
For example: a product dimension has one hierarchy p_hierarchy that consists of 3 levels with the following attributes (physical names):
PRODUCT_TYPE:
T_ID (unique key (level))
T_NAME
T_DESCRIPTION
PRODUCT_CATEGORY
C_ID (unique key (level))
C_NAME
C_DESCRIPTION
PRODUCT
P_ID (unique key (level); physical primary key)
P_NAME
P_DESCRIPTION
The default bridge behavior is to build a hierarchy based on T_ID, C_ID and P_ID. In order to (for example) use the names instead of the IDs, define the following levels with physical attribute names:
PRODUCT_TYPE
T_ID (unique key (level))
PRODUCT_TYPE_NAME (or NAME)
T_DESCRIPTION
PRODUCT_CATEGORY
C_ID (unique key (level))
PRODUCT_CATEGORY_NAME (or NAME)
C_DESCRIPTION
PRODUCT
P_ID (unique key (level); physical primary key)
PRODUCT_NAME (or NAME)
P_DESCRIPTION
The bridge will in this case use the (...)NAME attributes as hierarchy node attributes.
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:
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.
Expand the Warehouse Module Editor navigation tree.
Right-click the cube 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.
Select the Attribute Sets tab on the Properties window.
Create a new Attribute Set for the export by clicking Add and setting the Attribute Type to be BRIDGE_TYPE.
Select the attributes to include within this set and click Advanced.
The Advanced Attribute Set Properties dialog displays.
Choose the columns to hide in Discoverer by checking the corresponding box under the Hidden field.
You can also use this dialog to define Item Class and set default aggregation and default position of table columns within 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 4i and 9i, 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. For more information, see "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 needs to know which join to use the first time the two folders representing the tables are referred to in a query. Subsequently, it always uses that join in further parts of the query. If the query requires different joins at different times, the only way to support it 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 cube, 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 cube, the original role is reused. For example, if a FLIGHTS cube was also using the ORDER_DATE role, it would reference the same folder as the one referenced by the SALES cube.
The Discoverer navigation tree denotes dimensional reuse (two or more foreign key constraints pointing to the same dimension in Warehouse Builder) 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 preceding default role)
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 22-17 lists the object conversion from the Warehouse Builder repository to the OMG CWM standard.
Table 22-17 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 |
|
Cube |
Cube |
|
Cube Attributes |
Measure |
|
Table |
Table |
|
Column |
Column |
|
Foreign Key |
Foreign Key |
|
Unique Key |
Unique Constraint/Primary Key |
|
View |
View |
|
Column |
Column |
After you export metadata from Warehouse Builder to Oracle Express, use the following steps to access the metadata within Express:
Open RAA.
Enter the user name and password for your RAA repository.
Open the project that you just transferred. After you run the bridge, the project will appear in the drop-down list.
Once the project is open, select Express Database Maintenance and enter the user name, password, and service name for your RAA repository.
Choose Create Maintenance Procedure, then provide the procedure and select general maintenance.
The RDBMS login displays automatically.
Select Generate Qualified Selects at Runtime.
Select defaults for dimension processing.
Name your Express database and log file. These will be saved on the OES server machine under d:\orant\database\express_info.
Choose OES and then OES Batch Manager to monitor this job (Jobs -> Monitor).
You have now created your raw Express database.
Before you look at the data, follow these configuration steps:
Open the OSA Application Manager.
Select Database Setup and then Create.
You can now create a .dsc file. Provide a name for it that reflects the project you transferred. 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.
Select Communication Setup, then Define, and then Create.
Name your setup and choose thin-client, remote system. This is the server machine. For RPC, choose TCP/IP.
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.
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 22-18 lists the object conversion from the Warehouse Builder repository objects to OLAP objects in Oracle9i.
Table 22-18 Object Conversion for Export from Warehouse Builder into Oracle9i OLAP server
| Object into Warehouse Builder | Exported OLAP Object |
|---|---|
|
Cube |
Cube |
|
Measure |
Measure |
|
Dimension |
Dimension |
|
Level |
Level |
|
Hierarchy |
Hierarchy |
|
LevelAttribute |
Attribute |
|
Collection |
MeasureFolder |
Warehouse Builder generates a PL/SQL file after a metadata export to the Oracle9i OLAP Server. You can deploy this file to your server 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.
