Release 9.2
Part Number B10996-01
 Contents |
 Index |
| Oracle9i Warehouse Builder User's Guide Release 9.2 Part Number B10996-01 |
|
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:
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:
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 (mappings, tables, process flows, etc.) 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 on 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:
Warehouse Builder displays the Welcome page for the New Collections Wizard.
The wizard displays the Name page.
The wizard displays the Contents page as shown in.
The wizard displays a list of objects you can add to the collection.
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.
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 wizard displays the Finish page as shown in.
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:
Specify the following on the Name Tab:
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:The wizard displays a list of objects you can add to the collection.
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:
The Oracle Discoverer and Oracle Express bridges are only available if you are running Oracle9i Warehouse Builder client on a Windows platform.
The ERwin bridge is only available if you are running Oracle9i 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:
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:
If the set up program is not able to find a JDK on your machine, you must provide the JNI library directory pathname. Your path environment variable must contain the metaintegration directory. If not, you need to add it to the path: c:\program files\metaintegration\win32.
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: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.
The Metadata Source and Target Identification window displays as shown in Figure 22-3
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.
This description displays in the progress bar during the transfer process.
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 below on the wizard page. For details, see "Integrating with the Meta Integration Model Bridges (MIMB)".
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
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.
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 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:
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:The Oracle Transfer Wizard Welcome window displays, identifying the steps you perform while using the Transfer Wizard.
The Metadata Source and Target Identification window displays as shown in Figure 22-7.
This description displays in the progress bar during the transfer process.
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".
The Confirmation of Warehouse Builder Transfer window displays.
If any are incorrect, click Back to return to the previous screen and make the necessary changes.
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.
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.
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 Oracle9i 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 Oracle9i 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 Oracle9i 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:
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:
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 the table below 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 Oracle9i 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.
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:
_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.
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.
| Physical Level Name in Warehouse Builder | Time Dimension Levels Created in Oracle9i |
|---|---|
|
_DAY |
Day Level Type |
|
_MONTH |
Month Level Type |
|
_QUARTER |
Quarter Level Type |
|
_YEAR |
Year Level Type |
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 Oracle9i 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 Oracle9i 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:The Warehouse Builder Transfer Wizard Welcome page displays.
From: Oracle9i Warehouse Builder Export
To: Oracle9i OLAP Server
Description: Optional
Figure 22-12 shows the Warehouse Builder Transfer Wizard: Step 1 of 3.
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 alias.
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.
The Transfer Wizard Summary page displays.
The Transfer Bridge generates a script to perform the following:
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.
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:
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 Oracle9i 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.
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:The Add Mapping Transformation dialog displays.
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.
The Attribute Properties dialog displays.
The Expression Builder displays.
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:
Warehouse Builder displays a dialog for you to select a transformation.
Warehouse Builder displays the wrapper procedures: WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_GENUK.
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 alias
`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.
If you deploy the PLSQL scripts directly from the bridge, it will::
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.
Selecting Yes for AW clone will create a MOLAP clone of the ROLAP cube in an AW.
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:
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.
Select Yes for AW clone to create a MOLAP clone of the ROLAP cube in an AW.
Specify a valid directory alias for the directory parameter. The OLAP API enabler scripts will be generated on the server under this directory alias. 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 alias 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 alias 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 alias for the directory parameter. The MV scripts will be generated on the server under the directory alias. 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 alias 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 example below, 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:
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.
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.
| Object in Warehouse Builder | Object Exported to OMG CWM |
|---|---|
|
Project |
Package |
|
Module |
Schema |
|
Dimension |
Dimension |
|
Cube |
Cube |
|
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 22-13 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 22-14 lists the object conversion from PowerDesigner to the Warehouse Builder repository.
| Object in PowerDesigner | Imported into Warehouse Builder |
|---|---|
|
Table |
Table |
|
View |
View |
Table 22-15 lists the object conversion from Oracle9i OLAP server to the Warehouse Builder repository.
| 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.
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 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.
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 the dummy tables as follows using the Table editor:
Physical Name DAY##SHIP (logical name SHIP_DATE)
Column Physical Name SALES_DAY2_FK
Physical Name PROD##PUR (logical name PURCHASE)
Column Physical Name SALES_PROD_FK
Physical Name PROD##TIC (logical name TICKET)
Column Physical Name FLIGHTS_PROD_FK
In order to use a specific level attribute in a hierarchy, the physical name of the level attribute must be one of the following:
For example: a product dimension has one hierarchy p_hierarchy that consists of 3 levels with the following attributes (physical names):
PRODUCT_TYPE:
PRODUCT_CATEGORY
PRODUCT
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
PRODUCT_CATEGORY
PRODUCT
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:The Warehouse Module Editor displays.
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 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:
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 22-17 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 |
|
Cube |
Cube |
|
Table |
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.
You have now created your raw Express database.
Before you look at the data, follow these configuration steps:
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.
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.
| 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.
|
 Copyright © 2001, 2003 Oracle Corporation. All Rights Reserved. |
|