Metabase Oracle Connection



This chapter describes additional tasks that may be required after you have performed the upgrade.

  1. Metabase Connect Oracle
  2. Metabase Oracle Connection Manager

On the Connection Page of the Create Entity wizard, select the Loader Connection given to you by the administrator that you have checked in Step 1. Leave the default settings for the filter and the connection and click Next. Oracle Data Quality connects to the data source using the Loader Connection you selected in Step 4. If the connection fails, contact your Metabase Administrator.

  1. Working with Oracle in Metabase Starting in v0.20.0, Metabase provides a driver for connecting to Oracle databases. Under the hood, Metabase uses Oracle's JDBC driver; due to licensing restrictions, we can't include it as part of Metabase.
  2. With Panoply, you can eliminate that hassle—and all that wasted time. Set up code-free connections to both Microsoft SQL Server and Metabase with just a few clicks. Then Panoply will automatically sync your data so it’s ready to analyze when you are. It’s that simple: Unblock your data with Panoply.
  3. Starting in v0.20.0, Metabase provides a driver for connecting to Oracle databases. Under the hood, Metabase uses Oracle's JDBC driver; due to licensing restrictions, we can't include it as part of Metabase. Luckily, downloading it yourself and making it available to Metabase is straightforward and only takes a few minutes.
  4. You can connect Metabase to your Google Analytics data in Panoply via an ODBC connection. What data can I integrate with Metabase? Panoply allows you to integrate Metabase with over 60 data integrations including all major CRMs, databases, file systems, ad networks, analytics platforms, and finance tools.

8.1 Update Metabase Connections

The following commands allow you to update your loader connections after migrating the repository:

  • updateconnection - updates the details of a specific connection

  • updateallconnections - updates the details of all migrated connections

Oracle recommends using the updateconnection command to update your migrated loader connections.

To update the metabase connections: Bmw motorcycle battery charger manual.

  1. Start the Metabase Server Administrator Prompt

    • On UNIX operating systems:

    • On Windows operating systems:

      Select Programs > Oracle > Oracle Data Profiling and Quality > Metabase Server >Administrator Command Prompt from the Start menu.

  2. When prompted, supply your username, password, and metabase name.

    Use the _control metabase name.

  3. Enter one of the following commands:

    • To update a specific migrated connection in all the metabases:

    • To update all migrated connections in all the metabases.

  4. Respond to the system prompts by typing a new value and pressing Enter. To keep the current value, press Enter at the prompt.

    After you respond to all the prompts, the command locates the specified connection in all the metabases and updates them with the new information.

    Example of a connection update:

    1. Start the Metabase Server Administrator and log on to the _control metabase:

    2. At the _control prompt, enter the updateconnection command and press Enter:

      The following prompt appears:

    3. Press Enter to use the same description, or type a new description and then press Enter.

      The following prompt appears:

    4. Press Enter to use the same path, or type a new path and then press Enter. The following prompt appears:

    5. Press Enter to use the same path, or type a new path and then press Enter. The following message appears:

    6. Respond to the prompts for your username and password. Progress messages appear as the command updates the connections named myconnection in each metabase. For example:

8.2 Validate the Upgrade Operation

Start Metabase Manager and verify that your metabases and loader connections are available in the Control Admin pane.

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

This chapter contains the following topics:

About Source Data and Metadata

Metadata is data that describes the contents of objects in a data set. For example, the metadata for a table includes such information as the name of the table and the names and data types of columns, relationships between this table and other objects, and so on. When working with objects in data sources not created within Oracle Warehouse Builder, you must first import metadata from those sources.

Oracle Warehouse Builder groups metadata for objects in different data sources in modules, which are visible in the Projects Navigator. 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.

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.

To import metadata from a source:

  1. Review the list of supported sources and targets in Table 1-1, 'Sources and Targets Supported in Oracle Warehouse Builder 11g Release 2 (11.2)' to determine if the source from which you want to extract data is supported in Oracle Warehouse Builder.

  2. Select an existing location or 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.

    You can import metadata from database objects, flat files, COBOL copybook files, and Oracle Warehouse Builder metadata.

  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'.

