Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

B31278-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Identifying Data Sources and Importing Metadata

In Oracle Warehouse Builder you can access data from a variety of sources. You can interpret and extract metadata from custom as well as packaged applications and databases. As a precursor to extracting any data set, you first import its metadata.

This chapter includes the following topics:

About Source Data and Metadata

The source systems for a data warehouse are typically transaction processing applications. For example, a sales analysis data warehouse typically extracts data from an order entry system that records current order activities.Designing the extraction process can be problematic. If the source system is complex and poorly documented, then determining which data to extract can be difficult. Moreover, the source system typically cannot be modified, nor can its performance or availability be adjusted. You can overcome these problems by first importing the metadata.

Metadata is the data that describes the contents of a given object in a data set. For example, the metadata for a table would indicate the data type for each column. After you import the metadata into Warehouse Builder, you can annotate the metadata and design an extraction strategy independently from the transaction processing application.

Before you import source metadata into Warehouse Builder, first create a module that will contain these metadata definitions. The type of module you create depends on the source from which you are importing metadata. For example, to import metadata definitions from an Oracle database, create an Oracle module. To import metadata definitions from flat files, create a flat file module.

Supported Sources and Targets

Table 4-1 lists the data storage systems and applications that Warehouse Builder 11.1 can access. The table lists the supported sources and targets for each Location node as displayed in the Connection Explorer.

Table 4-1 Sources and Targets Supported in Warehouse Builder 11.1

Location Node in the Connection Explorer Supported Sources Supported Targets

Databases/Oracle

Oracle DB 8.1, 9.0, 9.2, 10.1, 10.2, 11.1

Oracle DB 9.2, 10.1, 10.2, 11.1

Databases/Non-Oracle

Any database accessible through Oracle Heterogeneous Services, including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata.

Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access.

See "Using Microsoft Products as Sources".

Any database accessible through Oracle Heterogeneous Services, including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata.

Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access.

To load data into spreadsheets or third-party databases, first deploy to a comma-delimited or XML format flat file.

Files

Delimited and fixed-length flat files.

See "Importing Definitions from Flat Files".

Comma-delimited and XML format flat files.

See " Flat Files as Sources or Targets".

Applications

SAP R/3: 3.x, 4.0x, 4.6x, 4.7, 5.0; mySAP ERP 2004; mySAP ERP 2005 (with SAP NetWeaver 2004, SAP BASIS 700 Components). See "Retrieving Data From SAP Applications".

Oracle E-Business Suite, see "Integrating with E-Business Suite"

PeopleSoft 8, 9, see "Integrating with PeopleSoft"

Siebel, see "Integrating with Siebel"

None

Process Flows and Schedules/Oracle Workflow

None

Oracle Workflow 2.6.2, 2.6.3, 2.6.4, 11i

Process Flows and Schedules/Concurrent Manager

None

In general, you can deploy a schedule in any Oracle database location, version 10g or later.

To deploy a schedule in Concurrent Manager, version 11i or 12i is required. However, for both versions, you must select 11i as the version when you create a location in Warehouse Builder.

Business Intelligence/Discoverer

None

Discoverer 10.1

Databases/Transportable Module Source

See "Moving Large Volumes of Data" in the Oracle Warehouse Builder Installation and Administration Guide.

N/A

Databases/Transportable Module Target

N/A

See "Moving Large Volumes of Data" in the Oracle Warehouse Builder Installation and Administration Guide.


Oracle Heterogeneous Services

Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make a non-Oracle system appear as a remote Oracle Database server. The agent can be an Oracle Transparent Gateway or the generic connectivity agent included with Oracle Database.

General Steps for Importing Metadata from Sources

Whether you want to import metadata from a table, file, or application, the general process is the same and you always import metadata through a module.

  1. Review the list of supported sources and targets in Table 4-1 to determine if the source from which you want to extract data is supported in Warehouse Builder.

  2. Create a location as described in "Creating Locations".

  3. Create a module for the source metadata as described in "Creating Modules".

  4. Right-click the module and select Import.

  5. Follow the prompts in the Import Metadata Wizard.

    The wizard prompts you for information based on the type of source you selected. For more information, see "Using the Import Metadata Wizard".

  6. (Optional) For Oracle data objects, view the data stored in the data object using the Data Viewer. Right-click the object and select Data.

