Business Intelligence – Oracle

Archive for September 4th, 2007

OWB 10g/11g – Heterogeneous Data Sources – Excel, MySQL and SQL Server

Posted by Venkatakrishnan J on September 4, 2007

As you would be knowing, Oracle Warehouse Builder can connect to heterogeneous data sources like Excel, MySQL and SQL Server. In this article, we would be seeing how to go about setting the connection to these datasources via ODBC. The steps are uniform and infact it requires a bit of knowledge on Heterogeneous Connectivity of Oracle Database. My example is based on a ODBC connection to an Excel file. But the steps should be same for any other datasource which you are trying to import via ODBC. The list of steps are

1. Create a System DSN pointing to your Excel (SQL Server, MySQL etc) data source.

2. Create a Listener entry in the listener.ora of the database. This step is to make the listener to listen for new connections to this DSN. In my case, the listener.ora would look like this

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = EXCEL)
(ORACLE_HOME = D:\oracle\BISE1\db)
(PROGRAM = hsodbc)
)

(SID_DESC =
(SID_NAME = orcl)
)
(SID_DESC =
(SID_NAME = bise1db)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

I have added the entry (marked in red) above to my listener.ora file

3. Create a tnsnames.ora entry. In my case, the entry would be like this

EXCEL =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SID = EXCEL) ) (HS = OK) )

As you note above, the SID is the DSN name (EXCEL) that we created in step 1.

4. The next step is to go to the folder ORACLE_HOME/hs/admin and create a new file of the name initEXCEL.ora (init.ora). The file would contain the following

HS_FDS_CONNECT_INFO = EXCEL
HS_FDS_TRACE_LEVEL = 1

The HS_FDS_CONNECT_INFO points to the DSN name that we created in step 1.

5. Restart the listener. Connect to sqlplus and test whether the Heterogeneous connection has been properly setup. To do this, log in as a known user. In my case, i would log in as SH.

Conn SH/SH

Create database link EXCEL using ‘EXCEL’ -> This is to create a database link using the tnsnames entry that we created in step 3.

select table_name from all_tables@EXCEL -> This should return all the tables that you have connected via the DSN. In Excel files it would list all the worksheets.

6. Now that the HS has been setup in the database, the next step is to import the tables via OWB. Open OWB design center and connect to the repository. Go to the Non-Oracle databases tab and create a new connection using ODBC.

7. Enter the connection details of the DSN that we created. Remember to use the DSN name as the service name.

8. Then import the tables/worksheets that you want.

As you see here, the connection setup is uniform for all the databases. The only step that would vary is the step 1. In 11g one can explicitly use an external database gateway to connect if one does not want to use ODBC. The documentation for this can be found here.

Advertisements

Posted in All Posts, OWB | 17 Comments »