Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 30, 2008

Oracle’s Warehouse Builder for any kind of your Extraction-Transformation-Loading needs within Oracle database

Filed under: Oracle Business Intelligence — H.Tonguç Yılmaz @ 1:06 pm

Warehouse Builder is a cost-free(part of the database license) ETL tool which produces PL/SQL when its mappings are deployed. The components you use to develop your data flows are based on Oracle’s SQL and PL/SQL capabilities.

In my opinion you really don’t need to own a warehouse to use OWB, with 11g now it is even coming installed with your database. So if you are also an Oracle database minded developer you better plan to check OWB 11g, similar to Apex I can guarantee that you will be having lots of fun and most probably some opportunity to develop your knowledge :)

Here is a hint showing how it can be possible to automate generation from your standard SQLs to OWB mappings.

And here you will find some important OWB hints.

Advertisements

December 29, 2008

Using Pipelined Table Functions for Siebel’s Audit_Read Data Transformation

It has been a while, I thought it is time that I should write on something? :)

25-12-2008 was my second anniversary on this blog: 233 posts on 18 categories resulted 383 approved comments and ~330,000 page views and lots of good friendships until now. So many thanks to all who contributed!

I was very surprised that I didn’t write on one of my best features of Oracle until today, the Pipelined Table Functions. Beginning with the 9i days I benefited from Pipelined Functions for lots of complex transformations needs.

Here with this post I will share a recent example need to demonstrate this feature. Siebel as a source system for our warehouse has a logging table called S_AUDIT_READ, this table has a CLOB column AUDIT_LOG which holds a string to be parsed with the rules mentioned in the below link.

How to parse audit log value (Doc ID 555553.1)

It can be possible to parse the string in several methods as usual, with even a single SQL of course. But PL/SQL is more readable/maintainable compared to SQL, so Pipelined Table Fuctions is always a good alternative to transform a massive data, in terms of performance also. In the below demonstration I also got rid of the algorithm mentioned above because of the cases in our data.

Siebel’s Audit_Read Data Transformation with Oracle’s Pipelined Table Functions

With the help of Pipelined Table Functions while reading the source data in parallel, it is possible to transform and insert to target table through pipes. For more examples please visit:

http://www.oracle-developer.net/display.php?id=207

http://psoug.org/reference/pipelined.html

http://www.oracle.com/technology/products/oracle9i/daily/may30.html

Just before closing, I wish a very happy new year to all of us! :)

Blog at WordPress.com.