OWB - Creating an ODBC Non-Oracle Module as data source (with Excel)

Card Puncher Data Processing

About

Oracle Warehouse Builder use the gateway capabilities of the oracle database to create Non-Oracle Module.

This article will create an ODBC Non-Oracle Module on Excel data source.

Set up the ODBC connectivity to a data source

To set up the ODBC connectivity to a data source, you have perform the steps :

  • Create the Excel Sheet
  • Create an ODBC System Dsn Connexion
  • Create the Heterogeneous Services Initialization File
  • Set up the listener on the agent
  • Create a Net Service Name

of this article : Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)

Creating an ODBC Module

After you set up the ODBC connectivity to a data source, you must create an ODBC module to import the metadata from this data source. The ODBC node is available under the Databases node in the Projects Navigator. To create an ODBC module:

  • Right-click ODBC and select New ODBC Module. The Create Module Wizard is displayed.
  • In the Name and Description page, provide a name, description (optional), and select a module status.
  • In the Connection Information page, either select an existing location or click Edit to open the Edit Non-Oracle Location Dialog Box and provide the connection details (see the paragraph below for more details)
  • In the Summary page, verify the details and click Finish.

At the end of the module wizard, you can see than the Non-Oracle Module is an Oracle Database - Gateway (Heterogeneous services) application.

Owb Hs Module Definition Complete

The newly created ODBC module is now available under the ODBC node.

Edit Non-Oracle Location Dialog Box

Select the Connection Type to be one of :

  • Host:Port:Service,
  • Database Link,
  • or SQL*Net.

Host:Port:Service

If you selected Host:Port:Service, then provide the following connection details:

  • User Name/Password: You can provide a dummy user name and password as you are not connecting to an Oracle database.
  • Host: Machine on which the database is hosted.
  • Port: SQL port number of the database.
  • Service Name: The SID_NAME that you specify in the listener.ora file.
  • Schema: You can leave this field empty if you are not importing from a schema.

Owb Host Port Service Odbc Connection

If you selected Database Link, then provide the following connection details:

  • From Location: An existing location where the database link is defined
  • Database Link Name: The object name of the database link
  • Schema: The schema where the source data is stored or the target objects will be deployed.

SQL*Net

For SQL*Net, provide the following connection details:

  • 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 require password, enter any text as a mock password.
  • Oracle Database - Net Service Name (tnsname): 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. Select this option when connecting to a database in a different network.

Owb Net Service Odbc Connection

Click Test Connection to verify that you have provided the correct details and click OK to set the connection details.

Import

When you want to import the metadata, you will see the name define in the creation step of the excel file.

Owb Import Odbc Excel Table

Documentation / Reference





Discover More
Card Puncher Data Processing
OWB - Source

Location Node : Oracle Database : Any Oracle Database Non-oracle Database : Any database accessible through Oracle : DB2, DRDA, Informix, SQL Server, Sybase, and Teradata. ODBC...
Card Puncher Data Processing
Oracle Warehouse Builder

Owb Documentation - Understanding Data Quality Management White...



Share this page:
Follow us:
Task Runner