Business Intelligence – Oracle

Archive for January 6th, 2009

Oracle Data Integrator 10.1.3.5 and Oracle OLAP – RKM and IKM – Using Oracle OLAP as a Target – Part 2

Posted by Venkatakrishnan J on January 6, 2009

In the blog entry here, we had seen how the new RKM for Oracle OLAP works. Today, we shall look at the other KM(IKM) which would be used for loading data directly into Analytic Workspaces. I had noted earlier that, the Oracle OLAP RKM just reverses the OLAP cube to its underlying source tables. So, basically, it is not Oracle OLAP aware per se and it does not support all kinds of data extract and load. This is the major difference in the Oracle OLAP RKM and Essbase RKMs. Essbase RKMs treats Essbase itself as multidimensional data store. So, all data extract through MDX, Report Scripts etc are supported. We shall look at how ODI uses the Essbase RKMs in a later blog entry. Lets proceed with our test on the Oracle OLAP IKM which would be used to load an Oracle OLAP AW Cube. To test this out we shall start out with a new cube in the Global Analytic Workspace. This cube mapping is provided below as a reference.

In our case, the UNITS_FACT_TGT is a table that is used as a source for the cube. We shall create another table UNITS_FACT_SRC which would be used as source to populate data into this target table. Our idea is to simulate an environment wherein the target table would be populated temporarily before loading into Oracle OLAP. This target table would contain only the incremental data.

CREATE TABLE UNITS_FACT_SRC AS SELECT * FROM UNITS_FACT;
CREATE TABLE UNITS_FACT_TGT AS SELECT * FROM UNITS_FACT WHERE 1=0;

Now, lets create an interface which would be using the above 2 tables (one as a source and the other as a target).

In the flow control, ensure that you are choosing the “IKM Oracle AW Incremental Update”.

This is very similar to the normal Oracle to Oracle data load. The only addition are the 4 options which are specific to AW loading. The 4 options are

1. REFRESH_CUBE – This will enable the automatic start of data load into Oracle OLAP at the end of data load into the target table.
2. AW_OWNER – Owner of the Oracle OLAP AW
3. AW_NAME – The Analytic Workspace name
4. CUBE_NAME – The cube whose data needs to be refreshed.

This IKM assumes a straight data load into the Oracle OLAP. The data load cannot be customized. The only way to do that is to call custom OLAP specific Pl/SQL packages. But again, i was expecting that to be available in the IKM itself. Now lets execute the interface and see what the Interface does under its covers.

Let us take a look at the Olap Refresh option to see the exact code.

declare
xml_clob clob;
xml_str varchar2(4000);
isAW number;
v_awname varchar2(50);
v_owner varchar2(50);
v_cube varchar2(50);
v_dimension varchar2(50);
v_line varchar2(250);

cursor c1 is select DIMENSION_AW_NAME from
olapsys.all$olap2_aw_cube_dim_uses
where
aw_owner = v_owner
and aw_name = v_awname
and aw_logical_name = v_cube
and DIMENSION_AW_NAME<> 'TIME';
begin

v_awname := 'GLOBAL';
v_owner  :=  'GLOBAL';
v_cube   :=  'UNITS_FROM_ODI';

sys.dbms_aw.execute('AW ATTACH '||upper(v_owner)||'.'||upper(v_awname)||' RW');
DBMS_LOB.CREATETEMPORARY(xml_clob,TRUE);
dbms_lob.open(xml_clob, DBMS_LOB.LOB_READWRITE);
v_line := '  <BuildDatabase  Id="Action7" AWName="'||upper(v_owner)||'.'||upper(v_awname)||'" BuildType="EXECUTE" RunSolve="true" CleanMeasures="false" CleanAttrs="true" CleanDim="false" TrackStatus="false" MaxJobQueues="0">';
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
dbms_output.put_line(v_line);
open c1;
loop
fetch c1 into v_dimension;
exit when c1%notfound;
v_line := '    <BuildList XMLIDref="'||v_dimension||'.DIMENSION" />';
dbms_output.put_line(v_line);
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
end loop;
close c1;
v_line := '    <BuildList XMLIDref="'||v_cube||'.CUBE" />';
dbms_output.put_line(v_line);
dbms_lob.writeappend(xml_clob, length(v_line),v_line );
dbms_lob.writeappend(xml_clob, 18, '  </BuildDatabase>');
dbms_lob.close(xml_clob);
xml_str := sys.interactionExecute(xml_clob);
dbms_output.put_line(xml_str);
dbms_output.put_line('finish');
sys.dbms_aw.execute('AW DETACH '||upper(v_owner)||'.'||upper(v_awname));
end;

The above is a java code and you would see calls to the DBMS_AW package (for attaching and detaching the AW). The actual data load is done through a custom XML load parser. This can be customized according to our needs. But again this is not something that can be done from the front end. This would have to be changed in the backend class files of ODI.

After i saw how the RKM worked, i was pretty sure how the IKM would work. The above confirmed my guess. In a future blog entry i would be comparing this with the Essbase specific RKMs and IKMs.

Update: John Goodwin has an excellent series of articles on ODI and Essbase,Planning connectivity. I would recommend everyone to check that out. Thanks John for letting me know.

Posted in All Posts, OLAP, Oracle Data Integrator | 2 Comments »