4 Identifying Data Sources and Importing Metadata

Creating Locations

In addition to the automatically created locations, you can create your own locations that correspond to target schemas that you want to use as sources or targets.

To create a location:

1. In the Connection Explorer, expand the Locations node and then the node that represents the type of location you want to create.

   For example, to create an Oracle database location, expand the Locations node, the Databases node, and then the Oracle node.

2. Right-click the type of location and select New.

   The Create <location_type> Location dialog box is displayed.

3. Complete the dialog box. Click the Help button for additional details.

Using SQL*Net to Create Locations

When you create Oracle locations of type SQL*Net, you must set up a TNS name entry for these locations. The TNS name must be accessible from the Oracle Database home. To do this, run the Net Configuration Assistant from the Oracle Database home.

While setting up a TNS name for use during deployment and execution of maps and process flows, the TNS name must be accessible from the Warehouse Builder home used to run the control center service. To make the TNS name accessible, run the Net Configuration Assistant from the Warehouse Builder home. Next, restart the control center service so that it can pick up the changes.

About Locations, Passwords, and Security

Considering that all Warehouse Builder users can view connection information in a location, note that the passwords are always encrypted. Furthermore, Warehouse Builder administrators can determine whether or not to allow locations to be shared across users and persisted across design sessions. By default, locations are not shared or persisted.

Granting Privileges to a Target Location

Some deployments require the owner of the target location to have more powerful privileges than are granted when creating a new user:

• Upgrade action

• EUL deployment

A privileged database user can grant the additional privileges.

For ETL, the owner of the target location must have sufficient privileges to read data from the source location. If the source location is a database table, for example, the target must have SELECT privileges on the table.

Upgrade Action 

The GRANT_UPGRADE_PRIVILEGES PL/SQL script grants the target user the necessary roles and privileges for the Upgrade action. Use this syntax:

@%OWB_ORACLE_HOME%/owb/rtp/sql/grant_upgrade_privileges username

Where:

OWB_ORACLE_HOME is the Oracle home directory for Warehouse Builder on the target system.

username is the owner of the target location.

For example, the following command grants privileges on a Windows system to the SALES_TARGET user.

@%OWB_ORACLE_HOME%\owb\rtp\sql\grant_upgrade_privileges sales_target

EUL Deployment 

Discoverer locations require the EUL user to have the CREATE DATABASE LINK privilege.

Registering and Unregistering Locations

During the design process, you create logical definitions of locations. All modules, including their source and target objects, must have locations associated with them before they can be deployed.

Registering a location establishes a link between the workspace and the locations of source data and deployed objects. You can change the definition of a location before it is registered, but not afterward. After the location is registered, you can only change the password. To further edit a location or one of its connectors, you must first unregister the location. Unregistering deletes the deployment history for the location.

Locations are registered automatically by deployment. Alternatively, you can explicitly register a location in the Control Center.

To register a location:

1. Open the Control Center Manager and select a location from the navigation tree.

2. From the File menu, select Register.

   The Location dialog box is displayed.

3. Check the location details carefully.

   Click Help for additional information.

4. Click OK.

To unregister a location:

1. Open the Control Center Manager and select a location from the navigation tree.

2. From the File menu, select Unregister.

3. Click OK to confirm the action.

Deleting Locations

To delete a location, right-click the location in the Connection Explorer and select Delete. If the delete option is not available here, this indicates that the location has been registered in a control center and is likely being utilized. Verify that the location is not in use, unregister the location in the Control Center Manager, and then you can delete the location from the Connection Explorer.

Creating Modules

To create a module:

1. Expand the Project Explorer until you find the node for the appropriate metadata type.

   For example, if the source data is stored in an Oracle Database, then expand the Databases node to view the Oracle node. If the source data is in an SAP R/3 system, expand the Applications node to view the SAP node.

2. Right-click the desired node and select New.

   The Create Module wizard opens. The wizard determines the correct integrator to use to enable access to the data store you selected.

3. On the Name and Description page, provide a name and an optional description for the module.

4. Click Next.

   The Connection Information page is displayed.

5. Provide details about the location that is associated with this module.

   The contents of the Connection Information page depend on the type of module you create. For more information about providing information on this page, click Help.

6. Click Next to display the Summary page.

   Review the information you provided and click Back to modify entered values.

7. Click Finish.

During the course of using Warehouse Builder, you may need to associate a module with a new location. For example, assuming your production environment utilizes different locations than your development environment, you need to reassociate the modules.

To change the location associated with a module:

1. In the Project Explorer, select the module.