Subsequent Steps

For most data sources, after importing the metadata of the data objects, you can view the data stored in these objects.

To move the data using Oracle Warehouse Builder, you can design ETL logic that extracts the data from the source, transforms the data, and loads it into a target schema.

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

See Also:

  • 'Managing Metadata Dependencies' in the Warehouse Builder Online Help

To introduce the changed metadata into Oracle Warehouse Builder, right-click the desired module and select Import. As described in 'Reimporting Definitions', Oracle 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 Oracle 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 Table 1-1.

Oracle Database locations and file locations can be sources, targets, or both. For example, you can use a location as a target for storing data temporarily or as a staging table. Later, you can reuse that location as a source to populate the final target.

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

Automatically Created Locations

During installation, Oracle Warehouse Builder creates an Oracle location named OWB_REPOSITORY_LOCATION. This location provides the connection details to the Oracle Warehouse Builder workspace.

Types of Locations

You can deploy to several different types of locations. These locations are available on the Locations Navigator of the UI. Each location type has a different use:

  • Databases: Targets for either relational or dimensional systems, including objects such as tables and views, or dimensions and cubes. See, Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more details about these objects.

  • Files: Targets for storing data in delimited, fixed, or XML format.

  • Applications: Targets for Oracle E-Business Suite, PeopleSoft, Siebel, and SAP systems.

  • Process Flows and Schedules: Indicates an Oracle Workflow location.

  • Business Intelligence: Targets for deploying metadata derived from databases or Oracle modules. This is then used by business intelligence tools.

  • URI Locations: URI locations are used internally as part of the definition of other locations. You must not create any instances of it.

  • Agents: Specifies the location of a control center agent or an application server.

  • Transportable Modules: For defining transportable module sources and targets.

Creating Locations

You can create your own locations to use as sources or targets.

To create a location:

  1. In the Locations Navigator, expand the Locations node and then expand 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 <Object> Location.

    The Create <Object> Location dialog box is displayed.

    Or, right-click Locations and select New. This opens the New Gallery dialog box. Select a location type from the list of items and click OK.

    The Create <Object> Location dialog box is displayed.

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

    Provide the required connection details in the Details tab.

Using SQL*Net to Create Locations

When you create Oracle locations of type SQL*Net, you must set up a transparent network substrate (TNS) name entry for these locations. The TNS name must be accessible from the Oracle Warehouse Builder client home. Run the Net Configuration Assistant from the Oracle Warehouse Builder client home. See Oracle Database Enterprise User Security Administrator's Guide for information about using the Net Configuration Assistant.

After setting up the TNS name entry, restart the control center service so that it can pick up the changes. Furthermore, if the Warehouse Builder control center home is distinct from the Oracle Database home, then the same TNS name entry must be defined in the Oracle Database home as well. Run the Net Configuration Assistant from the Oracle Database home and specify the same TNS name there as well.

Note:

If the TNS names in the Control Center home and the Oracle Database home are different, then connection errors may occur during deployment and execution.

About Locations, Passwords, and Security

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

See Also:

Oracle Warehouse Builder Installation and Administration GuideMetabase Oracle Connection 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

  • End User Layer (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 owner of the target must have SELECT privileges on the table. Similarly, for locations pertaining to Transportable Modules sources and targets, users require additional privileges as described in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

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_HOME is the home directory for Oracle 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.

EUL Deployment

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

Registering and Unregistering Locations

Locations must be created during the design process. All modules, including their source and target objects, must have locations associated with them before they can be deployed. You cannot view source data or deploy target objects unless there is a location defined for the associated module.

Registering a location in a control center establishes a link between the workspace and the locations of source data and deployed objects. You can register a location in multiple control centers. See Oracle Warehouse Builder Concepts for more information about control centers.

You can change the definition of a location before it is registered. However, after the location is registered, you can change only the password. To edit other details of a location or one of its connectors, you must first unregister the location from all the control centers where it is registered. Unregistering a location deletes the deployment history of the location.

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

To register a location:

  1. Select the required project.

  2. Select Tools, Control Center Manager to open the Control Center Manager.

  3. Right-click the location and click Register.

    The Location dialog box is displayed.

  4. Check the location details carefully.

    Click Help for additional information.

  5. Click Test Connection to verify the connection details.

  6. Click OK.

To unregister a location:

  1. Select Tools, Control Center Manager to open the Control Center Manager.

  2. Right-click the location and click Unregister.

  3. Click OK to confirm the action.

Deleting Locations

To delete a location:

  1. Right-click the location in Locations Navigator 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 used. See 'Locations Registered in Multiple Control Centers' for more information.

  2. Verify that the location is not in use, unregister the location in the Control Center Manager, and then delete the location from Locations Navigator.

Locations Registered in Multiple Control Centers

After you register a location in a control center, the connection details of the location are locked. You can edit the connection details or delete the location only after unregistering it from the control center.

It is also likely that as part of your project requirements, a location is registered in multiple control centers. In such a scenario, you cannot edit the connection details of the location or delete the location unless you unregister the location from all the control centers where it is registered.

To see the details of all the control centers where a location is registered:

  1. On the Locations Navigator, right-click the location and click Open.

    The Edit Location dialog box is displayed.

  2. Click Registration to view the list of control centers where the location is registered.

About DB Connectors and Directories

A DB connector or Directory is a logical link 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.

To create a database directory, a user requires the CREATE DIRECTORY and DROP DIRECTORY privileges. When you create an Oracle Warehouse Builder user, these privileges are not automatically granted to the user. Therefore, the database administrator must explicitly grant these privileges to the Oracle Warehouse Builder user. For example:

where OWB_USER is an Oracle Warehouse Builder user performing the deployment.

Or else, a user with these privileges must create the link connectors and grant you access to use them. You can then create the connectors manually and select the database link or directory from a list.

Note:

To create a Database Link, a user requires the CREATE DATABASE LINK privilege. This privilege is granted automatically when an Oracle Warehouse Builder user is created.

See Also:

  • Oracle Database SQL Language Reference for more information about the CREATE DATABASE LINK command

  • Oracle Database SQL Language Reference for more information about the CREATE DIRECTORY command

To manually create a database connector:

  1. In the Locations Navigator, expand the Locations folder and then expand the subfolder for the target location.

  2. Right-click DB Connectors and select New DB Connector.

    Follow the steps in the Create Connector Wizard.

To manually create a directory connector:

  1. In the Locations Navigator, expand the Locations folder and then expand the subfolder for the target location.

  2. Right-click Directories and select New Directory.

    The Create Connector Wizard opens.

  3. Follow the steps and create a directory connector.

About Modules

Modules are grouping mechanisms in the Projects Navigator that correspond to locations in the Locations Navigator. A single location can correspond to one or more modules. However, a given module can correspond to only one metadata location and data location at a time.

The association of a module to a location enables you to perform certain actions more easily in Oracle Warehouse Builder. For example, group actions such as creating snapshots, copying, validating, generating, deploying, and so on, can be performed on all the objects in a module by choosing an action on the context menu when the module is selected.

Creating Modules

You can create a module from the Projects Navigator.

To create a module:

  1. Expand the Projects Navigator until you find the node for the appropriate metadata type.

    For example, if the source data is stored in 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<Module Type>.

    The Create Module Wizard opens.

    You can also right-click the desired node and select New. This opens the New Gallery dialog box. You can now select the item you want to create (module) and click OK.

    The Create Module Wizard opens.

  3. On the Name and Description page, provide a name and an optional description for the module. For non-Oracle sources and applications, you must not select the access method. Select from Native Database Connection and Oracle Gateway.

  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 about 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.

While using Oracle Warehouse Builder, you must not associate a module with a new location. For example, assuming your production environment uses different locations than those used by your development environment, then you must change the module associations when moving code from the production to the development environment.

To change the location associated with a module, you must edit the configuration properties of the module. Configuration properties define the physical information pertaining to the metadata.

To change the location associated with a module:

  1. In the Projects Navigator, right-click the module and select Configure.

    The Configuration Properties editor is displayed.

  2. In the Identification node, select a new value for the Locations property. If the desired location is not in the list, then edit the module and add the location.

See Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more information about 'Configuring Data Objects'.

Creating User Folders

After you create a module for a particular object such as an Oracle database, you can view all the associated objects under the database such as Mappings, Transformation, Tables, and so on.

You can also create your own folder, called User Folder, under a module. You can then define the required objects within the folder. For example, related tables and views that must be generated or deployed can be placed under a common folder. User Folders provide more flexibility in organizing objects within a module.

To create a User Folder:

  1. Right-click a module, and click New.

    The New Gallery dialog box is displayed.

  2. Select User Folder from the list of items and click OK.

    The Create User Folder dialog box is displayed.

  3. Provide a name and description (optional), and click OK.

The newly created user folder is now available under the module. The folder is empty with no nodes under it. All the objects that can be added under a module can also be added under the newly created user folder. For example,

To add a table:

  1. Right-click the folder, and click New.

    The New Gallery dialog box is displayed.

  2. Select Table from the list of items and click OK.

    The Create Table dialog box is displayed.

  3. Provide a name and description (optional) and click OK.

The newly created table is now available under the Tables node within the folder.

Within a user folder, you can also:

  • Create other user folders.

  • Copy valid objects from other user folders.

  • Copy valid objects from other compatible module types.

Using the Import Metadata Wizard

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 the import of secondary tables. When you import an external table, Oracle 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 Metadata Definitions from Oracle Database

Create an Oracle module to store the imported metadata.

To create an Oracle module:

  1. From the Projects Navigator, right-click Oracle under the Databases node and select New Oracle Module.

    The Create Module Wizard is displayed.

  2. On the Name and Description page, provide a name and an optional description for the module. Click Next to open the Connection Information page.

  3. Use the Connection Information page to select a database location for the module. To define a new location, click Edit. The Edit Oracle Database Location dialog box is displayed. See 'Edit Oracle Database Location Dialog Box' for the connection parameters to be entered in this dialog box.

    Click OK to open the Summary page.

  4. Verify the specified details on the Summary page and click Finish.

    The newly created Oracle module is now available under the Oracle node in the Projects Navigator.

Edit Oracle Database Location Dialog Box

Use this dialog box to specify the location details of an Oracle location.

Name

A name for the location.

Description

An optional description.

Connection Type

Lists the connections available for access to a database location. You cannot change the type after creating the location. There are four connection types available: HOST:PORT:SERVICE, Database Link, SQL*NET Connection, and OCI.

Note:

When the source and target locations are on the same host, be sure to identify them in the same way. Otherwise, the product treats them as separate locations and creates a database link between them. The database link may slow down the loading of data.

Common mistakes of this type include the following:

  • Specifying localhost for one location and the actual computer name (such as mycomputer-pc) for another location.

  • Specifying the domain inconsistently, for example, mycomputer-pc for one location and mycomputer-pc.us.oracle.com for another.

  • HOST:PORT:SERVICE: Makes a connection using the Easy Connect Naming method, which requires no prior setup:

    • User Name: The database user credential that has permission to access the schema location.

      When connecting to a database that does not have user names, enter any text as a mock user name.

    • Password: The password associated with user name.

      When connecting to a database that does not have passwords, enter any text as a mock password.

    • Host: The name of the system where the database is installed.

      If Oracle Warehouse Builder is installed on the same system as the Oracle database, you can enter localhost instead of the computer name. If the database resides on a remote system, then provide the internet protocol (IP) address of the remote system.

    • Port: The SQL port number for the database.

    • Service Name: The service name of the database.

    • Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. For example, orcl.us.example.com identifies the orcl database in the us.example.com domain. Select this option when connecting to a database on a different network.

  • Database Link: Makes a connection to another database using an existing database link. Select this method only when you do not have privileges that enable you to make a direct connection. You cannot deploy to a location that uses a database link. Not available for Oracle Business Intelligence or Discoverer locations.

    A database link is a schema object that contains information for connecting to a remote database. Database links are used in distributed database environments and enable a client to access two physical databases as one logical database.

    • From Location: An existing location where the database link is defined.

    • Database Link: The object name of the database link.

  • SQL*NET Connection: Makes a connection using a net service name previously defined using a tool such as Oracle Net Configuration Assistant. The net service name provides a convenient alias for the connection information. This method of connecting is the best for Oracle RAC installations.

    • User Name: The database user credential that has permission to access the schema location.

      When connecting to a database that does not have user names, enter any text as a mock user name.

    • Password: The password associated with user name.

      When connecting to a database that does not have passwords, enter any text as a mock password.

    • Net Service Name: The name of the predefined connection.

    • Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. For example, orcl.us.example.com identifies the orcl database in the us.example.com domain. Select this option when connecting to a database in a different network.

  • OCI: Makes a connection using Oracle Call Interface (OCI) to interact with an Oracle database. This connection type uses the JDBC OCI driver that is specific to your version of Oracle database and your platform. Use this method when you need a high degree of compatibility with an earlier release of Oracle. The JDBC OCI drivers enable you to call the OCI directly from Java.

    • User Name: The database user credential that has permission to access the schema location.

      When connecting to a database that does not have user names, enter any text as a mock user name.

    • Password: The password associated with user name.

      When connecting to a database that does not have passwords, enter any text as a mock password.

    • Net Service Name: The name of the predefined OCI connection.

    • Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. For example, orcl.us.example.com identifies the orcl database in the us.example.com domain. Select this option when connecting to a database in a different network.

Schema

The schema where the source data is stored or the target objects are deployed. The schema must be registered. By default, it is the user name schema.

When connecting to a type of database that does not have schemas, do not enter any value in this field.

Version

The version number of Oracle Database. Not available for non-Oracle database locations.

Test Connection

Attempts to make a connection using the values provided on this page. Hesston 4750 baler operator manual.

Test Results

A message that reports whether a test connection succeeded or failed.

Importing Metadata Definitions

Use the Import Metadata Wizard to import metadata from an Oracle database into the module.

To import metadata definitions from an Oracle database:

  1. Right-click the newly created Oracle module and select Import, then Database Objects.

    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.

  2. Complete the following:

Filter Information Page

Use the Filter Information page to limit the search using one of the following methods:

Selecting the Object Types The Object Type section displays the types of database objects that you can import. These objects 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 tables and views, select Tables and Views.

Search Based on the Object Name Use the Only select objects that match the pattern option to specify a search pattern. Oracle Warehouse Builder searches for objects whose names match the pattern specified. Use the percent sign (%) as a wildcard match for multiple characters and an underscore (_) as a wildcard match for a single character. For example, you can enter a warehouse project name followed by a percent sign (%) to import objects that begin with that project name.

Click Next to retrieve names that meet the filter conditions from the data dictionary. Oracle Warehouse Builder then 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. If you are reimporting definitions, previously imported objects appear in bold.

To search for specific items by name, click the Find Objects icon that looks like 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.

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 importing the table, you must bind the dimension to its implementation table. For more information about how to perform binding, refer to Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Summary and Import Page

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

You can specify additional properties for objects that have been imported into the module. Click Advanced Import Options to specify these properties. The Advanced Import Options dialog box is displayed. For more information about the contents of this dialog box, see 'Advanced Import Options'. For more information about reimporting definitions, see 'Reimporting Definitions'.

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 result, and lists the objects and details about whether the object was created or synchronized.

Click OK to accept the results. To save a metadata loader (MDL) file associated with this import, click Save. Oracle Warehouse Builder stores the definitions in the database module from which you performed the import.

Reimporting Definitions

Reimporting your source database definitions enables you to import changes made to your source metadata after your previous import. You do not have to remove the original definitions from the workspace. Oracle 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 activity. This includes any new objects, foreign keys, relationships, and descriptions you may have created in Oracle 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 is displayed.

    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.

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

  6. Click Finish.

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

    The report lists the actions performed by Oracle Warehouse Builder for each object. For objects that have been reimported, the Actions column displays Synchronized. You can also expand an object to see which elements have changed. For example, you can see which columns of a table have changed since the last import.

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

  7. Click OK to proceed or 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 Oracle Warehouse Builder workspace.

By default, all options on this dialog box are selected. Deselect these options 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. To ensure that these descriptions are not overwritten while reimporting the table or view definitions, you must select the Preserve Existing Definitions option.

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, see the following sections:

Advanced Import Options for Views and External Tables

Select these options to reconcile views or external tables:

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

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

    See Also:

    Oracle Warehouse Builder Concepts for more information about 'Workspaces'.

Advanced Import Options for Tables

Select these options to reconcile tables:

  • Preserve workspace added columns: To retain any columns added to the table in the workspace. Mac os high sierra download.

  • Preserve workspace added constraints: To preserve the constraints you added to the table in Oracle Warehouse Builder.

  • Import indexes: To specify additional details about how indexes should be imported. Importing indexes consists of the following options:

    • Preserve workspace added indexes: To retain any indexes added to the workspace table.

    • Import physical properties of indexes: To indicate how indexes should be imported. Select the Preserve workspace 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: 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: To specify additional details about how partitions should be imported. Importing partitions contains the following options:

    • Preserve workspace added partitioning: 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 workspace added physical properties of partitioning to indicate that all physical properties of the partitions in the workspace table should be retained.

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

  • Import descriptions: To import the descriptions of the table.

Advanced Import Options for Object Types

Select these options to reconcile object types:

Metabase Connect Oracle

  • Import descriptions: To import the descriptions of the object type.

  • Preserve workspace added attributes: To retain the attributes added to the object type in the workspace.

Updating Source Module Definitions

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

To update the database definitions of an Oracle module:

  1. Right-click the module, and select Open.

    The Edit Module dialog box displays.

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

    • Source Type: Identifies the location of 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 metadata location for the module. You can select a location from the list.

  3. To edit the data location, click the Data Locations tab. By default, the location name specified while creating the module is selected. To change the location, you can either select from other 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 in the dialog box.

Metabase Oracle Connection Manager

This chapter provides a generic description of importing metadata, which is identical for Oracle databases and other data source types. Follow these procedures when working with Oracle databases.

For information about importing metadata definitions from non-Oracle databases and other applications, see:

  • Chapter 3, ' Using Flat Files as Sources or Targets' for importing metadata definitions from flat files and COBOL copybooks.

  • Chapter 4, 'Connecting to Non-Oracle Data Sources Through Gateways' for connecting to and importing metadata from non-Oracle databases such as DB2 and SQL Server using Oracle Gateways.

  • Chapter 6, 'Connecting to Data Sources Through JDBC' for connecting to non-Oracle databases such as DB2 and SQL Server using JDBC connectivity.

  • Chapter 8, 'Integrating with Oracle ERP Applications' for details of importing metadata from Oracle applications including Oracle E-Business Suite, PeopleSoft, and Siebel applications.

  • Chapter 7, 'Extracting Data from SAP Applications' for details of connecting to a SAP application and extracting data.

  • Chapter 9, 'Importing Oracle Warehouse Builder Data into Business Intelligence Applications' for information about deriving business intelligence objects and deploying these objects into Oracle Business Intelligence Suite Enterprise Edition and Oracle BI Discoverer.

  • Chapter 10, 'Importing Design Definitions from Oracle Designer' for information about importing design definitions from Oracle Designer applications.