| 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 many wizards, editors and tools that assist you with designing and deploying business intelligence systems. This chapter provides a brief introduction of how to use Warehouse Builder to design and create your target system.
This chapter includes the following topics:
Warehouse Builder enables you to create business intelligence solutions by integrating data from disparate sources. There are many reasons behind building data systems from existing sources. For many, this allows data to be consolidated and transformed into information that can be used to make business decisions. Warehouse Builder makes the process of designing such a system simple by providing easy to use wizards and editors. After you design a system, Warehouse Builder supports several types of deployment targets, including data warehouses, data marts, and business intelligence applications.
To build a target system, use the following steps:
Create a new project to begin designing your target system. Warehouse Builder projects are the largest storage objects within the Warehouse Builder design repository. Projects store and organize metadata definitions for your target system. These definitions include data sources, target warehouse objects, mappings from source to targets, transformation operations, and configuration parameters. These definitions are organized into folders within the project. Typically a project contains related metadata. Creating multiple projects enables you to create your design in an organized manner.
Define source and target modules to begin designing the model of your system. Modules are storage objects within projects that help to organize source and target objects. Source modules contain the metadata from existing source systems from which you are pulling. Target modules contain the metadata you are designing. You can create these modules in either order, source then target or target then source.
Create source modules by selecting data from existing source systems. There are three types of source modules:
Databases
Files
Applications
When you define data sources in Warehouse Builder, you import logical definitions of the sources and objects to be included in the model. These definitions are stored within a project in the design repository. The steps you take to create source modules depend on the type of sources you are using. For more information, see Chapter 4, "Importing Data Definitions".
Define targets by creating a model of the target warehouse you intend to create upon deployment. Use Oracle database warehouse modules to store the metadata definitions. You can create and design new objects, or you can also import definitions from source data locations. For more information, see Chapter 3, "Defining Oracle Data Objects".
When you define a source or target module you must also register them to a location for deployment to be successful. Locations define the type and version, when appropriate, of a source or target module. Locations are saved in the design repository as logical definitions. During deployment, the connection information is requested and saved in the runtime repository.
You must also define connectors for any module that is linked through a mapping to another module. The connector provides a link between the locations of each module. Connector definitions are saved in the design repository as logical definitions and may be used to generate database links during deployment if the two locations, linked by the connector, are located on separate machines. For more information, see "Defining Connectors" and "Defining Runtime Repository Connections".
Configure objects in the target module to define the physical characteristics of the objects. There are many configuration parameters you can use to define your target. You can also use the default values. For information about configuring data objects, see Chapter 5, "Configuring Data Objects".
After defining source and target modules, create data movement and transformation logic. This is also known as ETL logic. The majority of design work you do in Warehouse Builder is defining the ETL logic. You can use the following components to define data movement and transformation in Warehouse Builder:
Transformations
Mappings
Process Flows
Use these objects to define how you want to manipulate source data to create your target system. There is a large set of mapping operators available within the Mapping Editor as well as a full library of transformations. For more information, see Part (UNKNOWN STEP NUMBER) "Designing ETL Objects".
Configure mappings and process flows to define the physical characteristics of the objects. Use the configuration parameters to optimize your target system deployment. You can also use the default values. For information about configuring data movement and transformation objects, see Chapter 11, "Configuring ETL Objects".
After you define source and target modules and define the ETL logic you can validate and generate the code to check for errors prior to deployment.
You can validate all objects and modules to ensure that no definitions are invalid or incomplete. If you find invalid definitions, use the editors to correct the problems. If all definitions are valid, you are ready to configure the modules and their components for deployment. For more information, see "About Validation".
You can generate and view scripts prior to deployment. The type of code generated depends on the configuration parameters defined for the type of target. When you generate the code, you can also save it if you want to deploy it from outside Warehouse Builder. For more information, see "Viewing Generated Scripts".
Deploy the design to the target environment after the target model is complete. You must install a runtime repository and define a Runtime Repository Connection before deployment. Warehouse Builder provides two methods for deployment. You can deploy directly from the main console or you can use the Deployment Manager. You can then execute mappings and process flows from the Deployment Manager. You can also validate and generate objects prior to deployment.
When you deploy objects, you must select a Runtime Repository Connection. The connection you specify defines the Runtime Repository that stores the deployment data. Before you can use deployment, you must install Runtime Repositories and create Runtime Repository Connections in the navigation tree. Runtime Repository Connections describe a connection to the Runtime Repository that you use to manage deployments. For more information about the Runtime Repository, see the Oracle Warehouse Builder Installation and Configuration Guide.
Use the Deployment Manager to assist you in the deployment and execution process. The Deployment Manager provides a complete deployment platform as well as intelligent components that allow you to manage the deployment and future upgrades and execute mappings and process flows immediately. The Deployment Manager also enables you to view data about previously deployed objects in order to make decisions about upgrading the system. For more information about deployment and execution and the Deployment Manager, see Chapter 13, "Deploying Target Systems".
Use the Warehouse Builder Runtime Audit Browser to view reports about deployments and execution of mappings and process flows. When you deploy objects to a target or execute scripts, the runtime repository stores data about each deployment and execution. This data can be accessed in reports using either the Runtime Audit Client-based Browser or the Runtime Audit Portal-based Browser. For more information, see Chapter 14, "Auditing Deployment and Execution".
To log in to your Warehouse Builder client, you need to create a new Warehouse Builder repository schema in your database. The Warehouse Builder repository stores definitions for all the objects created or imported into Warehouse Builder to design your data warehouse. You use the Warehouse Builder Repository Assistant to create a new repository to run the Warehouse Builder client and access the Warehouse Builder metadata.
Optionally, Warehouse Builder enables multiple user schemas to access the same repository. Warehouse Builder provides a repository security and auditing system by implementing the following:
Multiple User Account System: Enables multiple identifiable users to access the same Warehouse Builder repository. The owner of the Warehouse Builder repository schema sets up these users.
Security Registration Service: Enables you to plug-in your customized security PL/SQL implementation package in the Warehouse Builder repository. Warehouse Builder provides the correct access control according to the security rules defined in the PL/SQL package by your organization.
When you use Warehouse Builder, you can:
Use both the multiple user account system and the security registration mechanism to implement security in Warehouse Builder.
Use only the multiple user account system to enable multiple identifiable users to access a central Warehouse Builder repository. Although these users will leave an audit trail, there will be no access control and all users can invoke all operations in Warehouse Builder.
Choose not to implement the security feature or not use the multiple user account system. You can create a separate repository schema for every user or let multiple users use the same repository by sharing the repository login and password.
The following sections show you how to set up multiple user accounts and the security registration mechanism system in Warehouse Builder.
The multiple user account system enables multiple user schemas to access a central Warehouse Builder repository schema using the Warehouse Builder client. This enables the central schema owner to audit and trace changes made to the same repository by different users. However, these repository users should not access the Warehouse Builder repository using other methods such as SQL*PLUS. This is enforced by Warehouse Builder.
Figure 2-1 shows how multiple warehouse users (inside circles) can access the central repositories (inside rectangles).
To enable multiple Warehouse Builder user schemas to access the central Warehouse Builder repository:
Each Warehouse Builder user must have their own Oracle database schema created in the same database instance as the central Warehouse Builder repository.
The central schema owner should register all the prospective Warehouse Builder users to the repository. See "Registering a Warehouse Builder User".
Multiple Warehouse Builder users can now start the Warehouse Builder client session and connect to the same central Warehouse Builder repository.
Tip:
Warehouse Builder users can only access the central Warehouse Builder schema using the Warehouse Builder client, not through SQL*PLUS. This is enforced by Warehouse Builder.To create and register a Warehouse Builder user, the user must have a schema on the same database instance as the central Warehouse Builder repository.
To register a Warehouse Builder user who is not a database user:
The DBA must create a normal database user (User1) using SQL*Plus or Oracle Enterprise Manager.
The DBA must execute the following SQL statement to limit the default roles for USER1.
ALTER USER user1 DEFAULT ROLE NONE;
When a database user is first created, the user's default role setting is ALL. Because the multiple user account system requires that the user's default role cannot be set to ALL, you must change this setting. Otherwise, the following registration step will fail.
The Repository Owner must log in to the repository using SQL*Plus and type the following command:
call WBSecurityHelper.registerOWBUser('User1');
Now the database user User1 becomes a Warehouse Builder user and can access the central Warehouse Builder repository using the Warehouse Builder client.
To register a Warehouse Builder user when a Warehouse Builder user is already a database user:
The Repository Owner must log in to the repository using SQL*Plus and type the command:
call WBSecurityHelper.registerOWBUser('user1');
Now the database user User1 becomes a Warehouse Builder user and can access the central Warehouse Builder repository using the Warehouse Builder client.
If the preceding procedure call fails due to the following error message,
Òuser: User1 has the DEFAULT ROLE set to ALL.The database administrator should use ALTER USER statement to limit the default roles of user: User1.Then register the OWB user again."
then the database administrator should limit the user's default role (it should not be ALL) by using ALTER USER statement.
Warehouse Builder includes utility procedures for the following maintenance tasks:
Update the role password.
Although the repository owner does not explicitly use the protecting password for the role, it is recommended that the repository owner change the password often.
The repository owner must connect to the database containing the repository and issue the following statements from SQL Plus:
Call WBSecurityHelper.updateRolePwd('mewpwd');
where 'newpwd' is the new password used to protect the role chosen by the repository owner. The changed password encryption appears in the OWB_Role_Info table.
Unregister repository users
To unregister a user from a repository, the repository owner must connect to the repository schema and execute the following statement from SQL Plus:
Call WBSecurityHelper.unregisterOWBUser('username');
List all repository users
The repository owner must connect to the repository schema and issue the following statement:
Set serveroutput on; Call WBSecurityHelper.list OWBUsers();
You must use Set serveroutput on because list OWBUsers() uses the DBMS_OUTPUT.put_line for the output to dump the data to the user interface. Otherwise, DBMS_OUTPUT.put_line only dumps the output into an intermediate data structure.
The following steps show you how the multiple user account system works in Warehouse Builder:
Collect the privileges required by the Warehouse Builder client.
During Warehouse Builder installation, run the Warehouse Builder Repository Assistant to automatically create a password protected database role called OWB USER ROLE.
Grant these privileges to the newly created OWB USER ROLE.
Whenever a new Warehouse Builder user is registered, the OWB USER ROLE is granted to that user through the user registration process.
When the Warehouse Builder client session is started, Warehouse Builder allows the users to access the central repository schema by enabling the OWB USER ROLE.
Optionally, the central schema owner can grant the OWB PUBLIC VIEW role to a Warehouse Builder user if they need to access the public views provided by the repository. For example, ÒOWBR_Ó+ repos_name is the role name of OWB public view, where the repository name is the central repository schema name. This role is also created by the OWB Repository Assistant.
Warehouse Builder provides a security registration service that enables you to extend Warehouse Builder to have access control functionality. Using this service, you can plug in your own security service implementation according to your organization's security policy and Warehouse Builder will provide the correct access control on any operation invoked by the user through Warehouse Builder. Currently there are no restrictions on READ privileges.
Warehouse Builder provides a PL/SQL package interface that you can implement if you want to plug in your own security service implementation. This PL/SQL package interface and a dummy implementation is installed in the Warehouse Builder repository. Because the dummy implementation has no intelligence, it grants all users the permission to invoke any operation in Warehouse Builder. If you want to use Warehouse Builder security, you need to replace this dummy PL/SQL package implementation with your own by installing your customized implementation in the repository.
The main procedures defined in the PL/SQL package interface are used as call out procedures by Warehouse Builder to know whether the operation invoked by the login user is acceptable or not. Your PL/SQL implementation enables Warehouse Builder to make the access control decision. The use of this PL/SQL package as a plug-in mechanism has the following benefits:
Security: Because the repository owner alone has the privilege to create or update a PL/SQL package in the repository schema, no other user can substitute the plug-in package.
Flexibility: You can define and enforce your own security policy by implementing this PL/SQL package.
Convenience: You install the PL/SQL package body file in the repository schema through SQL*PLUS and the security mechanism is implemented.
For detailed information on how to implement the security registration service, refer to "Managing Security with PL/SQL".
In Warehouse Builder, there are two types of privileges:
System Privilege: If the user is granted this type of privilege, the user can use it throughout the system no matter what the object under the operation affects. The following is a list of system privileges, also known as service type privileges:
DEPLOY MDL_IMPORT MDL_EXPORT BRIDGE_IMPORT BRIDGE_EXPORT RUNTIME_EXECUTE SNAPSHOT_RESTORE SOURCE_IMPORT
Object Privilege: This privilege can be connected to a particular Warehouse Builder object. You grant the user this type of privilege at an object level, object type level, module folder level (user can invoke the operation on all objects under the module), or project folder level. The following is a list of object privileges:
EDIT DELETE REFERENCE VALIDATE GENERATION VERSION CREATE
For a complete list of objects and their privileges, see "Managing Security with PL/SQL".
Note:
In the current release, all users are granted READ privilege on all objects.After you install your PL/SQL package in the repository, Warehouse Builder will provide the correct access control according to the security policy embedded in your implementation. For example, if a user who has no edit privilege on a project tries to edit the project, Warehouse Builder will stop the user and display an error message.
Please keep in mind the following caveats regarding Warehouse Builder security:
The database administrator must not grant EXECUTE ALL PROCEDURES to the public or any Warehouse Builder user.
The default role in the Warehouse Builder user schema should NOT be set to ALL.
Warehouse Builder users should not be granted any access to the central Warehouse Builder schema by the central schema owner or administrator.
The implementation of the security service package must be correct.
To use Warehouse Builder you must either have access to an existing Warehouse Builder Repository or create a new one using the Repository Assistant. You need to have the connection information available when you open Warehouse Builder. For more information about creating a Warehouse Builder Repository, see the Oracle Warehouse Builder Installation and Configuration Guide.
To start Warehouse Builder using Windows NT/2000/XP:
From the Start menu, select Programs, then Oracle Database Developer Suite, then Warehouse Builder, and then OWB Client.
The Warehouse Builder logon dialog displays.
Click Connection Info.
The Connect Information dialog displays.
Type the following connection information for the machine that contains the Warehouse Builder repository: Host Name of the database server, Port Number of the database instance, and Oracle Service Name.
After you specify the connection information, it remains the same whenever you open Warehouse Builder. To log on to a different repository, you must open this dialog and edit the connection information.
Click OK.
The Warehouse Builder client saves the connection information and the logon dialog displays again.
Enter the User Name and Password of the Warehouse Builder repository and click Logon.
If the Oracle8i/9i database instance is inactive, Warehouse Builder will display a connection error message.
The Warehouse Builder console opens and displays the contents of the repository in the navigation tree. If this is a newly created repository, the default project MY_PROJECT displays. Figure 2-2 shows the Warehouse Builder console with the default project called MY_PROJECT.
If the login user is not a Warehouse Builder repository owner, then Warehouse Builder displays the Select Repository dialog as shown in Figure 2-3. Choose the repository you want to access and click OK.
If you access a repository that is used by multiple users, you may want to synchronize periodically in order to update the console with what is stored in the repository. For more information, see "Supporting Multiple Users".
To start Warehouse Builder using UNIX:
Start a shell.
Navigate (cd) to <OWB ORACLE HOME>/owb/bin/unix
For example: /private/home/OWB904/owb/bin/unix
Table 2-1 UNIX
| Warehouse Builder Component | Invoke |
|---|---|
|
Warehouse Builder Client |
owbclient.sh |
|
Warehouse Builder Browser Assistant |
browserasst.sh |
|
Warehouse Builder MDL File Upgrade Utility |
mdlconvertui.sh |
|
Warehouse Builder Repository Assistant |
reposasst.sh |
|
Warehouse Builder Runtime Assistant |
runtimeinst.sh |
|
Warehouse Builder OMB Plus |
OMBPlus.sh |
To close Warehouse Builder:
From the Project menu, select Exit.
The Commit Confirmation dialog displays if you have not already committed your changes.
Click Yes to commit any changes you have made. Click No to discard changes.
Warehouse Builder closes and ends the session.
You can commit changes to the design repository at any time during a Warehouse Builder session. To commit changes during a session, go to the console window and click the Commit icon on the toolbar. Warehouse Builder displays the Commit Confirmation dialog. Click Yes to commit the changes.
You can also commit changes at the end of each session. To commit changes upon exiting Warehouse Builder, click Yes when the Commit Confirmation dialog displays.
Multiple users can access the Warehouse Builder Repository at the same time. Warehouse Builder ensures that only one user has write privileges to an object and all other users have read-only access. Once a user has write access to any object, Warehouse Builder maintains a lock on the object for the duration of the transaction. Warehouse Builder releases the lock when the user commits the changes or performs a rollback and closes all editors associated with the object.
Note:
If you validate, generate, or deploy code at any time during a Warehouse Builder session, multiuser locking remains in effect until you commit or rollback your changes.You lock an object when you open the editor, property sheet, or dialog for the object. You acquire a folder-in-use lock when you access an object in a Warehouse Builder hierarchy. This lock prevents other users from deleting a higher object in the hierarchy. For example, if you are editing an object in a module, other users cannot delete the module.
To enter read/write mode for an object and lock the object, you open an editor, property sheet, or dialog. By default, you access objects in read/write mode.
Editors, property sheets, and dialogs indicate in the title bar that the object is in read/write mode, as shown in Figure 2-4.
To end the read/write mode for an object and unlock the object for others, do one of the following:
Close the object editors and commit or rollback the changes.
Close or cancel out of the property sheet, editor or wizard you are using without making any changes.
Exit Warehouse Builder.
If you attempt to open an object locked by another user, Warehouse Builder displays a message that prompts you either to cancel the request or access the object in read-only mode.
If you choose to continue in read-only mode, the editor displays ÒRead onlyÓ in the title bar. You can move objects around on the editor canvas or expand and collapse the object icons. You cannot edit the object in read-only mode. If you try to edit an object in read-only mode, an error message dialog displays. On a property sheet opened in read-only mode, the OK button is disabled.
When a Warehouse Builder object is locked by a user, other users can view the changes by using synchronize only after the changes have been committed.
Warehouse Builder includes the following types of synchronization:
Automatic Synchronization: Warehouse Builder automatically synchronizes objects when you access them in read/write mode.
Synchronization on command: You can synchronize objects by selecting Synchronize from the Project menu or by pressing F5. This method displays changes to objects that do not show up with automatic synchronization. You can also use the synchronize button on the toolbar. This is useful for synchronizing the tree and synchronizing when you are in read-only mode.
After you log on to a repository in Warehouse Builder, the console displays the contents of the repository in the navigation tree, as shown in Figure 2-5. There are several components that make up the Warehouse Builder console.
The main components of the Warehouse Builder console include:
Warehouse Builder enables you to organize objects into projects within a navigation tree, as shown in Figure 2-6. The navigation tree is similar to a file system with all of the objects organized into expandable folders. If you have multiple projects in a repository, all projects initially display compacted. You can expand one project at a time. You must commit or rollback when switching between projects.
Table 2-2 describes the main folders in the navigation tree. You can expand these folders to view their contents and create objects.
Table 2-2 Navigation Tree Folders
| Folder | Description |
|---|---|
|
Collections |
Groups of objects within a project. Use Collections to organize the contents of your project and to export metadata to other tools using the Warehouse Builder Transfer Wizard. |
|
Databases |
Contains all database objects. These objects are stored within modules. A modules contains either source or target database objects. At the highest level, databases are split into Oracle and non-Oracle databases. |
|
Files |
Contains file-based data objects stored within modules. |
|
Applications |
Contain application-based data objects for packaged applications such as SAP. Objects are grouped into modules. |
|
Process Flows |
Contains process flows grouped in modules. |
|
Public Transformations |
Contains public and pre-defined transformation libraries. |
|
Runtime Repository Connection |
Contains runtime repository connection information. |
The toolbar is located along the top of the console under the menu bar. The toolbar provides shortcuts to frequently used Warehouse Builder tasks. Table 2-3 lists the shortcuts in the order they appear on the toolbar. Click the icon to run the task. All toolbar tasks can also be run from the menu bar. Table 2-2 shows the Warehouse Builder console toolbar.
Table 2-3 shows the icons available on the Warehouse Builder console toolbar and a description of their functions.
Table 2-3 Toolbar Icons
| Icon | Task | Description |
|---|---|---|
 |