Subsequent Steps

After successfully importing the metadata, you can design ETL logic to extract the data from the source, transform the data, and load it into a target schema.

Over a period of time, the source metadata may change. If this occurs, you can use Warehouse Builder to identify the ETL logic that would be impacted and potentially made invalid due to a change in metadata.

See Also:

To introduce the changed metadata into Warehouse Builder, right-click the desired module and select Import. As described in "Reimporting Definitions from an Oracle Database", Warehouse Builder recognizes when you are reimporting metadata.

About Locations

Locations enable you to store the connection information to the various files, databases, and applications that Warehouse Builder accesses for extracting and loading data. Similarly, locations also store connection information to ETL management tools and Business Intelligence tools. For a detailed listing, see "Supported Sources and Targets".

Oracle Database locations and file locations can be sources, targets, or both. For example, you can use a location as a target for storing temporary or staging tables. Later, you can re-use that location as a source to populate the final target schema.

In some cases, such as with flat file data, the data and metadata for a given source are stored separately. In such a case, create a location for the data and another for the metadata.

Automatically Created Locations

During installation, Warehouse Builder creates an Oracle location named OWB_REPOSITORY_LOCATION. This location provides the connection details to the Warehouse Builder workspace. You cannot rename or delete the workspace location. Only a database administrator can change the password. To prevent unauthorized access to the database administrator password, all users are restricted from deploying to the workspace location.

Types of Locations

You can deploy to several different types of locations. Each location type has a different use:

  • Databases: Targets for either relational or dimensional business intelligence systems, including objects such as tables and views, or dimensions and cubes.

  • Files: Targets for storing data in comma-delimited or XML format.

  • Applications: Targets for SAP systems.

  • Process Flows and Schedules: Targets for managing ETL.

  • Business Intelligence: Targets for metadata derived from Databases or Oracle modules.

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.

See Also:

Oracle Warehouse Builder Installation and Administration Guide for more information about managing passwords

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.

About Connectors

A connector is a logical link created by a mapping between a source location and a target location. The connector between schemas in two different Oracle Databases is implemented as a database link, and the connector between a schema and an operating system directory is implemented as a database directory.

You do not need to create connectors manually if your user ID has the credentials for creating these database objects. Warehouse Builder will create them automatically the first time you deploy the mapping. Otherwise, a privileged user must create the objects and grant you access to use them. You can then create the connectors manually and select the database object from a list.

See Also:

To create a database connector:

  1. In the Connection Explorer, expand the Locations folder and the subfolder for the target location.

  2. Right-click DB Connectors and select New.

    The Create Connector wizard opens.

  3. Follow the steps of the wizard. Click the Help button for specific information.

To create a directory connector:

  1. In the Connection Explorer, expand the Locations folder and the subfolder for the target location.

  2. Right-click Directories and select New.

    The Create Connector dialog box opens.

  3. Click the Help button for specific information about completing this dialog box.

About Modules

Modules are grouping mechanisms in the Project Explorer that correspond to locations in the Connection Explorer. A single location can correspond to one or more modules. However, a given module can correspond to only a single location at a time.

The association of a module to a location enables you to perform certain actions more easily in Warehouse Builder. For example, you can reimport metadata by reusing an existing module. Furthermore, when you deploy ETL processes in subsequent steps, modules enable you to deploy related objects together such as process flows.

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.

Example: Importing Metadata from Flat Files

Assume that there are numerous flat files stored across two different drives and directories on your source system. In the Connection Explorer, you create two locations that reference the directories in which the source data is stored. Now in the Project Explorer, right-click the Files node and select New to create a new module. Repeat this for each of the two directories. For each of the two modules, select Import. A wizard directs you on how to import one or more files into each module.

Figure 4-1 provides a diagrammatic representation of accessing flat file data stored in different drives or directories on your source system. Each location maps to a particular directory on your source system.

Figure 4-1 Importing Data From Flat File Sources

Description of Figure 4-1 follows
Description of "Figure 4-1 Importing Data From Flat File Sources"

Using the Import Metadata Wizard

Importing is also known as reverse engineering. It saves design time by bringing metadata definitions of existing database objects into Warehouse Builder. You use the Import Metadata Wizard to import metadata definitions into modules.

