Business Intelligence – Oracle

Archive for the ‘OWB’ Category

Oracle Data Integrator Enterprise Edition – New bundling – ODI and OWB merged

Posted by Venkatakrishnan J on January 31, 2009

Well, this news has been floating around for sometime and now it has been officially released. Oracle Warehouse Builder and Oracle Data Integrator have been merged together into a single product called Oracle Data Integrator Enterprise Edition. This has a primary impact from a product pricing/purchasing standpoint. Going forward its not possible to purchase OWB (Enterprise ETL) and ODI seperately(i believe this is the case, not completely sure though). The official details of this are available below

1. Oracle Data Integrator Enterprise Edition

2. Oracle Data Integrator – Statement of Direction

So, now with a single product(from a licensing standpoint) it is possible to deploy both OWB and ODI (customers have more flexibility in their deployments which is good). But effectively both the components will have their seperate releases (according to the SOD above). The actual product merging would happen only after a seperate individual release of both the products. More details are available in the statement of direction.

Posted in All Posts, Oracle Data Integrator, OWB | 1 Comment »

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.

Posted in All Posts, OWB | 17 Comments »

Oracle Warehouse Builder 11g/10g – Ebusiness Suite Connector

Posted by Venkatakrishnan J on August 31, 2007

I was speaking to one of the users of OWB yesterday and the conversation drifted towards the Ebusiness Suite connector that OWB 11g/10g provides to the end users. The user wanted to know how is the ebusiness connector different from a normal connection to an oracle database? It made a lot of sense to me considering the fact that OWB connector does not provide out of the box ETL mappings (based on business modules in ebusiness suite like BI Applications) to a target warehouse. The only way to find that out was to use the connector and see what it does differently. I did exactly that and thought that this deserves a blog entry in itself. Ebusiness suite connector does a lot of things other than connecting to the ebusiness database. It logically groups all the tables according to the business functions.

The first step i did was to create a connection to an ebusiness suite database using the Application module. The connection details would be exactly same as what you would give for an oracle database.

         screenshot-4.png

         screenshot-6.png

         screenshot-5.png

Once the connection details are specified, the connector gives a window that would look like this

         connector-3.png

As you see above, the connector provides 2 options. First one is an option where in one can choose a business domain and the 2nd one is an option similar to an ordinary table import wizard from a schema. The 1st one is the one that is unique to the connector i.e identifying and segregating the tables based on the business modules. The business modules would look like this

         connector-2.png

In our illustration i would choose the Order Entry (OE) module and import all the tables under that.

         connector-5.png

         connector-4.png

As you see above, the connector helps a developer in working on the tables that are specific to his module. It reduces a lot of effort, considering the fact that ebusiness suite has a huge number of tables. Apart from making the life of a developer easy by segregating the tables and views, the connector can also write back to an ebusiness suite instance. It can deploy objects to the concurrent manager. One can use the ebusiness suite objects both in the mappings and also in the process flows. One can access the documentation about the connectors here

Posted in All Posts, OWB | 9 Comments »