Commit |
Commits changes to the database. You can select this icon at anytime. |
 |
Synchronize |
Synchronizes the objects displayed in the navigation tree with the objects in the repository. This is useful if you are working in a multiple user environment. It updates the objects displayed in the console with changes that other users have committed. You can select this icon at anytime. |
 |
Find |
Searches for an object within the currently expanded project. The Object Find dialog displays and you can type in the name or part of the name of an object. You can select this icon at anytime. |
 |
Properties |
Select an object in the navigation tree and click this icon to display the object property sheet. This icon is only available if the selected object has properties. It is greyed out if you select a folder or label. |
 |
Configure |
Select an object and click this icon. The configuration parameters dialog displays and can be used to define or edit parameters. This icon is only available if the selected object has configuration parameters. It is greyed out if you select a folder or label. |
 |
Validate |
Select an object or set of objects and click this icon. Warehouse Builder validates the object and displays the validation results. This icon is only available if the selected object can be validated. |
 |
Generate |
Select an object or set of objects and click this icon. Warehouse Builder generates the object and displays the generation results.This icon is only available if the selected object can be generated. |
 |
Deploy |
Select an object or set of objects and click this icon. Warehouse Builder prompts you to select a runtime repository connection for the deployment if you have not yet selected one. This icon is only available if the selected object can be deployed. |
 |
