BO as an ETL tools ??? 1

[Benouche]

Hello,

I need to extract and aggregate datas from an Oracle db to another Oracle db (let's say it's an ETL task).

Looking for an easy an quick solution, I thought of extracting and transforming datas periodically using BO.
I can export this to a text file and then load those text files to the Oracle db.

any reaction on this process ?

I did'nt found an easy way to do it inside Oracle ...
Is there any simplest/greatest solution around there ?

Thanks,
Benouche

 

[blom0344]

Hello Benouche,

ORACLE supports the set-up of database-links between databases which you could possible use to exchange data.
(update/insert strategies) Otherwise it would probably be quicker to export dumps from one table and load them in the other environment (won't help with aggregating , though) Everything BO has to offer PRIOR to reporting functions (so from the data-cube) is based on pure SQL, so with the right SQL scripts you should be able to get every possible dataset you can attain using BO as an ETL tool. (Feels like quite a bit of work......) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[skrandel]

Traditional BO is NOT a good ETL tool. It wasn't made to be one.

Their new Data Integrator tool is quite nice. It is an ETL product. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[pabloj]

The best way is to use dblinks, you can however use BO to design and check your query, and then run/schedule the SQL in Oracle Stick to your guns

 

[Benouche]

Hello Pabloj !

Thanks for your answer... it sounds great.

Tell me if I understood well :
I design my query in BO, and I catch the sql code corresponding. I then use this code in dblinks.

Can you detail me the main steps for using dblinks this way.
How do I schedule the task (Windows schedule tasks ??)
Any sample code ? I'm very beginner in Oracle !

Thanks for your help !!!!

Benouche.

 

[pabloj]

Create a dblink like: create database link {dblink name}
connect to {account} identified by {password} using 'T:{ip address:dbname}';

(better use dbastudio or enterprise manager)

then run the query as:

insert into ... as select ... from user.table@dblinkname

you can use a dbms_job to schedule a procedure ... Stick to your guns