Write Back in OBIEE 11g:
Introduction:
As we all
know we have a Writeback property in OBIEE with Insert and Update. But many of
us don’t know that we can even implement delete Writeback which we made in our
project as per our client’s requirement.
Here I am
going to provide everyone a complete document on OBIEE Writeback which includes
Insert, Update and Delete WRITEBACK.
Requirement:
We have a
complete new table created for Writeback (No data) and in this we have to
implement inserting new records, updating them and deleting the record entered.
Data to be entered a fresh.
Changes
in Instance Config File:
Add the following tag in
instance config file.
<WebConfig>
<ServerInstance>
<LightWriteback>true</LightWriteback>
</ServerInstance>
RPD
Changes for Writeback:
Before
implementing Writeback following are the RPD changes.
1.
Import the created table in to RPD and make necessary
joins in physical layer.
2.
Make necessary changes in the RPD business layer as
per the design.
3.
In BMM Layer which ever column to be made editable
please check writable option as shown
below.
Fig 1:
Enabling Writeable option in BMM Layer
4.
Make the above changes for necessary columns and drag
that to presentation layer and save it.
5.
Once above changes are done create the report in UI
and do following change.
a.
Whichever columns required for editable enable
Writeback option in the report as shown below and set the text field and save
the report.
Fig 2:
Enabling Writeable option in Report
Insert
Writeback: For inserting records,
1.
Make sure that table should have a null record for
inserting the data.
2.
Create an xml file as shown below and save with the
same web message name used in this xml marked with yellow color. Also add
insert syntax for which u r going to insert data in the insert tag.
<?xml version="1.0"
encoding="utf-8" ?>
<WebMessageTables
xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack"
table="Messages">
<WebMessage
name="DSR_Twist_writeback_insert">
<XML>
<writeBack connectionPool="BI Writeback Connection
Pool">
<update>
insert into
BI_WRITEBACK(WRITEBACK_KEY,SUBJECT_AREA,DASHBOARD_NAME,PAGE_NAME,REPORT_NAME,COMMENT_TEXT,SORT_ORDER,REPORT_DATE,REPORT_REGION)
values
(to_char(seq_writeback.nextval),'DSR Reports','DSR - TWIST','Global
Sales','Market Overview','@{ce0535f1ee8fd6988}','@{c1771a2407a39a37a}','@{cfe255305c4bb1c5b}','@{c8eec6ef257ca3d52}')
</update>
<insert>
insert into
BI_WRITEBACK(WRITEBACK_KEY,SUBJECT_AREA,DASHBOARD_NAME,PAGE_NAME,REPORT_NAME,COMMENT_TEXT,SORT_ORDER,REPORT_DATE,REPORT_REGION)
values
(to_char(seq_writeback.nextval),'DSR Reports','DSR - TWIST','Global
Sales','Market
Overview','@{ce0535f1ee8fd6988}','@{c1771a2407a39a37a}','@{cfe255305c4bb1c5b}','@{c8eec6ef257ca3d52}')
</insert>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
The above xml file to be placed in the following path
C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages
Create the customMessages folder in the above as it
won’t be available here with same name shown above with all case sensitive’s.
Fig 3: XML
File for insert and its location
Once
the above step is done go to table properties and enable Writeback.
i.
Give the template name here whichever u give in xml
file.
ii.
Disable Toggle Table Mode which will be enabled by
default and this is because if we don’t disable it editing of column will not
happen.
iii.
Also give our necessary naming conventions in the
apply button which ever required.
iv.
Standards Insert
for inserting and Update for
updating remaining defaults as shown below and click ok.
Fig 4: Passing
xml file web message name in the table properties for insert
Update
Writeback: For
Updating records,
Create an
xml file as shown below and save with the same web message name used in this
xml marked with yellow color. Also add update syntax for which u r going to
update data in the update tag.
<?xml version="1.0"
encoding="utf-8" ?>
<WebMessageTables
xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable
lang="en-us" system="WriteBack"
table="Messages">
<WebMessage
name="DSR_Twist_writeback_update">
<XML>
<writeBack connectionPool="BI Writeback Connection
Pool">
<update>
update
BI_WRITEBACK set
SUBJECT_AREA='DSR
Reports',DASHBOARD_NAME='DSR - TWIST',PAGE_NAME='Global
Sales',
REPORT_NAME='Market
Overview',COMMENT_TEXT='@{ce0535f1ee8fd6988}',SORT_ORDER=@{c1771a2407a39a37a},REPORT_
REGION='@{c8eec6ef257ca3d52}'
where
WRITEBACK_KEY='@{c33a77793cd508cf9}' </update>
<insert>
</insert>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables
The above xml file to be placed in the following path
C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages
Create the customMessages folder in the
above as it won’t be available here with same name shown above with all case
sensitives.
Fig 5: XML
File for update and its location
Once
the above step is done go to table properties and enable Writeback.
i.
Give the template name here whichever u give in xml
file.
ii.
Disable Toggle Table Mode which will be enabled by
default and this is because if we don’t disable it editing of column will not
happen.
iii.
Also give our necessary naming conventions in the
apply button which ever required.
Standards Insert for inserting and Update for updating remaining defaults
as shown below and click ok.
Fig 6: Passing
xml file web message name in the table properties for update
Once all
the above changes are done restart the services for getting all the changed
affected.
Finally
Insert and Update looks like the following screenshot once u make insert or
update we get a mess updates are
successful.
Fig 7: Final
layout of reportafter inserting and updating records
Delete
Writeback: For
Deleting records,
Delete
Writeback is not possible directly as we did for Insert and Update. There is no
provision in the xml file for delete as we have tags for insert and update.
We
generally create 2 reports for deleting records.
Steps
for deleting the records which are Inserted and Updated:
1.
Create a main report with the corresponding columns as
shown below. For better delete operation use at least a primary key column of
that table i.e., a unique key column. In the below screen shot we have a unique
column called Writeback Key and this is a varchar in our case.
Fig 8: Main
report created for delete writeback
2.
Create a function in the backend as shown below.
create or replace function writeback_delete_geo(r_key in
varchar2,to_delete in varchar2)
return varchar2
is pragma autonomous_transaction;
v_geo varchar2(100);
begin
if to_delete = 'yes' then
delete bi_writeback where writeback_key = r_key;
commit;
return 'deleted';
else
return 'no delete';
end if;
exception
when others then return 'failure'; end;
In
the above function delete operation is handled on a unique column where there
are no repetitions of data else if repetitions are there, more than a record is
deleted which is not correct. So always make it on a
unique identifier.
In
our case I made this key varchar because I passed character values like
failure, delete and no delete in the function as return values and this will
holds good only when the unique identifier can be a char.
3.
Create another report which will be drill down from main report created
previously and make the unique identifier is prompted in the drill report.
In the child report drag 2 columns
one unique identifier column and other dummy column as shown below.
Fig 9: Sub
report creted for delete writeback
In the
other column called delete writeback we need add the following formula when u
create the report i.e.,
EVALUATE('WRITEBACK_DELETE_GEO(%1,''No'')',"Writeback"."Writeback
Key")
Here for
evaluate function pass the function that we created in the backend and pass
parameter No on the key that we have created the function with delete
statement.
Once we
create this report and display the results report looks like
Fig 10: After creating Subreport in table view
4.
Now add narrative and type The row is @2 successfully.
The layout looks like
Fig
11: Adding Narrative view
5.
Now put only narrative by deleting table view from the
above screen shot which looks like
Fig 12: Only Narrative View
6.
Once done go to criteria and change the delete
writeback column to the following formula which is
EVALUATE('WRITEBACK_DELETE_GEO(%1,''Yes'')',"Writeback"."Writeback
Key")
Once this is done don’t run the report just save the
report if we run the report then whole data in that table is lost. We need to
re insert the data in the table. Make sure we don’t delete data while creating
the intermediate report itself.
7.
Once these changes are done navigate the intermediate
report from the main report on the unique column and save it.
8.
Once we click on the main report the result is as
below.
Fig 13:Main
report after enabling drill on writeback key
Click on Writeback Key where link is enabled the
result is as
Fig 14: Message
after deleting a row
After deleting the report looks like
Record is being deleted.
In this way delete Writeback is
implemented.
Note:
1. If
this Writeback key is not unique and repeating then all those records will be deleted.
2. There
are some bugs like while inserting and updating records some time it throws error but when u edit report and
insert or update and save then it never throws error in either in answers or
dashboard but this is not always only some times.
Final Layout looks like
Fig 16: Dashboard
Layout with Insert,Update and Delete Strategies
Another way of using
delete writeback
Delete
Writeback: For inserting records,
1.
Make sure that table should have a null record for
inserting the data.
2.
Create an xml file as shown below and save with the
same web message name used in this xml marked with yellow color. Also add
insert syntax for which u r going to insert data in the insert tag.
<?xml version="1.0"
encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack"
table="Messages">
<WebMessage
name="DSR_Twist_writeback_insert">
<XML>
<writeBack connectionPool="BI Writeback Connection
Pool">
<update>
delete bi_writeback where writeback_key = '@{ce0535f1ee8fd6988}';
</update>
<insert>
delete bi_writeback where writeback_key = '@{ce0535f1ee8fd6988}';
</insert>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
The above xml file to be placed in the following path
C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages
Create the customMessages folder in the above as it
won’t be available here with same name shown above with all case sensitive’s.
Once
the above step is done go to table properties and enable Writeback.
v.
Give the template name here whichever u give in xml
file.
vi.
Disable Toggle Table Mode which will be enabled by
default and this is because if we don’t disable it editing of column will not
happen.
vii.
Also give our necessary naming conventions in the
apply button which ever required.
viii.
Standards Insert
for inserting ,Update for
updating remaining defaults and Delete for
deleting records as shown below and click ok.
Thanks