Help |
Click this icon to view the Oracle Warehouse Builder User's Guide in the online help navigator. You can select this icon at anytime. |
Warehouse Builder has a set of preferences that you can use to configure the client environment. To open the preferences, select Project from the menu bar and then Preferences.
Table 2-4 describes the types of preferences in the Preferences window. These are described in detail in the following sections.
Table 2-4 Preferences
| Preference Type | Description |
|---|---|
|
General |
Use to verify or define color and wizard welcome page preferences. Also use to define locale and MLS language. |
|
Naming |
Use to set naming preferences. |
|
Message Log |
Use to set log file options such as file location, file size, and types of messages saved to any log file. The log file contains messages relating to your design process. |
|
Utilities |
Use to provide information about the utilities you have selected for the Utility Tools menu. |
|
Browser |
Use to define the connection to the web server that is hosting the Warehouse Builder Design Browser. This enables you to access metadata reports from the within the client. |
|
Clipboard/Recycle Bin |
Use to set preferences for both the Recycle Bin and the Clipboard. |
Use the General tab to define color scheme, define wizard welcome page preferences, set locale and choose display language.
Use the drop-down menu to choose a color scheme for the Warehouse Builder console. The default color is Titanium.
Select the appropriate radio button to display or hide wizard welcome pages. Every wizard in Warehouse Builder starts with a welcome page that summarizes the steps you follow to complete that task.
Display Welcome Page on all Wizards: Indicates you want the wizard welcome page to display when you start a wizard in Warehouse Builder.
Hide Welcome Page on all Wizards: Indicates you want to skip the wizard welcome page when you start a wizard in Warehouse Builder.
Click Locale Setup and then, from the drop-down list, select the language you want the client text to display. This selection does not define the character set of your repository; it only affects the text and menu options on the client user interface. The repository character set is determined by the database. Warehouse Builder prompts you to restart the computer in order to use the new language setting.
Click Choose Language to change the language in which you edit the business name and description of objects created in Warehouse Builder. The business name is the name you assign an object when you create object using business naming mode. After you have changed the MLS display language, you can only change business names and descriptions for pre-existing objects. You must set the MLS language back to the base language in order to create new objects.
The supported languages must have already been defined during repository installation or upgrade in order to use this feature. For more information, see the Oracle Warehouse Builder Installation and Configuration Guide.
Base Language: This is the language of your Warehouse Builder repository.
MLS Display Language: Choose the language in which you want to edit or display the business name and description of an object in Warehouse Builder. The list of languages available to you are the languages you selected while creating or upgrading your repository.
For more information about MLS, see the Oracle Warehouse Builder Installation and Configuration Guide.
This page enables you to set naming preferences by selecting whether you want to view objects in business or physical name mode. You can also set up how you want to propagate object name changes.
Warehouse Builder maintains a business and a physical name for each object stored in the repository. A business name is a descriptive logical name for an object.
When you generate DDL scripts for a named object, the physical names are used. Physical names must conform to the syntax rules for basic elements as defined in the Oracle Database SQL Reference.
Names must be unique within their category:
Module names must be unique within a project.
Warehouse object names must be unique within a warehouse module. This includes the names of tables, dimensions, cubes, mappings, materialized views, sequences, views and indexes.
Transformation names must be unique within a transformation package.
You can create a business name for an object or change the business name of an existing object when Warehouse Builder is in business name mode. Warehouse Builder editors, wizards, and property sheets display the business names of objects in this mode.
A business name must conform to these rules:
The name must be unique within its category.
All source modules reflect the case of the imported source and are subject to the double-quotes rules as defined in the Oracle Database SQL Reference.
Copy operations from a source to a target in a mapping do not propagate case.
When you create a business name, Warehouse Builder generates a valid physical name that resembles the business name. If you create a business name that duplicates an existing physical name, Warehouse Builder appends an underscore and a number in order to create a unique name.
You can create a physical name for an object or change the physical name of an existing object when Warehouse Builder is in the Physical name mode. Warehouse Builder editors, wizards, and property sheets display physical names of objects in this mode. Physical names are converted to uppercase.
Contain no more than 30 characters.
Conform with the basic syntax rules for schema objects defined by the Oracle Database SQL Reference.
Note:
A collection can have a physical name containing up to 200 characters.Warehouse Builder prevents you from entering an invalid physical name. For example, you cannot enter a duplicate name, a name with too many characters, or a name that is a reserved word.
To create or change a business name for an object, Warehouse Builder must be in business name mode. To create or change a physical name for an object, Warehouse Builder must be in physical name mode.
The default naming preferences for Warehouse Builder are:
Icons for the name mode and name propagation settings are located in the lower-right corner of the editors. These icons indicate the current setting.
To set the name mode:
From the Naming tab, select physical or business name mode.
You can switch the naming mode at any time during a session.
Select how the names propagate.
Click OK.
Warehouse Builder saves your naming preferences across sessions. The name mode preference is stored in a file on the client workstation. If you use Warehouse Builder from another workstation, your preferences may be different.
Use the Message Log tab to set Warehouse Builder log file options such as file location and name, file size, and types of messages saved to the log file. These files store Warehouse Builder design operations and errors. By default a message log is saved to the default location.
Log File Path: Type the location or use the Browse button to select the location where you want to save the log files. The default location is owbhome\owb\bin\admin.
Log File Name: Type in the log file name. Do not include a file extension.
Max Size: Indicate the maximum file size for the log file(s). There are two log files: <logfilename>.0, and <logfilename>.1. When the maximum size of the first log file <logfilename>.0 is reached, Warehouse Builder starts writing to the second, <logfilename>.1. When the maximum size of the second one is reached, Warehouse Builder starts overwriting the first.
Clear Log File: Use this button to erase the contents of the log files.
Select the following options to indicate which messages you want to capture:
Log Error Message: Writes all error messages to the log file.
Log Warning Message: Writes all warning messages to the log file.
Log Info Message: Writes all informational messages to the log file.
Use the Utilities tab to add, update, or remove utilities. The utilities are available from the Tools menu.
To add a utility to the Tools menu:
Select the Utility tab and replace the fields with the following information:
Name of the utility.
Location of the utility. Click Browse to search for program folders and files.
Location of the icon for the utility. Click Browse to search for icon folders and files.
Description of the utility.
Click Add.
Click OK.
To update the configuration of a utility:
From the Utilities sheet, select the utility name from the Contents list.
Warehouse Builder displays the configuration information.
Modify the following configuration details:
Name of the utility.
Location of the utility. Click Browse to search for program folders and files.
Location of the icon for the utility. Click Browse to search for icon folders and files.
Description of the utility.
Click Update.
Click OK.
Use the Browser tab to select which version of the Warehouse Builder Browser you want to use when you invoke metadata reports from within the client console. You can choose the Client version, or the Oracle9iAS version.
If you choose the Warehouse Builder Browser Client version, you must provide the Oracle HTTP Server Port. This requires that you run the HTTP Server on the same machine as the Warehouse Builder Repository that you are accessing. Contact your DBA to obtain your Oracle HTTP Server Port:
If you choose to use the Oracle Portal version, you must establish a connection with Oracle Portal which is installed with Oracle9iAS. Enter the following connection details to use this option:
Portal Host Name: Name of the system where you installed Oracle Portal.
Portal Port Number: Default:7778.
Portal DAD: Portal Database Access Descriptor chosen during Oracle Portal installation.
Browser Schema Name: The username provided during the Warehouse Builder Browser installation using the Browser Assistant.
Use the Clipboard/Recycle tab to determine when objects are removed from the Clipboard and Recycle Bin.
To preserve the objects in the Clipboard across sessions, you must clear the Clear Clipboard on exit box.
The Clipboard stores the object you last cut or copied from the Warehouse Builder navigation tree. The Clipboard is stored on the Warehouse Builder client. Set this preference to indicate whether you want objects stored for use only during the current Warehouse Builder session or across all Warehouse Builder sessions using this client machine. The default setting is to empty the Clipboard each time you log out of a session.
To preserve the objects in the Recycle Bin across sessions, you must clear the Empty Recycle Bin on exit box.
The Recycle Bin stores all the objects you delete from the Warehouse Builder navigation tree. The Recycle Bin is stored on the Warehouse Builder client. Set this preference to indicate whether you want objects stored for use only during the current Warehouse Builder session or across all Warehouse Builder sessions using this client machine. The default setting is to empty the Recycle Bin each time you log out of a session.
If you choose to persist the deleted objects in the Recycle Bin, you can restore them to the repository during another session. Commit and Rollback actions do not affect objects in the Recycle Bin.
When you begin using Warehouse Builder, begin by creating a Project. After you create a project, you can create all the other Warehouse Builder objects. Warehouse Builder contains wizards, object editors, property sheets, and object finding tools that assist you use in designing your business intelligence system. Use these components to aide you in the design process.
When you install a Warehouse Builder design repository, Warehouse Builder creates a default project called MY_PROJECT with a set of pre-defined public transformations. The default project is required for the initial logon sequence. You can delete MY_PROJECT after you create other projects. For information about installing a Warehouse Builder design repository, see the Oracle Warehouse Builder Installation and Configuration Guide.
When you begin using Warehouse Builder, create new projects to manage your design work.
To create a new project:
From the Project menu, select Create Project.
The welcome page for the New Project Wizard displays.
Click Next.
The Name page displays.
Enter the project name and an optional description, and click Next.
The Version Properties page displays.
Enter the optional version label and then click Finish.
The new project is created and added to the bottom of the navigation tree and Warehouse Builder does an implicit commit and saves the new project in the design repository.
You can now use wizards to create objects within the project. Expand the project and select an object type to begin.
Deleting a project is not as simple as deleting any other object in Warehouse Builder. Since projects are the main design component in Warehouse Builder, this feature is designed to protect them from being deleted unintentionally.
The following guidelines apply:
The currently active or expanded project cannot be deleted.
The last remaining project in a repository cannot be deleted.
Each design repository must contain at least one project.
To delete a project:
Select and expand any project in the navigation tree other than the project you want to delete.
The project you expand becomes the current active project.
Select, but do not expand the project you want to delete.
From the Edit menu, select Delete. You can also right-click the project and select Delete.
The Deletion Confirmation dialog displays. You can choose to delete the object, or put the object in the recycle bin.Warehouse Builder does not implicitly commit the deletion of a project as it does with the creation of a new project.
Warehouse Builder provides wizards for creating all objects. To open a wizard, right-click an object type and select Create. The first page of each wizard is the welcome page, as shown in Table 2-4. This page lists the number of pages in the wizard and describes the task performed on each page.
You can configure wizards to skip the welcome pages by clearing the Show this page the next time check box. To re-enable the welcome page, open the Preferences dialog from the Project menu and check the Display Welcome page on all wizards box.
To navigate through the pages of a wizard, click the Next and Back buttons. Click Cancel to exit without saving your work. You can move around a page using the mouse or using the Tab key. Use Control-Tab to move the cursor out of a long description text box.
For help on a specific topic in a wizard, click Help at the bottom of the wizard page.
After you create modules and objects, you can edit them using editors and property sheets. Object editors provide a separate window with menu items and a toolbox to edit the object. To display an editor, right-click the object and select Editor. Figure 2-17 shows an example of a Dimension Editor.
Selecting the Property icon from the console toolbar displays the property sheet for the selected object. Each object stored in a project has a property sheet that defines the object. You can update the stored definition of an object by editing its property sheet. Property sheets vary by object. Figure 2-18 shows an example of a property sheet for a dimension.
To enhance the metadata for your unique needs, Warehouse Builder enables you to create additional properties for objects. You can create user-defined properties for any object type on the navigation tree. To create user-defined properties, use the Oracle MetaBase (OMB) Scripting Language, OMB Plus. This is the scripting utility packaged with Warehouse Builder. Create user-defined properties to store additional business, design, or versioning information for objects.
When you create user-defined properties, you must prefix them with UDP_. This naming convention enables you and Warehouse Builder to distinguish core properties from customized ones. If you try to create a property without following the naming convention, an error message directs you to use the UDP_ prefix. You can view and populate the properties you created either in the user interface Properties sheets for the corresponding objects or directly in OMB Plus.
For instance, you can add a property called UDP_BUSINESS_PURPOSE to table objects so that you can describe the business purpose of each table. The property then appears in the user interface as ÒUDP Business PurposeÓ when you open the Properties sheet for any table. You can populate the property with a description of the business purpose either in the user interface Properties sheets or directly in OMB Plus.
Tip:
Try to finalize any user-defined properties you plan to create before defining data objects. This enables you to populate the user-defined properties as you go along instead of having to retroactively edit objects later in the design process.For the scripting commands and arguments related to user-defined properties, consult the Oracle Warehouse Builder Scripting Reference .
Objects stored in a Warehouse Builder repository are organized in a navigation tree. To find an object in the repository, you can click Find and type the name or part of the name. Use the asterisk (*) as a wildcard character to match one or more characters. Figure 2-19 displays the Object Find dialog.
Warehouse Builder stores all metadata for the target system you design in the Warehouse Builder design repository. Before you begin using Warehouse Builder you must already have an installed repository. When you open Warehouse Builder, the navigation tree displays the contents of the design repository. To manage your metadata, Warehouse Builder enables you to run reports on the metadata including lineage and impact analysis, export metadata from one repository and import it into another, and exchange metadata between different tools.
Access reports on all of the metadata stored in your design repository using the Warehouse Builder Design Browser. You can access metadata reports from within the Warehouse Builder client, or through Oracle Portal without even installing the client. For more information, see Chapter 17, "Metadata Browsing and Reporting".
Use the Metadata Loader (MDL) to populate a new repository as well as transfer, update, or restore a backup of existing repository metadata. You can import and export metadata for any type of object on the navigation tree using the MDL utility. Access MDL through the Warehouse Builder client or through the OMB Plus scripting interface. For more information, see "Importing and Exporting Metadata Using the Metadata Loader".
Create collections and use the Warehouse Builder Transfer Wizard to exchange metadata stored in the Warehouse Builder design repository.
Collections are groups of objects within projects that you can define to organize objects in Warehouse Builder. When you create a collection, you create shortcuts pointing to objects already existing in the project. These shortcuts provide quick access to the base object and allow you to make fast changes to it.
Use the Warehouse Builder Transfer Wizard to synchronize, integrate, and use metadata stored in the design repository in a variety of business intelligence tools. You can import and export metadata using a bridge. You can also exchange metadata with OMG files, Oracle Discoverer, Oracle Express, ERwin, Predesignate, and Oracle Database OLAP Server. For instructions for using the Transfer Wizard, and for additional information on this topic, see Chapter 22, "Integrating Warehouse Builder Metadata with Other BI Products".
