Skip to main content

ORE-OBIEE Integration

Let us say I want to have prediction and linear model plots of data table in Oracle having data of height and weight using ORE, to use server memory execution rather than client memory execution and show the result in OBIEE reporting tool.

Prerequisite:

1. I have ORE in 12c pdb configured with a user, say RUSER2 with RQROLE and RQADMIN roles.
2. OBIEE 11.1.1.7.0 is configured.
3. Table PRED_TBL is created with height, weight and ID columns.
4. I know R,ORE and OBIEE

Let us start with scripts:

#olm is a ORE script which returns predicted table.
begin
sys.rqScriptDrop('olm');
sys.rqScriptCreate('olm', 'function(dat) {
raw<-dat
library(ORE)
lmdl<-lm(WEIGHT~.,raw)
lpred<-predict(lmdl,raw)
pred_tbl<-cbind(raw,lpred)
pred_tbl
}');
end;

#Creating view which calls olm script with PRED_TBL as input
create or replace view R_PREDICT as
select HEIGHT, WEIGHT, ID,LPRED from table( rqTableEval(
        cursor(select * from PRED_TBL),cursor(select 1 as "ore.connect" from dual),
        'select 1 HEIGHT,2 WEIGHT, 3 ID,4 LPRED from dual',
        'olm'));
     
     

##lmpmg returns plots of liner model.
 begin
 sys.rqScriptDrop('lmpng');
 sys.rqScriptCreate('lmpng',
'function(dat){
 plot(lm(WEIGHT~.,dat))
  }');
end;

## PNG_TEST view will hold the plots in blob,
create or replace view PNG_TEST as
select ID,image from table( rqTableEval(
        cursor(select * from PRED_TBL),cursor(select 1 as "ore.connect" from dual),
        'PNG',
        'lmpng'));    

##ORE Rpd is the simple self explanatory OBIEE RPD.
ORE.rpd

Thats it! Create Table view in presentation layer from each of above views and have fun!

Don't forget to give reference if you are using elsewhere or leave comment if this information helped
:-)

Hari Prasad :-)

Comments

Popular posts from this blog