2. Click the Configure icon.

   The Configuration Properties dialog box is displayed.

3. In the Identification folder, select a new value for the Locations property.

Filter Information Page

Use the Filter Information page to limit the search of the data dictionary. Use one of the following methods to limit the search:

Selecting the Object Types The Object Type section displays the types of database objects that you can import. This include tables, dimensions, external tables, sequences, materialized views, cubes, views, PL/SQL transformations, and user-defined types. Select the types of objects you want to import. For example, to import three tables and one view, select Tables and Views.

Search Based on the Object Name Use the Only select objects that match the pattern option to type a search pattern. Warehouse Builder searches for objects whose names match the pattern specified. Use % as a wild card match for multiple characters and _ as a wild card match for a single character. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name.

Click Next and Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page.

Object Selection Page

Select items to import from the Available list and click the right arrow to move them to the Selected list.

To search for specific items by name, click the Find Objects icon that displays as a flashlight.

To move all items to the Selected Objects list, click Move All.

Importing Dependent Objects The Import Metadata wizard enables you to import the dependent objects of the object being imported. If you are reimporting definitions, previously imported objects appear in bold.

Select one of the following options to specify if dependent objects should be included in the import:

• None: Moves only the selected object to the Selected list. No dependencies are imported when you select this option.

• One Level: Moves the selected object and the objects it references to the Selected list. This is the default selection.

• All Levels: Moves the selected object and all its references, direct or indirect, to the Selected list.

Click Next to display the Summary and Import page.

Importing Dimensions When you import a dimension that uses a relational implementation, the implementation table that stores the dimension data is not imported. You must explicitly import this table by moving the table from the Available list to the Selected list on the Object Selection page. Also, after the import, you must bind the dimension to its implementation table. For more information on how to perform binding, see "Binding" .

Summary and Import Page

This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reimported or created. Verify the contents of this page and add descriptions, if required, for each of the objects.

If the objects you selected on the Object Selection page already exist in the module into which you are attempting to import them, you can specify additional properties related to the reimport. Click Advanced Import Options to specify options related to reimporting objects. The Advanced Import Options dialog box is displayed. For more information on the contents of this dialog box, see "Advanced Import Options".

Click Finish to import the selected objects. The Importing Progress dialog box shows the progress of the import activity. After the import completes, the Import Results page is displayed.

Import Results Page

This page summarizes the import and lists the objects and details about whether the object was created or synchronized.

Click OK to accept the changes. To save an MDL file associated with this import, click Save. Click Undo to cancel the import. Warehouse Builder stores the definitions in the database module from which you performed the import.

Advanced Import Options for Views and External Tables

Select these options for reconciling views or external tables:

• Import descriptions: The descriptions of the view or external table are imported. Existing descriptions are not preserved.

• Preserve repository added columns: The columns you added to the object in the workspace are preserved.

Advanced Import Options for Tables

Select these options for reconciling tables:

• Preserve repository added columns: Select this option to retain any columns added to the table in the workspace.

• Preserve repository added constraints: The constraints you added to the table in Warehouse Builder are preserved.

• Import indexes: Select this option to specify additional details about how indexes should be imported. Importing indexes consists of the following options:

  • Preserve repository added indexes: Select this option to retain any indexes added to the workspace table.

  • Import physical properties of indexes: Select this option to indicate how indexes should be imported. Select the Preserve repository added physical properties of indexes option below this option to specify that any physical properties added to the indexes should be preserved.

  • Import index partitioning: Select this option to indicate how index partitions should be imported. Select the Preserve repository added index partitioning option to specify that any index partitions added to the workspace table must be preserved.

• Import Partitioning: Select this option to specify additional details about how partitions should be imported. Importing partitions contains the following options:

  • Preserve repository added partitioning: Select this option to retain all partitions added to the workspace table.

  • Import physical properties of partitioning: Use this option to indicate how the physical properties of partitions should be imported. Select Preserve repository added physical properties of partitioning to indicate that all physical properties of the partitions in the workspace table should be retained.

• Import physical properties: Select this option to indicate how the physical properties of the table should be imported. Select the Preserve repository added physical properties option to specify that all physical properties added to the workspace table must be preserved.

• Import descriptions: Select this option to import the descriptions of the table.

Advanced Import Options for Object Types

Select these options for reconciling object types:

• Import descriptions: Select this option to import the descriptions of the object type.

• Preserve repository added attributes: Select this option to retain the attributes added to the object type in the workspace.

Advanced Import Options for SQL Collections

SQL collection includes nested tables and Varrays.

Import descriptions: Select this option to import the descriptions of nested tables and Varrays.