The Import Metadata Wizard supports importing of tables, views, materialized views, dimensions, cubes, external tables, sequences, user-defined types, and PL/SQL transformations directly or through object lookups using synonyms.

Importing a table includes importing its columns, primary keys, unique keys, and foreign keys, which enable import of secondary tables. When you import an external table, Warehouse Builder also imports the associated location and directory information for the associated flat file.

You can import metadata definitions either from the Oracle Database catalog or Designer/2000 (Oracle Designer).

Importing Definitions from a Database

Use the Import Metadata Wizard to import metadata from a database into a module. You can import metadata from an Oracle Database, a non-Oracle Database, or a Designer repository.

To import definitions from an Oracle Data Dictionary:

  1. Right-click a data source module name and select Import.

    The Welcome page of the Import Metadata Wizard is displayed. This page lists the steps to import object metadata. Click Next to proceed with the import.

    If you did not specify the location details for the Oracle module, Warehouse Builder displays a warning dialog box. This dialog box informs you that you must first specify the location details. Click OK. The Edit Oracle Database Location dialog box for the Oracle module is displayed. Use this dialog box to specify the location information. Clicking OK on this dialog box displays the Welcome page of Import Metadata Wizard.

  2. Complete the following pages:

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.

Reimporting Definitions from an Oracle Database

Reimporting your source database definitions enables you to import changes made to your source metadata since your previous import. You do not have to remove the original definitions from the workspace. Warehouse Builder provides you with options that also enable you to preserve any changes you may have made to the definitions since the previous import. This includes any new objects, foreign keys, relationships, and descriptions you may have created in Warehouse Builder.

To reimport definitions:

  1. Right-click a data source module name and select Import.

    The Welcome page for the Import Metadata Wizard is displayed.

  2. Click Next.

    The Filter Information page is displayed.

  3. Complete the Filter Information Page and Object Selection Page, selecting the same settings used in the original import to ensure that the same objects are reimported.

  4. The Summary and Import page displays. For objects that already exist in the workspace or ones that you are reimporting, the Reimport action is displayed in the Action column.

    If the source contains new objects related to the object you are reimporting, the wizard requires that you import the new objects at the same time. For these objects, the Create action displays in the Action column.

  5. Click Advanced Import Options and make selections. (Optional)

  6. Click Finish.

    Warehouse Builder reconciles and creates objects. When this is complete, the Import Results dialog box displays.

    The report lists the actions performed by Warehouse Builder for each object.

    Click Save to save the report. You should use a naming convention that is specific to the reimport.

  7. Click OK to proceed.

    Click Undo to undo all changes to your workspace.

Advanced Import Options

The Advanced Import Options dialog box displays the options that you can configure while importing objects. This dialog box enables you to preserve any edits and additions made to the object definitions in the Warehouse Builder workspace.

By default, all options on this dialog box are checked. Clear boxes to have these objects replaced and not preserved.

For example, after importing tables or views for the first time, you manually add descriptions to the table or view definitions. If you want to make sure that these descriptions are not overwritten while reimporting the table or view definitions, you must select the Preserve Existing Definitions option. This ensures that your descriptions are not overwritten.

The contents of this dialog box depend on the type of objects being imported. For more information about the advanced import options for each type of objects, refer to the following sections:

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.

Updating Oracle Database Source Definitions

The Edit Module dialog box enables you to edit the name, metadata location, and the data location of a source module.

To update the database definitions:

  1. Double-click any Oracle module.

    The Edit Module dialog box displays. You can edit the metadata location as well as the data location of the database.

  2. To edit the metadata location, click the Metadata Locations tab and specify the following:

    • Source Type: The source type identifies the location of the data and the metadata. It can be either Oracle Data Dictionary or Oracle Designer Repository. Select Oracle Data Dictionary if the metadata is stored in the default workspace of the Oracle Database. Select Oracle Designer Repository if the metadata is stored in an Oracle Designer repository.

    • Location: Identifies the location of the module. You can select a location from the list.

  3. To edit the data location, click the Data Locations tab. You can either select from the existing locations or create a new location. To create a new location, click New. The Edit Oracle Database Location dialog box displays. Specify the details of the data location here.