|Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter includes additional and optional steps that you may take when initially designing your data system. This chapter covers the following topics:
Projects are the largest storage objects within a Warehouse Builder workspace. Projects store and organize related metadata definitions. You should include all the objects in a project that you think can or will share information. These definitions include data objects, mappings, and transformation operations. The definitions are organized into folders within the project. By creating multiple projects, you can organize the design and deployment of a large system.
To create a project:
In the Project Explorer, right-click a project, such as
MY_PROJECT, and select New.
The Create Project dialog box is displayed.
Click Help for additional instructions.
Each Warehouse Builder workspace has a default project called
MY_PROJECT. You can rename
MY_PROJECT, or you can delete it after you create other projects. However, a workspace must contain at least one project at all times.
Because projects are the main design component in Warehouse Builder, some restrictions are enforced to prevent you from deleting them unintentionally. You cannot delete:
The currently active or expanded project.
The only project in a workspace.
To delete a project:
In the Project Explorer, collapse the project that you want to delete. You cannot delete the project when it is expanded.
Select and expand any other project.
Highlight the project you want to delete and, from the Edit menu, select Delete.
Right-click the project and select Delete.
The Warehouse Builder Warning dialog box provides the option of putting the project in the recycle bin.
Click OK to delete the project.
To delete an object, right-click on the object in the Design Center and select Delete.
Warehouse Builder imposes few restrictions on deleting objects. For example, you cannot delete the last remaining project in a workspace. Similarly, you cannot delete a location with first unregistering it as described in "Deleting Locations".
You can choose to move deleted objects to the recycle bin. This allows you to subsequently restore deleted objects back to the location from which they were deleted. To move deleted objects to the recycle bin, set the Recycle Deleted Objects environment preference as described in "Environment Preferences".
It is possible, however, to delete objects upon which other objects are dependent. To protect important objects from being deleted, you can enable metadata security as described in Oracle Warehouse Builder Installation and Administration Guide.
Warehouse Builder enables you to retrieve objects that were moved to a recycle bin. You can retrieve an object only if you did not empty the recycle bin after the object was moved into it. For information about emptying the recycle bin each time you exit the Design Center, see "Environment Preferences".
To retrieve deleted objects, select Recycle Bin from the Tools menu. The Warehouse Builder Recycle Bin is displayed. Click Restore to restore the deleted object from the recycle bin and move it back to the location from which it was deleted.
Note that any references to the restored object are not restored. You must manually create the references again. For example, you create two table called EMP and DEPT. The table DEPT has a foreign key reference to the table EMP. You delete DEPT and subsequently restore it from the recycle bin. the foreign key reference to EMP is not restored. You must recreate this manually.
Warehouse Builder provides a set of user preferences that enable you to customize your user interface environment. To set user preferences, select Tools and then Preferences from the Design Center menu. The Preferences dialog box that is used to set preferences is displayed.
The Preferences dialog box contains two sections. The section on the left lists the categories of preferences. The section on the right displays the preferences and their corresponding values. Click a category on the left panel to display the preferences it contains and its value in the right panel.
Warehouse Builder enables you to set the following types of preferences:
The Appearance category contains the Locale preference. Use the Locale list to set the language you want the client text to display. This list displays the language options. Warehouse Builder prompts you to restart the computer in order to use the new language setting.
The Locale 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.
Use the Control Center Monitor category to set preferences that control the display of components in the control center. When you use the control center to deploy or execute objects, the Job Details window displays the results of deployment or execution. The Control Center Monitor preferences enable you to control the display of components in the object tree of the Job Details window.
Note:Warehouse Builder displays the Job Details window only if you select the Show Monitor preference under the Process node of the Deployment preferences category.
If this option is not selected, view the Job Details window by double-clicking the row representing the deployment or execution job in the Control Center Jobs panel of the Control Center.
You can set the following Control Center Monitor preferences:
Show Project: Select this option to display the project name in the Job Details window object tree. When this option is selected, the object tree displays a node for the project name. All the objects are displayed under the project node.
Show Module: Select this option to display the name of the module to which the object being deployed or executed belongs in the Job Details window. When this option is selected, the object tree displays a node for the module. Expand the module node to view the object details.
Show Location: Select this option to display the location name in the object tree of the Job Details window.
Show Action: Select this option to display the action performed on the object in the object tree of the Job Details window. The actions performed include Create, Drop, Deploy, and Upgrade.
Show Type: Select this option to display the type of object in the object tree of the Job Details window. When you select this option, a node is displayed for the type of object and the objects are listed under the respective nodes.
Figure 3-1 displays the object tree of the Job Details window in which the following Control Center Monitor preferences were selected: Show Project, Show Module, Show Action, and Show Type.
Use the Data Profiling category to set the preferences for data profiling. This section contains the following preferences:
Data Rule Folder Name: Use this option to set the name of the folder that contains the data rules as a result of data profiling.
Default Profile Location: Use this option to set the default location that is used to store the data profiling results. You can override this setting by selecting a different location as your profile location. In the Data Profile Editor, from the Edit menu, select Properties. Use the Data Locations tab to change the default profile location.
The Deployment category enables you to set deployment preferences such as displaying the deployment monitor, prompting for execution parameters, and showing completion messages. This enables you to control some of the popup windows that are displayed by the Control Center Manager during object deployment.
Deployment preferences are divided into two sections: Process and Tools. Expand the Deployment node in the Preferences dialog box. Two nodes called Process and Tools are displayed. Click the node for which you want to set preferences.
Set the following deployment options in this section:
Allow Undo/Redo: Select this option to allow the user to undo and redo a deployment upgrade job. You can undo or redo a deployment upgrade job using the Job Details window. To display the Job Details window for a job, double-click the job in the Control Center Jobs panel of the Control Center Manager.
Pause After Compile: Select this option to pause deployment after script generation. This means that you must explicitly deploy an object after it is successfully generated.
Prompt for Commit: Select this option to prompt the user to commit design time changes before a deployment. When you deploy objects from the Design Center, if there are any unsaved design changes, Warehouse Builder prompts you to save these changes by displaying the Warehouse Builder Warning dialog box. Click Save to commit unsaved design changes. Click Cancel to terminate the deployment.
If you do not set this option, Warehouse Builder saves any design changes before the deployment job.
Prompt for Job Name: Select this option to prompt the user for the name of a deployment job. When this option is not selected, Warehouse Builder assigns a default job name.
Prompt for Execution Parameters: Select this option to prompt the user for the values of execution parameters. If you do not select this option, Warehouse Builder uses the default value of parameters during the execution. The user is not prompted to provide the parameter values.
Show Monitor: Select this option to display the Job Details window when you deploy or execute an object. This dialog box displays details of the objects being deployed, deployment progress, and deployment status.
Show Deployment Completion Message: Select this option to display an alert indicating that the deployment job has completed.
Show Design Center Deployment Job: Select this option to display the Control Center Jobs dialog box when you deploy an object from the Design Center. The Control Center Jobs dialog box, which is similar to the Jobs panel of the Control Center Manager, contains the Deployment, Execution, and Scheduled tabs. Use this option to view the status of a deployment job while deploying using the Design Center.
Set the following deployment options:
Show Monitor Tree: Select this option to show the Job Details window when you perform a deployment or execution.
Show Monitor Results: Select this option to display the deployment or execution results in Control Center Manager.
Show Monitor Logfile: Select this option to display the log file in the Control Center Manager.
The Environment category enables you to set generic preferences that control the client environment such as displaying welcome pages for wizards and recycle bin preferences.
Set the following environment preferences:
Personality: For the standard installation, set the value of this preference to Default. For a customized installation, this preference tailors the types of objects shown in the Project Explorer tree. Oracle recommends that you change the value of this preference, from Default, only after discussion with your Oracle system administrator. This feature is reserved for future use.
Allow Optimize Repository Warning on Startup: Select this option to collect schema statistics when you log in to Warehouse Builder. Collecting schema statistics improves repository performance. If this option is selected, at log on, Warehouse Builder determines if statistics must be gathered for the repository schema. If statistics must be gathered, a warning dialog box is displayed asking if you want to gather statistics now. Click Yes to collect schema statistics and optimize the repository.
If you do not select this option, you can still collect schema statistics from the Design Center by selecting Optimize Repository from Tools menu.
Hide All Wizard Welcome pages: Select this option to hide the welcome page of all wizards. Every wizard in Warehouse Builder starts with a Welcome page that summarizes the steps to follow to complete that task. To display the Welcome page for all wizards, deselect this preference.
Show Delete Confirmation Dialog Box: Select this option to display a dialog box that asks for a confirmation before deleting an object. When this option is selected, if you delete an object, the Warehouse Builder Warning dialog box is displayed. Click Yes to delete the object. Click No to cancel the Delete operation and retain the object.
Recycle Deleted Objects: Select this option to move deleted objects to the recycle bin. If this option is not selected, any objects you delete are lost and you have no way of recovering them.
Empty Recycle Bin on Exit: Select this option to empty the contents of the recycle bin when you exit the Warehouse Builder client. Deselect this option to save the recycle bin objects across sessions.
The Generation/Validation category enables you to set preferences related to generation and validation of Warehouse Builder design objects. Use these preferences to control what is displayed in the Generation Results window or Validation Results window. These dialog boxes are displayed when you generate or validate an object from the design center. You can set the following preferences:
Show Project: Select this option to display the project node in Validation Results window or the Generation Results window.
Show Module: Select this option to display the module node in Validation Results window or the Generation Results window.
Show Location: Select this option to display the location node in Validation Results window or the Generation Results window.
Show Action: Select this option to display the action node in Validation Results window or the Generation Results window.
Show Type: Select this option to display the type node in Validation Results window or the Generation Results window.
The Logging Preferences category enables you 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. By default a message log is saved to the default location. Following are the logging preferences that you can set:
File Path: Represents the location where the log files are saved. Type the complete path or use the Browse button to select the location. The default location is
File Name: Represents the name of the log file. Do not include a file extension when you specify a file name.
Maximum Size (Kb): Indicate the maximum file size for the log file(s) in KB. 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.
Log Error Messages: Select this option to write all error messages to the log file.
Log Warning Messages: Select this option to write all warning messages to the log file.
Log Information Messages: Select this option to write all information messages to the log file.
The Naming Preferences category 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.
Set the following naming preferences:
Naming Mode: Select whether to display objects using their physical or business names.
Propagate Name Changes: Propagate Name Changes from the current naming mode to the other naming mode.
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 Language 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.
Business Name Mode 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.
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 Language 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.
Physical Name Mode 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 Language 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.
Setting the Name Mode 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 as follows:
Propagation: The default propagation setting is to propagate physical name to business name.
Icons for the name mode and name propagation settings are located in the lower-left corner of the editors. These icons indicate the current naming preference setting.
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.
Only administrators can edit the security preferences. Administrators can set the following preferences:
This option determines whether or not location passwords are persisted across Warehouse Builder design sessions.
By default, this option is deselected, which is the more secure option. Warehouse Builder retains location passwords for the length of the design session only. That is, the first time you start tools such as the Data Viewer or Debugger, you must enter the appropriate location passwords.
If selected, Warehouse Builder persists encrypted versions of location passwords in the workspace. You can start tools such as the Data Viewer and Debugger without entering passwords each time.
See Also:Oracle Warehouse Builder Installation and Administration Guide, for more information about the encryption methodology.
This preference determines whether or not the location passwords users enter during the design phase can be shared with other users. For example, assume that user
Dev1 designs mapping
MAP1. To access the sources and targets for this mapping,
Dev1 defines the locations to each source and target including a username and password. When other users subsequently attempt to execute
MAP1 or view data associated with it, the Share Location Password During Runtime preference determines whether or not each user must enter the password each time in the Design Center or the first time in the Control Center.
Share Location Password During Runtime works in conjunction with Persist Location Password in Metadata. The most secure mode, and therefore the default behavior, is for both options to be deactivated. In this case, each user including
Dev1 must enter their password once for each Design Center session and the first time they attempt to use that location in the Control Center. Depending on your security requirements, you may want each user to define their own location for a given source or target
If both Share Location Password During Runtime and Persist Location Password in Metadata are activated, then any user can access a schema given that any user previously defined the location. Therefore, user
Oper2 can execute
MAP1 given that
Dev1 or any other user previously defined the location with valid credentials.
Specify the default security policy to be applied. Minimum security allows all users full control over objects any newly registered user creates. Maximum security, however, restricts access to the newly registered user that created the object and Warehouse Builder administrators.
This setting is not retroactive. That is, if you change this setting in an existing Warehouse Builder implementation, the setting does not affect existing users and existing objects. You must change the security on existing objects manually.
See Also:Oracle Warehouse Builder Installation and Administration Guide, for more information about manually changing the security settings.
Collections are structures in Warehouse Builder that store the metadata you want to export to other tools and systems. Collections enable you to perform the following tasks:
Organize a large logical warehouse
Validate and generate a group of objects
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. You can use a collection 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 his own collection of frequently used objects. The users can also add the same objects (such as mappings, tables, or process flows) to their separate collections.
When 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 Create Collection Wizard to define a collection.
To define a new collection:
Select and expand a project node on the Project Explorer.
Right-click the Collections node and select New.
Warehouse Builder displays the Welcome page for the Create Collections Wizard. This page lists the steps to create a collection. Click Next to proceed.
Provide information on the following pages of the Create Collection wizard:
Use the Name and Description page to provide a name and an optional description for the collection. The name should be unique within the module. In physical naming mode, type a name between 1 to 200 characters. Spaces are not allowed. In logical mode, the maximum number of characters is 200 and spaces are allowed.
The Contents page enables you to select the data objects that you want to refer to in the collection. Use the following steps:
Select and expand the project node in the left panel.
The wizard displays a list of objects you can add to the collection.
Select objects from Available section in the left panel.
Use the Ctrl key to select multiple objects. You can select objects at the object level or the module level. For example, under the Files node, you can add a specific file or add all the files in a given flat file module.
If you add a module or another collection, Warehouse Builder creates references to the module or collection and also creates references to objects contained in the module or collection.
Click the right arrow.
The wizard displays the list of objects under the Selected section on the right panel. You can remove objects from the list by selecting objects and clicking the left arrow.
The Summary page displays the objects selected for the collection. Review the objects and click Back to make changes to your selections. Click Finish to complete the collection definition. Warehouse Builder creates the collection and adds it to the Project Explorer.
Use the Edit Collection dialog box to edit a collection. You can perform the following actions when you edit a collection definition:
Rename the collection
Add data objects to the collection
Remove data objects that the collection references.
From the Project Explorer, right-click the collection and select Open Editor. Warehouse Builder displays the Edit Collection dialog box that contains the following two tabs: Name Tab and Contents Tab. Use these tabs to edit the collection definition.
Use the Name tab to rename a collection or modify its description. To rename a collection, select the name in the Name field and enter the new name. The name must be unique within the project. In physical naming mode, type a name between 1 to 200 characters. Spaces are not allowed. In logical mode, the maximum number of characters is 200 and spaces are allowed.
You can also modify the description of the collection using the Description field.
Use the Contents tab to modify the contents of the collection. Use the following steps:
Select and expand the project node in the left panel.
The wizard displays a list of objects you can add to the collection.
Select and expand the collection node in the right panel.
The list of objects that are referenced in the collection are displayed.
Add new objects to the collection by selecting the objects in the Available section and clicking the right arrow.
Remove objects referenced in the collection by selecting the objects in the Selected section and clicking the left arrow.
In addition to the Design Center, Warehouse Builder provides other interfaces to create and implement your data integration solution. One such interface is OMB Plus.
OMB Plus, an extension of the Tcl programming language, is the scripting language provided by Warehouse Builder. It is a flexible, high-level command line metadata access tool for Warehouse Builder. With OMB Plus, you can write the syntactic constructs such as variable support, conditional and looping control structures, error handling, and standard library procedures.
Use OMB Plus to create, modify, delete, and retrieve object metadata in Warehouse Builder repository. You can use this scripting interface to:
Perform complex actions directly in Warehouse Builder, without launching the client user interface.
Define sets of routine operations to be executed in Warehouse Builder.
Perform batch operations in Warehouse Builder.
Automate a series of conditional operations in Warehouse Builder.
To access OMB Plus:
Select Start, Programs, Oracle - OWB_HOME, Warehouse Builder, then OMB Plus.
From the Design Center, select Window, then OMB*Plus.
The Design Center displays the OMB*Plus panel.
See Also:Oracle Warehouse Builder API and Scripting Reference, for information about OMB Plus and its commands.