|Oracle® Warehouse Builder API and Scripting Reference
11g Release 2 (11.2)
Part Number E10584-01
This chapter describes how to extend the workspace by creating custom objects and custom properties. This chapter includes the following topics:
You may encounter scenarios that require you to extend the OWB object model with new types of objects or properties. For example, as you use Warehouse Builder in conjunction with other applications, you may want to document how the data and metadata you manage in Warehouse Builder interacts with other applications.Oracle Warehouse Builder supports user-defined objects (UDOs) and user-defined properties (UDPs) that allow you to represent these new objects or to annotate pre-defined Warehouse Builder objects with new properties.You can specify icons to represent user-defined objects, edit user-defined objects and properties with a basic GUI editor in Design Center, and reference them in OMB*Plus scripts. Combining UDOs, UDPs with experts and scripting is one powerful way to extend Warehouse Builder's feature set.You can also establish relationships between UDOs and other objects, and perform lineage and impact analysis that includes the UDOs.
For example, as you use Warehouse Builder in conjunction with other applications, you may want to document how the data and metadata you manage in Warehouse Builder interacts with other applications. To facilitate this documentation, you can introduce new metadata objects and associate those objects with existing workspace objects. These custom objects appear in the Design Center with the icon of your choice. You can edit them with a basic editor and perform lineage and impact analysis.
Users with administration privileges can extend the workspace by adding new properties and objects.
Adding New Properties to Workspace Objects: Each workspace object has a pre-defined property set. If you would like to add new properties specific to your environment, this can be done with UDP's. (For example, you might like to add a property called Design Notes for each of your metadata objects.) Once defined, UDP's are treated the same as pre-defined properties.
Adding UDOs to the Workspace: You can introduce new types of objects to the workspace by defining UDOs which follow the general rules for object locking, multiuser access, transactions, security, and snapshots. You can also import and export UDOs and UDPs using the Metadata Loader (MDL). Note that after you define a new UDO, you can use it in scripting as well.
For the sake of clarity, this chapter refers to the objects native to the workspace as workspace objects. Any objects you introduce to the workspace are UDOs and any new properties are UDPs.
OMB*Plus scripts enable you to define new objects, add and remove properties, as well as view the attributes for existing objects. The syntax is case sensitive and must be in upper case. While creating UDOs and UDPs, follow the guidelines in "Naming Conventions for UDOs and UDPs".
OMBDEFINE CLASS_DEFINITION enables you to create new object definitions in the workspace.
To create a new module definition, use the following command:
OMBDEFINE MODULE CLASS_DEFINITION 'UD_TABLEMODULE' SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) VALUES ('Table Module', 'Table Modules')
This creates a new module definition called
OMBREDEFINE CLASS_DEFINITION enables you to redefine a UDO.
To create a UDP on the Dimension object, use the following command:
OMBREDEFINE CLASS_DEFINITION 'DIMENSION' ADD PROPERTY_DEFINITION 'UD_DOCID' SET PROPERTIES (TYPE, DEFAULT_VALUE) VALUES ('INTEGER', '100')
This adds a new property definition called
UD_DOCID to class definition
The following command adds a new property definition for notes for the
COLUMN type. Because columns exist in tables, views, materialized view, external tables and sequences, the following command adds the definition of this property to columns for all of those metadata objects:
OMBREDEFINE CLASS_DEFINITION 'COLUMN' ADD PROPERTY_DEFINITION 'UD_COL_NOTE' SET PROPERTIES (TYPE, DEFAULT_VALUE) VALUES ('STRING', 'notes')
When you create and save a new property definition, OMB*Plus performs the following validations:
A user access check ensuring that you have single-user access to the current workspace.
A name space check ensuring that you did not define two identically named property definitions within the same class hierarchy.
A property value check ensuring that you defined default values consistent with the data types that you specified.
To change the name or the default value of a given property definition, use a command as follows:
OMBREDEFINE CLASS_DEFINITION 'TABLE' MODIFY PROPERTY_DEFINITION 'UD_TBL_NOTE' SET PROPERTIES (DEFAULT_VALUE, BUSINESS_NAME) VALUES ('99', 'Table Note')
To delete a UDP, use a command such as
OMBREDEFINE CLASS_DEFINITION 'TABLE' DELETE PROPERTY_DEFINITION 'UD_TBL_NOTE'
which deletes the UD_TBL_NOTE property definition from the Table class. Deleting a UDP is a destructive action and should generally be done with caution because it cannot be undone. It renders irretrievable all custom property values made for this property definition in your workspace.
You can use
OMBDESCRIBE on any class definition to view the attributes for a metadata element. Among other tasks, use
OMBDESCRIBE to list all the property definitions including the UDPs for a given object type. For instance, the following command lists the UDPs for dimensions:
OMBDESCRIBE CLASS_DEFINITION 'DIMENSION' GET USER_DEFINED PROPERTY_DEFINITIONS
You can also use
OMBDESCRIBE to inspect the properties of a property definition. For instance, for a UDP called
UD_DOCID, you can discover its data type, default value, and business name using the following command:
OMBDESCRIBE CLASS_DEFINITION 'DIMENSION' PROPERTY_DEFINITION 'UD_DOCID' GET PROPERTIES (TYPE, DEFAULT_VALUE, BUSINESS_NAME)
It is mandatory to include the prefix UD_ while naming UDOs and UDPs. This ensures that the names of UDOs and UDPs are not identical to the names of predefined objects in the workspace.
Note:If in a previous release you named a UDP with the prefix UDP_, then it is still valid. However, for all subsequent UDOs and UDPs, use the UD_ prefix.
Carefully plan the new additions to the workspace.
If possible, you should define all user-defined properties into the workspace before enabling end users to access it. In doing so, you avoid the task of supplying values for UDPs on existing objects.
Log in as the workspace owner, in single user mode.
If another user is logged on within the GUI or OMB*Plus, then you will be prevented from running commands that alter the structure of the workspace.
If already logged into the Design Center, then you can start OMB*Plus from the main menu of Design Center. On the main menu, click View, and select OMB*Plus. To ensure that other users do not access the workspace, issue the command
Use the command OMBREDEFINE on the workspace object to which you want to add a custom property definition.OMBDEFINE FOLDER_DEFINITION
For examples on how to use this command, see the section OMBREDEFINE.
To view the changes in OMB*Plus, use the command OMBDESCRIBE.
Use the command OMBCOMMIT to save the changes.
After you commit the changes, users can view and assign values to the new property in the graphical user interface.
Notify users that they can log in to the Design Center.
Log in to the client as an administrator.
Open the OMB*Plus view from the main menu of the Design Center. To do this, click View, and select OMB*Plus.
Ensure that you are in single user mode. You can verify this with the command
OMBDISPLAYCURRENTMODE. If you are in multiple user mode, then switch to single user mode by using the command:OMBREDEFINE CLASS_DEFINITION 'VIEW
In the OMB*Plus client, enter the following command to create four UDPs for the object
OMBREDEFINE CLASS_DEFINITION 'VIEW' \ ADD PROPERTY_DEFINITION 'UD_OWNER' SET PROPERTIES \ (TYPE, DEFAULT_VALUE, BUSINESS_NAME) VALUES \ ('STRING', 'REP_OWNER', 'Object Owner')
OMBREDEFINE CLASS_DEFINITION 'VIEW' \ ADD PROPERTY_DEFINITION 'UD_FILE' SET PROPERTIES \ (TYPE, DEFAULT_VALUE) VALUES ('FILE', 'C:\\vw.sql')
OMBREDEFINE CLASS_DEFINITION 'VIEW' \ ADD PROPERTY_DEFINITION 'UD_LINK' SET PROPERTIES \ (TYPE, DEFAULT_VALUE) VALUES ('URL', 'http://www.oracle.com')
OMBREDEFINE CLASS_DEFINITION 'VIEW' \ ADD PROPERTY_DEFINITION 'UD_VERSION' SET PROPERTIES \ (TYPE, DEFAULT_VALUE) VALUES ('DATE', '2006-1-7')
This creates the following UDPs:
Note that the valid UDP types are: integer, string, float, double, date, timestamp, boolean, long, file, and url.
From the Projects Navigator create a view in any module.
Open the property inspector for the view. To do this, select the view and from the main menu, click View, and then select Property Inspector.
The user defined properties are listed on the property inspector.
You can modify the values of any of the UDPs from the property inspector.
OMBREDEFINE CLASS_DEFINITION 'VIEW' DELETE PROPERTY_DEFINITION 'UD_VERSION'
All UDOs must belong to a module, and the module itself is a UDO. This module acts as the topmost container holding other objects within it. A module can contain folders, first class objects (FCOs), and second class objects (SCOs). Similarly, a folder can contain other folders, FCOs, and SCOs. An FCO can contain one or more SCOs.
UDOs exhibit a parent-child relationship. The module is the topmost parent. An FCO within a module is a child element of the module. Similarly, an SCO within an FCO is a child element of the FCO. For example, an Oracle module is a parent module. A table within this module is an FCO and a column within the table is an SCO.
To define new objects for the workspace, complete the following steps:
Carefully plan the new additions to the workspace.
Before you begin, fully review the remainder of this chapter and become familiar with the necessary scripting commands.
Log in to the client as an administrator and in single user mode.
Design the UDO based on the steps described in "Writing Scripts to Define UDOs".
Once you create the UDO through scripting, you can use the graphical user interface to create and edit the objects it contains.
Log in to the Design Center and view the new objects as described in "Working with UDOs and UDPs".
Verify that the new objects display as intended.
(Optional) Assign a new icon to the UDO, as described in"Creating New Icons for Workspace Objects".
Save the changes using the command OMBCOMMIT.
Notify users that they can log in to the client.
Create a user defined module: This will be the parent module.
Define the object type: Define the module to be a folder. This folder is an object that can hold other objects.
Define FCOs and SCOs: Create user-defined FCOs and SCOs for the UDO, define the parent-child relationship between the FCOs and the SCOs, and assign physical names to these objects. For example,
UD_WORKBOOK is a valid physical name. You can also indicate a business name and plural name, both of which are displayed in the Design Center and in editors. Continuing the previous example,
Workbooks are likely entries for the business name and plural name, respectively. If you do not specify these values, then they default to the physical name.
Define object properties: Define the properties for all the objects you create. Some properties, such as Name, Business_Name, Plural_Name, and Description, are assigned automatically to any newly created object.
Add component definition: All parent objects need to be assigned a component definition. The child elements have to be added to the component definition. The component definition determines the lifetime of child elements.For example, a column cannot exist if the parent table is deleted. Note that a component definition includes all the SCOs for the parent FCO, and all the SCOs owned by the first level SCOs and so on. If the child type is a FCO, then it must be added to the FOLDER_DEFINITION of the parent.
Define association types: Create association types to indicate the types of relationships a UDO can have with workspace objects and other UDOs. You need to perform this step only if you want end users to later relate the UDO to specific instances of objects. For instance, in your script you could associate the UDO with tables and views. In the Design Center, end users could then relate instances of the UDO with specific tables and views. Warehouse Builder displays these relationships in impact and lineage analysis reports.
Assign icons (optional): See "Creating New Icons for Workspace Objects".
Save the changes.
This section provides an example to create UDOs modeled on a Java application. The Java application acts as a module. This module contains classes (FCOs), and those classes contain methods (SCOs). Within a method, you can model the lines of code. From a business standpoint, this is of interest because a particular line of code in an application may be impacted by a change in a database table if it is used within a SQL (JDBC) statement.
Figure 7-1 displays the structure of the UDO.
Figure 7-1 Structure of the UDOs
To create the UDOs, perform the following steps:
Log in to the Warehouse Builder client as an administrator and open the OMB*Plus window. Make sure that you are logged in single user mode.
First create a module definition and set properties for this module:
OMBDEFINE MODULE CLASS_DEFINITION 'UD_JAVA_APP' \ SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ VALUES ('Java Application', 'Java Applications')
This defines the module definition and sets certain properties common to all objects. BUSINESS_NAME is the user-friendly name for an object. If the Naming mode preference for the Design Center is switched to Business mode, then the value set for BUSINESS_NAME is displayed for the object. PLURAL_NAME is the label that is used to show where multiple instances of an object are shown, such as the label used for a tree node in the Design Center that contains several instances of the object.
Now create a folder definition with the same name as the module so that the module assumes the role of a folder:
OMBDEFINE FOLDER_DEFINITION 'UD_JAVA_APP'
Now create an FCO:
OMBDEFINE FIRST_CLASS_OBJECT CLASS_DEFINITION \ 'UD_JCLASS' SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ VALUES ('Java Class File', 'Java Class Files')
Add the FCO as a child of the folder class:
OMBREDEFINE CLASS_DEFINITION 'UD_JAVA_APP' \ ADD CHILD_TYPE 'UD_JCLASS'
Create a component definition for the FCO:
OMBDEFINE COMPONENT_DEFINITION 'UD_JCLASS'
Add the component definition to the folder definition:
OMBREDEFINE FOLDER_DEFINITION 'UD_JAVA_APP' \ ADD 'UD_JCLASS'
Create an SCO and set its properties:
OMBDEFINE SECOND_CLASS_OBJECT \ CLASS_DEFINITION 'UD_JMETHOD' \ SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ VALUES ('Method', 'Methods')
Add the SCO as a child of the FCO:
OMBREDEFINE CLASS_DEFINITION 'UD_JCLASS' \ ADD CHILD_TYPE 'UD_JMETHOD'
Add the SCO to the component definition:
OMBREDEFINE COMPONENT_DEFINITION 'UD_JCLASS' \ ADD 'UD_JMETHOD'
Create an SCO and set its properties:
OMBDEFINE SECOND_CLASS_OBJECT \ CLASS_DEFINITION 'UD_JMETHOD_LINE' \ SET PROPERTIES (BUSINESS_NAME, PLURAL_NAME) \ VALUES ('Java Method Line', 'Java Method Lines')
Add this SCO as a child of the initially created SCO:
OMBREDEFINE CLASS_DEFINITION 'UD_JMETHOD' \ ADD CHILD_TYPE 'UD_JMETHOD_LINE'
Add this SCO to the component definition:
OMBREDEFINE COMPONENT_DEFINITION 'UD_JCLASS' \ ADD 'UD_JMETHOD_LINE'
This creates the following UDOs:
A module folder called UD_JAVA_APP
An FCO named UD_JCLASS, within the module
An SCO named UD_JMETHOD, which is the child of UD_JCLASS
Another SCO named UD_JMETHOD_LINE, which is the child of UD_JMETHOD
You can access the UDOs from the Projects Navigator under the User Defined Modules icon. To create a new instance of the UDO, right-click the UDO and select New. You can create new modules and FCOs as well as edit these modules and FCOs.
Note:If you cannot see the newly created UDOs in the Projects Navigator, shut down Warehouse Builder completely, and then start it up again, saving any changes if prompted.
Associating a Java Application with a Table
This example associates the SCO, UD_JMETHOD, with one or more tables. This is modeling the fact that a method could be referencing tables in JDBC calls.
To associate the Java method to table, use the command:
OMBDEFINE ASSOCIATION_DEFINITION 'UD_XJMETHOD2TABLE' \ SET PROPERTIES (CLASS_1,CLASS_2,ROLE_1,ROLE_2 \ ,ROLE_1_MAX_CARDINALITY,ROLE_1_NAVIGABLE) \ VALUES ('UD_JMETHOD','TABLE','TABLEUSED','JM2TABLE' \ ,'INFINITE','true') ADD DEPENDENCY_DEFINITION 'DATAFLOW'
CLASS_1 and CLASS_2 can be any classes (FCO or SCO). At least one of the classes should be a user defined class. The other class can be either a user defined class or one of the main Warehouse Builder classes, such as table or column. In this example, the association is between the UDO UD_JMETHOD, and table.
Role_1 and Role_2 are the names you use to identify Class_1 from the point of view of this association. A class may have multiple associations and it plays a role in each one of them.
MAX_CARDINALITY allows you to limit the number of objects of that class which participate in that association. For example, consider the association between uniqueKey and foreignKey. The max_cardinality of uniqueKey is 1, because a given foreignKey object can be associated with at most one uniqueKey object. MAX_CARDINALITY can be set to any positive integer, or the reserved word INFINITE.
ROLE_1_NAVIGABLE is used by Lineage/Impact analyzer. If set to TRUE, it means that the analyzer can traverse the association in either direction between Class_1 and Class_2. If the property is set to FALSE, it means that the analyzer can traverse the relationship from Class_1 to Class_2, but not the other way around.
When you set the DEPENDENCY_DEFINITION parameter to DATAFLOW, the association participates in the dataflow impact and lineage analysis.
To associate the UDO to a table, complete the following steps:
In the Projects Navigator, expand the node User Defined Modules.
Right-click UD_JAVA_APP and select New UD_JAVA_APP.
Specify a name for the application.
Right-click UD_JCLASS and select New UD_JCLASS.
Specify a name for the Java class. In the example, it is CUSTOMERUPDATE.
Right-click CUSTOMERUPDATE, and select Open to open the UDO Editor.
Click the Object and Association Tree tab and select CUSTOMERUPDATE. In the right panel, you can view the properties for the user defined object CUSTOMERUPDATE.
On the Object and Association Tree tab, right-click UD_JMethod and select Create.
An SCO called JMETHOD_1 is created.
JMETHOD_1 contains two nodes: UD_JMETHOD_LINE, which is the child SCO, and TABLEUSED, which is the value specified for ROLE_1 when the association UD_XJMETHOD2TABLE was created.
Right-click TABLEUSED and select Reference.
The Object Selector dialog box is displayed, and allows you to select the table to which you want to connect the UDO.
In the graphical user interface, you can view UDOs and UDPs in the Projects Navigator and in the Repository Design Browser. However, in the Projects Navigator, you can also edit the UDOs and UDPs.
Repository Design Browser
The Repository Browser is a web browser for viewing repository metadata, generating different reports for objects metadata, and auditing runtime deployment and execution operations. In the Design Browser you can see all objects (including UDOs)and their properties (including UDPs) of all the projects created for a workspace. You can also generate and view reports on metadata including lineage and impact analysis reports.
If you define a UDP for a given object, then you can view the UDP name and values in the browser reports. The values are listed as Extended Property Name and Extended Property Value.
You can export UDOs and UDPs as any other object.
In the MDL Control file, the option is
filename to export the metadata definition. For example:
## Sample Export file USERID=UserName/Password@HostName:PortID:OracleServiceName # DEFINITIONFILE=Drive:\DirectoryName\filename.mdd FILE=Drive:\DirectoryName\filename.mdl LOG=Drive:\DirectoryName\filename.log
You can import UDPs from the command line as well as from the user interface. During import, MDL updates the UDPs for all objects. In the MDL Control file, the option is DEFINITIONFILE=filename to import the metadata definition. For example:
## Sample Import file USERID=UserName/Password@HostName:PortID:OracleServiceName # DEFINITIONFILE=Drive:\DirectoryName\filename.mdd FILE=Drive:\DirectoryName\filename.mdl LOG=Drive:\DirectoryName\filename.log
You can import UDPs using one of the following search criteria:
Universal ID: The metadata definition contains a Universal Object ID (UOID). The UOID uniquely identifies objects across workspaces. If you import the MDL file by UOID, then MDL looks up the metadata definition by UOID. If the metadata definition name in the source MDL file is different from the metadata definition in the workspace, then MDL renames it when it is running in the Update/Replace mode.
Physical Name: MDL looks up the metadata definition by physical name.
Regardless of the import mode, MDL either adds the metadata definition if it does not exist in the workspace, or updates the metadata definition if it already exists. MDL does not delete metadata definitions in the workspace.
When updating the metadata definition, MDL only renames the object if the names are different (search criteria is by UOID), and updates the default value. MDL does not change the data type.
Icons are graphics that visually suggest the availability of a function or type of an object to end users. There are many types of pre-defined workspace objects, each with their own icon. You may want to change the icon associated with an existing object or instance of an object to something more recognizable. For example, you could visually highlight a particular table by altering its icon. Additionally, for UDOs, you may want to change the default icon to something representative of the object. You can create your own icons using a graphics editor or third-party software.
You can create a new icon set and then associate it with an object using the OMB*Plus scripting language.
Note:You can assign new icons to most workspace objects with the exception of pre-defined objects like public transformations and public data rules and
Every object has a set of icons of varying sizes to represent it throughout the various editors and toolbars. Each icon set includes a canvas icon, palette icon, and a tree icon as described in Table 7-1. When you define a new icon set, follow the sizing guidelines. If you specify a new icon with an incorrect size, it is automatically resized, which may distort your intended design.
Table 7-1 Icon Sets
Represents instances of objects in the canvas of an editor. For example, it displays a table icon in the canvas of the Mapping Editor or in a Lineage Report. The correct size is 32 x 32 pixels in GIF or JPEG format.
Represents types of objects in editor palettes. For example, it displays the table operator in the Mapping Editor operator palette. The correct size is 18 x 18 pixels in GIF or JPEG format.
Represents types and instances of objects in navigation trees such as the Project Explorer in the Design Center. The correct size is 16 x 16 pixels in GIF or JPEG format.
Log in to the client as an administrator.
In the Globals Navigator, right-click the Icon Sets node and select New Icon Set.
The Create Icon Set dialog box is displayed. For details on the values to be entered on this page, see "Create Icon Set Dialog Box" .
The Create Icon Set dialog box enables you to specify values for the Icon Set. Enter the following values and click OK to define a new Icon Set:
Name: The name of the Icon Set.
Description: A description of the Icon Set.
File Name: Navigate and select the image that you want to assign to the new Icon Set. You need to select an image for Canvas, Palette, and Tree Icon.
Note:You can select any image to represent the new icon.
The newly created icon set will be available under Icon Sets in the Globals Navigator.
To assign a new icon set to an object, you must use the OMB*Plus scripting clause SET REFERENCE ICONSET. To remove an icon set, use the clause UNSET REFERENCE ICONSET.
To assign an icon set named ICON1 to an FCO such as Table, use the following command:
OMBREDEFINE CLASS_DEFINITION 'TABLE' SET REF ICONSET 'ICON1'
To remove an icon set assigned to this FCO, use the following command:
OMBREDEFINE CLASS_DEFINITION 'TABLE' UNSET REF ICONSET
To assign an icon set to a specific instance of a table TAB1, use the following command:
OMBALTER TABLE 'TAB1' SET REFERENCE ICONSET 'ICON1'
To remove this icon set, use the following command:
OMBALTER TABLE 'TAB1' UNSET REFERENCE ICONSET
To know what icon set has been assigned to an object, use the following command:
OMBRETRIEVE TABLE 'TAB1' GET REFERENCE ICONSET