Friday, January 11, 2008

OBIEE using oracle stored procedure to fill report

Oracle stored procedures by default don't return recordsets, but sometimes a query is to slow or complex. Especially if it contains multiple outerjoins or complex statistical calculations. (yes, if know how to handle table types there is an other workaround see: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:666224436920  )

In this demo I will show you a workaround. First you need to tables, one for the parameters and one for the resultset.



CREATE TABLE SH.DEMOPAR
( ID NUMBER, SDATE DATE DEFAULT trunc(sysdate
- 183), EDATE DATE DEFAULT
trunc(sysdate), MACHINENUMBER VARCHAR2(30 BYTE) )
;

CREATE
UNIQUE INDEX SH.DEMOPAR_PK ON SH.DEMOPAR (ID) ;
CREATE
TABLE SH.DEMODATE
(ID NUMBER, MDATE DATE, VALUE NUMBER, INTERPOL NUMBER, C1
NUMBER, C2 NUMBER ) ;
CREATE UNIQUE INDEX SH.DEMODATE_PK ON SH.DEMODATE
(ID, MDATE) ;

For the parameter table you need to construct an XML file for the write-back functionality:

-<-webmessagetables sawm="com.siebel.analytics.web/message/v1">
-<-webmessagetable lang="en-us" table="Messages" system="WriteBack">
-<-webmessage name="demosavepar">
-<-XML>
-<-writeBack connectionPool="CP_dateDemo">
-<-insert>INSERT
INTO DEMOPAR VALUES('@{c0}','@{c1}','@{c2}','@{c3}')
-<-update>UPDATE DEMOPAR SET SDATE = '@{c1}',EDATE =
'@{c2}',MACHINENUMBER = '@{c3}' WHERE ID= '@{c0}'
-<-/writeBack>
-<-/XML>
-<-/webmessage>
-<-/webmessagetable>
-<-/webmessagetables>


Alter the "-<-" in "<" and place the file in ?:\{OracleBI}\web\msgdb\customMessages. Next restart the presentation server. Add both tables to two separate connection pools in your repository! Be sure the cache is off.

Make one report for the parameter table and add the write-back functionality.
Next go to the connection pool for your datatable and open the "Execute before query" tab:


Here you can put your script to execute the stored procedure:
Now you can put both reports on a dashboard!
Till next time

12 comments:

Prakash said...

Hi,

Can you help me understand the reason for seperate connection pool?

Thanks in advance
-P

John Minkjan said...

prakash,

The reason I use two seperate connection pools is that a 'normal' connection pool normally doesn't have write rights on the database.

regards John

Pankaj said...

We have a requirement where we need to call backend procedure & pass few parameters from a report on a buton click.
In general we can call the procedure thru a function & pass parameters too but how to implement the same in OBIEE?

Can you please list the changes that need to be done on the Report/RPD level.

I have heard initialization block can be used, but how if applicable?

FYI: on the list of parameters sent the procedure truncates a table & loads it with refreshed values from other tables. Also the procedure doesnt return any value.

Any help/hint is highly appreciated.
Thanks in advance,

John Minkjan said...

Hi Pankej,

You can add the exection code for your SP to the insert or update statement in the write-back XML.
fi:
INSERT
INTO DEMOPAR VALUES('@{c0}','@{c1}','@{c2}','@{c3}')
commit;
Begin
myProcedure;
end;

regards John

Rits said...

I have requirement where I need to show a dropdown from a column of one table and write it to another column based on selection.

I do understand that OBIEE is not a data-entry tool but this is kind of integrated requirement. So it is something like

==> Table1 --> Column1 --> Values of this columns are displayed as list of value / drop down.
==> User selects one value and clicks Go / Update.
==> This will be updated in database in Table2 ==> Column1

Can we do this in OBIEE?

John Minkjan said...

@Rits

The writeback scripts is independent of the originating table. So if you can bring the value of your dropdwon to a (hidden) report you could use that to fill your second table.

Regards

Kwl_PranayJain said...

Hi John,
Thanks a lot D info it was really helpful But in mY database i need to consider few exceptions so i was wondering if i could use PL/SQL with XML..??? If Possible can u give a Sample XML wid PL/SQL integrated FOR UPDATE/INSERT Write Back in OBIEE..
I would really Appreicate it... Thanks in Advance...

Regards
Pranay Jain

John Minkjan said...

@Pranay Jain,

have a look at the comment I made blow the remark of Pankaj.

regards

John

orcl said...

"Oracle stored procedures don't return recordsets"
=> this is wrong !!

just an example over there :
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:666224436920

Salih said...

I used to think Oracle BI is not capable of prompting values that will be used by stored procedures or table functions, but write back property (with disabled cache) might be a workaround for that problem. However Oracle should really consider making more effective prompts, maybe ones that can be created in the rpd file.

Jawad Essani said...

If I have boh reports on Dashboard.

When the user enters the values for the first report and saves it, would my 2nd report automatically refresh and display results.

For Users first report will be as if they are entering prompts and click save which should automatically refresh the second report?

Anonymous said...

Hi John

i'm new to obiee

where u created demo.fncfilldemodata,