Thursday, July 16, 2009

OBIEE write back, solution for insert and update in database

Here is my solution for write back (insert and update). I'm just trying to do some table data maintenance, and that is, insert and update one after the other. This solution includes two reports (one for insert and one for update, and two XML files). It's very fast to insert data, or update.

1. At first, we create table in the database:

create table wb_test(col1 number, col2 varchar2(50), col3 varchar2(255))

alter table wb_test add constraint wb_test_pk primary key (col1)

create sequence wb_test_seq start with 1 increment by 1

2. In Administrator, we need to disable cacheable option:

3. In Answers the user must have write back privilegies

4. In this write back insert/update solution user inserts new data through the one report and through the other report is doing update. Both reports are for the same database table. Write back option is taking XML file that we put in ..\OracleBI\web\msgdb\customMessages folder. Inside this XML there are SQL's for insert and/or update.

5. Insert report

Here is Id=0 in the filter. What does it mean? This insert report is taking only rows that have Id=0. So, for 'insert new row' we'll update this Id from 0 to some sequence value. You'll see later that we put update statement in insert XML. This dummy row is a necessary to be in the table, before (when the table is empty) and after we update (this is resolved by a after update trigger) because the table has the primary key (id), otherwise we have problems.

OBIEE has some problems with null values in the database. In this case we have 0 – null – null and we need to put in the col2 and col3 columns expression ifnull(WB_TEST.COL2,'n/a') and ifnull(WB_TEST.COL3,'n/a') to get null value from database and represents it as n/a.

Without this workaround if we try to insert something (test) we would have:

and we get an error:

We can ignore/fix this error, with ifnull(WB_TEST.COL2,'n/a') in the col2 and ifnull(WB_TEST.COL3,'n/a') in the col3. Try it.

Then we get:

N/a is null, so if we delete n/a from both columns we are saving null in the database with new inserted Id, and this works fine, like in case if we put some value:

After successful insert the next thing that we see is again dummy Id=0 row. This means that OBIEE saved our insert data in the database (mmarkic) i automatically give us dummy Id=o row. If we don't want to insert we close the insert report. After the user push insert button, XML code is generated.

Insert XML:

After insert the state in the database is:

We see new inserted row with Id 34, and a new dummy row appeared.

6. Update report

In this update report we show only inserted rows, with Id that is greater then zero (Id>0). We can update them. In case we don't have inserted rows this report would return no results.

In our case we have one row to update..

We have changed value from mmarkic u mmarkic2:

After insert we see in the report new data and the state in the database is:

Update XML:

XML files location:

We set writeback template here:

7. Database trigger:

create or replace trigger wb_test_af_upd_tr
after update
ON wb_test
cursor c1
is select 1 as num
from wb_test
where col1=0;
v1 number;
open c1;
fetch c1 into v1;
if v1=1 then
insert into wb_test values (0,null,null);
end if;
end loop;
close c1;
when others then

We use dummy row insert method.
After we insert row (we update dummy row) this new row we don't see in the update report because of Id=0 filter.
After update of dummy row we activate trigger in wb_test table which inserts then again this dummy Id=0 row if the one does not exist.
The trigger will be activated during regular update.
Update is only for rows that have Id greater then zero


Srinivas Malyala said...

Very informative ...

Anonymous said...

But what I'de like to know is how one can force OBIEE to use the <insert> </insert> element instead of the update element.

Anonymous said...

Do you know if we can use a date in the where clause of the update statement?


Notice c3 is a date ...

Record: '3422010-01-31 00:00:00'

Anonymous_An said...

very much helpful.
Thanks a lot.