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
INSERT NULL FROM DUAL
UPDATE WRITESAMPLE SET “EXPERIENCE”=’@{c2}’ WHERE “EMPID”=@{c0}
Here the things to be noted is that
– you have to give the WebMessageName the same as that you have given in the Answer
– 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. –
UPDATE WRITESAMPLE SET “EMPLOYEE_NAME”=’@{c1}’,”EXPERIENCE”=’@{c2}’ WHERE “EMPID”=@{c0}
UPDATE WRITESAMPLE SET “EXPERIENCE”=’@{c2}’ WHERE “EMPID”=@{c0}
3. Now make the other column WRITE BACK by changing the column properties
4. Now insert values in the second and third column
Its All About Data - By Jethin Abraham 
Hi I am new to OBIEE. Thank you so much for your effort to explain so clearly. I understand everything else what I don't understand is how to write the template. Many articles on the web says we should save the template in D:\OracleBI\web\msgdb\customMessages
Could you please explain how to create the template for very first time?
Thank You
LikeLike
Thanks a lot.. Im happy to know that my efforts are helping a lot of people. Well if you have to create a template for the first time, you just have to open your notepad and copy the XML format mentioned and save it in the path mentioned by you. You can provide any name for this template, it doesn't matter. I have this step captured in the 9th point above. If you still have any queries feel free to get back. If there is any other areas where you require any information please feel free to inform me.
Thanks once again. 🙂
LikeLike
This comment has been removed by the author.
LikeLike
Thank you so much. I tried it but the update option is not on when you put the cursor on UPDATE its shows the following error: Unable to write back: Associated write back SQL template not found on server.
Should I edit this section too because the information in this section is different in other sites?
LikeLike
I mean this section system=” ” and table=” “
LikeLike
It seems that the server is not able to recognize the template you have created. There are a few things that can be the issue.
1. Copy the exact same XML which I have above in my blog and change the connection pool information and the table information to meet with your criteria
2. Make sure that you mentioned in the XML is the exact same as that you provide it in the Template Name section in the answers
3. Make sure you provide Write Back to the same columns as mentioned in the SQL provided in the XML
4. After saving the XML it is best advised to restart all the services so that the new XML created is also reflected in the Presentation Services.
If these checks are done Im sure you will be able to do write back with no issues. Please get back if you have any more queries.
LikeLike
In the second point make sure that the Web Message name is the same as that in the Answers because it is through this that the Server knows that it is referring to this XML.
LikeLike