Business Intelligence – Oracle

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

17 Responses to “OWB 10g/11g – Heterogeneous Data Sources – Excel, MySQL and SQL Server”

  1. Mohamed Hamdy Hassan said

    This article is very good and help me for connecting to non oracle database. thank you for the web site staff.

  2. David Pedro said

    Conratulations on the article, it is very good. Because I have one problem, this article help me for connecting to mysql database. I can see the schema tables in Oracle Warehouse Builder but I can´t import the data to OWB. It don`t show any error and it don´t create the tables in OWB. Can you help me?

    Thanks.

    • Stéphane DECALUWE said

      Very good article !!! Thank you very much.
      But I have the same problem than David Pedro.
      I see the schema tables, I select the tables to import, I click on “finish” and… nothing happens. No tables created.
      Can somebody help me ?

      Thanks

    • Stéphane DECALUWE said

      clarification : the import don’t work with the tables, but works with the view…

  3. sebastian trzeciak said

    good article -> helped me a lot! thanks

  4. David Barragan said

    Greetings! thanks for the article … I’ve got hs working to a MySQL database … (I “snap” a series of tables everyday using a dblink via this HS) I can’t seem to get my OWB instance (same machine as my database) to see this however… I tried your example above (sorry can’t see the details in your images…) my verified HS is called “mysqlSORDdev_cmdb”
    If I set type to “dblink” i can get a successful “test” using a previously created public dblink that uses the HS.

    when I setup the “Nn-Oracle location: MYSQL_HS_LOCATION, I assume
    1) I should use Type: HOST:PORT:SERVICE
    2) my “User Name” should be the username at the MySQL database
    3) my password is the password at the MySQL database
    4) host is the database host where the HS service was defined (same host as my OWB instance)
    5) port 1521
    6) Service Name is the DSN I created that I use in the dblink (i.e., “mysqlSORDdev_cmdb”)

    when I hit “test connection” I get: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][MySQL][ODBC 3.51 Driver]Access denied for user ‘SORD_RO’@’DSS0W023’ (using password: YES) (SQL State: S1000; SQL Code: 1045)
    ORA-02063: preceding 2 lines from OWB_790

    any Ideas?

  5. John Mattausch said

    J,
    The first three screenshots that are not displayed. Could you change the permissions on these, or add them if they are missing?

    Thanks,
    John

  6. luigi cavasin said

    e per informix?

  7. luigi cavasin said

    sorry
    for informix odbc driver configuration?

  8. Ely said

    About MSQL connection via ODBC, setting up Non-Oracle Location.

    Adding a Non-Oracle Database (MySQL) on Oracle Warehouse Builder I got the following error:
    [Generic Connectivity Using ODBC][MySQL][ODBC 3.51 Driver]Access denied for user ‘user’@’host′ (using password: YES) (SQL State: S1000; SQL Code: 1045)
    ORA-02063: preceding 2 lines from OWB_790

    My solution was to type username and password including in double quotes “! Now it works.

    I hope this can help somebody….

  9. himawan said

    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from OWB_32

    i have that kind of error, what should i do?

    thx…

  10. himawan said

    can anyone explain to me how to connect to SQL server?? in details?/

    thx a lot….

  11. lars said

    good info. Thanks. also fyi, you can use the SQL Drill addin (http://www.sqldrill.com) to connect to non oracle data sources. Its a freeware that runs inside Microsoft Excel. good luck

  12. salih said

    In 10g it is working for me but i am not sure what i am doing different in 11g it is not working

  13. Oscar said

    Hi

    do you have all installed on windows?, i need to connect Warehouse Builder installed on unix with SQL Server, how can i do that?

    Thanks.

  14. adzlan said

    hello there… i want your help.. how to link the heterogenous DB.. How to link mysql to oracle? what oracle what i should use? please help me…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: