Oracle ODI

The Summary
===============
ODI….hmmmmmm……………..‘nuff said ….

The Detail
========
ODI is really good at

- Gluing the batch together – better than, say, Unix shell scripts
- Scheduling the batch
- Reporting on how the batch did
- Getting data from non-Oracle sources – although even here I would use other ‘Oracle’ methods were possible – e.g. heterogeneous services

Its not so good at
- Moving data from one Oracle source to another e.g. ‘live’ Oracle database to Oracle warehouse
- Checking that the results are correct
- Rerunning processes which different parameters (eg load the data from 20 days ago for this interface)
- The generation of scenarios (compiled code) means it is too easy to loose track of whether to designer version of the interface is the same as the as the scenario(compiled code) that is currently running in live.

Justification
=========

Hang on, moving data around is the one of the major functions of ODI. True.. but it is too simplistic. It will only do simple variations on ‘Get data from source table into staging table and from there to target table’. If you need to do anything more complex (for say performance reasons) you need to resort to either Jython or pl/sql . A good example is that a common method of adding data to large table is to create a table and then swap it with on of the target tables partitions.

Why would you want to learn yet another obscure language (Jython) and if you are going to write PL/SQL, it should be in the form of database packages and (at a pinch) database procedures anyway.

The other problem is it does not do much in the way of checking that the number of rows in landing table is the same as the number of rows in the target/fact table. It is very easy to ‘loose’ rows when doing joins to dimension tables and ODI will not tell you. This is not necessarily a problem if the developer is aware of such issues (he can simply tack a pl/sql check on the end of the scenario) but the ‘clicky, pointy’ nature of the development interface is not conducive to thinking too hard about what you are doing.

Lack of parameters – again this may be because I don’t know the tool well enough but it seems really difficult to run a process with different paras (as you would a pl/sql routine).
e.g. re- load the data for 20 days ago
A developer here always copies the interface changes the variables and then runs the copy – dooh!


Documentation
==============

It is argued that ODI is ‘self documenting’, a phrase that should always be viewed with suspicion. The arguement goes that Oracle is going to integrate (in future version) ODI and OBI so that it will be possible to trace back from a report the original data on the live system through the ODI/OBI system. I will leave you to form an opinion on that statement.
Lets just say (and this may be ignorance on my part) I find it really difficult to work out what interface loads data into which data warehouse tables at the moment.

How’s that , sorry it is not more positive , perhaps I should add that the bits I have mentioned that it is good at, it is really good at, but does that justify the cost ?........dunno

simple xml parser

CREATE OR REPLACE FUNCTION dwfacts.parse_xml(p_string IN VARCHAR2
,p_node IN VARCHAR2
)
RETURN VARCHAR2
AS
v_data VARCHAR2(4000);
v_node VARCHAR2(4000);
v_upper_str VARCHAR2(4000);
v_startnode VARCHAR2(4000);
v_endnode VARCHAR2(4000);
v_startnodelength NUMBER;
BEGIN
v_node := UPPER(p_node);
v_upper_str := UPPER(p_string);
v_startnode := '<'||v_node||'>';
v_endnode := '';
v_startnodelength := LENGTH(v_startnode);

v_data := SUBSTR(p_string,INSTR(v_upper_str,v_startnode)+v_startnodelength);
v_data := SUBSTR(v_data,1,INSTR(UPPER(v_data),v_endnode)-1);
RETURN(TRIM(v_data));
END;

External Table fro XML file

Turns out that an east way to load simple xml file is to treat in just like a plain text file
e.g.

CREATE TABLE dwstage.reh_table
(
accountid VARCHAR2(80)
,bets NUMBER
,losses NUMBER
,loyaltyPoints NUMBER
,playedHands NUMBER
,playedTournaments NUMBER
,rakeLosses NUMBER
,tableRake NUMBER
,totalRake NUMBER
,tournamentFee NUMBER
,transferIn NUMBER
,transferOut NUMBER
,wins NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS
(
records delimited by ""
badfile TMP:'table.bad'
logfile TMP:'table.log'
fields
(
accountid CHAR(80) enclosed by "<accountid>" and "</accountid>"
,bets CHAR(80) enclosed by "<bets>" and "</bets>"
,losses CHAR(80) enclosed by "<losses>" and "</losses>"
,loyaltyPoints CHAR(80) enclosed by "<loyaltypoints>" and "</loyaltypoints>"
,playedHands CHAR(80) enclosed by "<playedhands>" and "</playedhands>"
,playedTournaments CHAR(80) enclosed by "<playedtournaments>" and "</playedtournaments>"
,rakeLosses CHAR(80) enclosed by "<rakelosses>" and "</rakelosses>"
,tableRake CHAR(80) enclosed by "<tablerake>" and "</tablerake>"
,totalRake CHAR(80) enclosed by "<totalrake>" and "</totalrake>"
,tournamentFee CHAR(80) enclosed by "<tournamentfee>" and "</tournamentfee>"
,transferIn CHAR(80) enclosed by "<transferin>" and "</transferin>"
,transferOut CHAR(80) enclosed by "<transferout>" and "</transferout>"
,wins CHAR(80) enclosed by "<wins>" and "</wins>"
)
)
LOCATION ('table.xml')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Multiple time rows from dual

SELECT TRUNC(SYSDATE - 4 / 1440, 'MI') - (LEVEL - 1) / 1440 starttime
FROM SYS.DUAL
CONNECT BY LEVEL <= 36)

dynamic sampling

http://www.oracle.com/technology/oramag/oracle/09-jan/o19asktom.html

Bash date arithmetic

Not really oracle but useful nevertheless
date -d "yesterday" +"%d %B %y"


http://www.walkernews.net/2007/06/03/date-arithmetic-in-linux-shell-scripts/




Convert comma separated list into an array - dbms_utility.comma_to_table

Simple way to convert a comma separated list held in a single variable into its component parts.
pl/sql equivalent to php explode
e.g.

SET SERVEROUTPUT ON
DECLARE
l_list1   VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
l_list2   VARCHAR2(50);
l_tablen  BINARY_INTEGER;
l_tab     DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

DBMS_UTILITY.comma_to_table (
   list   => l_list1,
   tablen => l_tablen,
   tab    => l_tab);

FOR i IN 1 .. l_tablen LOOP
  DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;

DBMS_UTILITY.table_to_comma (
   tab    => l_tab,
   tablen => l_tablen,
   list   => l_list2);

DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);



END;

http://www.oracle-base.com/articles/9i/UsefulProceduresAndFunctions9i.php