I was working on the Write back function in OBIEE and found it quite interesting.
Introduction
Imagine if we can enter in data in the report for certain columns and
that becomes a part of the reports generated. Yes this is the basic
example of Write Back. Data entered in the report will be added into the
database. Basically we can follow two types of functions i,e INSERT and
UPDATE.
For doing so just follow the basic steps mentioned below.
1. UPDATE ONLY
1. First I created a database with 3 columns
Our aim is to enter values for the column 'EXPERIENCE' from the report so that it gets reflected in the database.
2. I created a ODBC connectioned and imported it in the RPD
3.Once this is done remember to untick the cacheable option
( You see a Write Back tab in the Connection Pool but since our database is Oracle you dont have to do anthing in this).
5. Create a Fact Dimension Model in ther BMM Layer and then save the changes
6.In the Answers first provide privilege to the Group for Write Back(Administration-->Manage Privilege-->Write Back)
7. Choose the column format for the column you want to add data and
choose the option Write Back. Inthis case it is the column "EXPERIENCE"
8. Then choose the third button above and fill in the information. Save this report and follow exactly the next step below
9. Now the next step is to write the INSERT and UPDATE statements in an
XML which is to be placed in the path location OracleBI\web\msgdb\customMessages . You can give any name for this XML. The
only thing you have to make sure is that the 'WebMessageName' in the XML
should be the same that is given in the answers with this one. The XML
pattern is like the one you see below
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable lang="en-us" system="WriteBackTemplates" table="Templates">
<WebMessage name= "Update">
<XML>
<writeBack connectionPool="xyz">
<insert>INSERT NULL FROM DUAL</insert>
<update>UPDATE WRITESAMPLE SET "EXPERIENCE"='@{c2}' WHERE "EMPID"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
Here the things to be noted is that
- you have to give the WebMessageName the same as that you have given in the Answer
<WebMessage name= "Update"> 
- Give the the Connection Pool name as that of the one set in the RPD
- Give a insert statement even though your not inserting any record otherwise it will throw an error
11. Once this is done make sure that the Presentation Services are
started. Then open the report you have saved and update a data you have
entered and click update.
12. Go to the database to see if the change is reflected.
Walla... Write back works!!!
2. UPDATE AND INSERT TOGETHER
Doing the INSERT is a bit tricky. As stated on Siebel SupportWeb,
in order for an INSERT to occur, a value has to change in a NULL
field that is not part of the UPDATE commands WHERE clause.
What this basically means is, if you have a Writeback report with
fields A, B and C. If fields B and C are part of the where
clause of the UPDATE command and A is not, then any updates to A
will cause an INSERT command. The only catch is that A has to be
a NULL field that you edit.
in order for an INSERT to occur, a value has to change in a NULL
field that is not part of the UPDATE commands WHERE clause.
What this basically means is, if you have a Writeback report with
fields A, B and C. If fields B and C are part of the where
clause of the UPDATE command and A is not, then any updates to A
will cause an INSERT command. The only catch is that A has to be
a NULL field that you edit.
So I have taken a condition here that I have a column an ID column that is auto generated and I want to add information as a part of insert and
In case of UPDATE I want to edit an already existing data/ column
1. Well I have the very same 3 columns
2. Now in the XML I make a small change in the INSERT statement. I tried to do a direct insert statement and didnt work for me. It throws a lot of error and then went with a logic that I want to update a rough with EMPID=3 so in this case I can have a insert statement also. The XML is below. -
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable lang="en-us" system="WriteBackTemplates" table="Templates">
<WebMessage name= "babs">
<XML>
<writeBack connectionPool="xyz">
<insert>UPDATE WRITESAMPLE SET "EMPLOYEE_NAME"='@{c1}',"EXPERIENCE"='@{c2}' WHERE "EMPID"=@{c0}</insert>
<update>UPDATE WRITESAMPLE SET "EXPERIENCE"='@{c2}' WHERE "EMPID"=@{c0}</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
3. Now make the other column WRITE BACK by changing the column properties
4. Now insert values in the second and third column
