Oracle BI EE 10.1.3.3/2 - Write Back Option - Budgeting/Planning
Posted by Venkatakrishnan J on September 20, 2007
OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.
1. The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.
2. The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.
3. Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.
4. Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.
5. Once this is done, go to the column properties of sales_quota and enable writeback.
6. After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is
The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.
7. Go to the table properties of the report and click on the write back option. Enter in the template details.
8. Once this is done, enter in some data and test out the write back.
More details can be obtained from the BI Server and Presentation Services guides.
October 18, 2007 at 12:12 pm
[...] Posts Customizing OBI EE - SOAP APIOracle BI EE 10.1.3.3/2 - Write Back Option - Budgeting/PlanningOracle BI EE 10.1.3.3 - Configuring Delivers - iBotsOracle BI EE 10.1.3.3/2 - Understanding Dates in [...]
October 29, 2007 at 1:59 pm
Hi Venkat,
Your article is very useful and I would like to know some more details about write back functionality (ex: limitations, authentications, etc…). I’ll appreciate if you can post more about write back.
Thanks in advance,
Nancy
November 14, 2007 at 4:28 pm
Hello,
Thanks for a good example on write-back function! It was easy to follow the steps. If you want to make the article better you could add some typical write-back errors and their solutions, you can find some in ittoolbox, for instance.
Thanks and good luck with your blog!
January 28, 2008 at 6:46 am
[...] Posts Oracle BI EE 10.1.3.3 - Configuring Delivers - iBotsOracle BI EE 10.1.3.3/2 - Write Back Option - Budgeting/PlanningCustomizing OBI EE – GO URL ParametersOracle BI EE 10.1.3.3/2 - Dynamic Column Headers using [...]
January 29, 2008 at 9:09 pm
I don’t think the “Write Back” tab of the connection pool is involved when doing writing back of the type shown in this example. You can skip that step. Probably the most error prone step is creating the write back template. Be sure to surround column references that refer to character data types in single quotes. The way to handle dates varies by database. If you’re writing back via ODBC (e.g. to Excel), be sure that the ODBC DSN does not have its “Read Only” property checked.
February 5, 2008 at 5:07 pm
I am doing a writeback to MS access and it works fine for the Administrator, but not for my groups. I believe there are some setups in the repository in the group permissions area. Have you tried a non-oracle source?
February 15, 2008 at 2:14 pm
Hi Venkat,
We are not able to see/apply the OBI Dashboard “Read” permission.
We are our using OBIEE 10.1.3.3
We are getting following permission only
No Access->Travese folder->change/delete->Full Control
Can u suggest how to get “Read” permission
Thanks
Murali
February 25, 2008 at 9:27 pm
Hi Venkat,
Would it be possible to have some kind of data Validations in place when using Write Back. What i am trying to achieve is that on ‘Submitting’ the request it should check for my Business Logic / conditions, example : if the record is not there then it should Insert the record,also check for Duplicate records etc. Since write back supports Inserts and Updates, I was thinking of writing a Function to Validate data. But i am not sure how to link it up with the Write back Template/ Report request.
Have you come across this issue before. Would appreciate your input on this.
Thanks
February 27, 2008 at 3:13 pm
Hi Venkat,
I did everything as above. but update button not enabled at the last step.
can u suggest me what might be the mistake i have done.
Regards,
Jyoti
February 27, 2008 at 3:23 pm
Hi Venkat,
I am getting this error.
please help me.
The system is unable to read the Write Back Template ‘Test’. Please contact your system administrator.
Regards,
Joe
February 28, 2008 at 9:49 pm
Hi Venka,
This article is excellent. My write back finally works because of your article.
Regards,
Vincent
March 6, 2008 at 7:01 am
Hi Venkat,
I want to ask you about using datetime in write back. I cant write back the field if there is a datetime column along with them. I guess it is because of the incompatible datetime format. The error message says that the value is in yyyy-mm-dd hh:mm:ss format. While in contrary, this datetime format didnt work when i tried in directly to the database. But when i tried with the dd-MMM-yyy format directly to the database, it worked. How to force the value in presentation so when it do the write back, the value is read as dd-MMM-yyyy instead of yyyy-mm-dd hh:mm:ss? Or is there any other solution?
Thanks.
Will
Anyway, your blogs is really useful
March 6, 2008 at 8:20 am
Anyway, I use TO_DATE(’@{c0}’,”
in xml and it works! 
March 6, 2008 at 8:21 am
Anyway, I use TO_DATE(’@{c0}’, date format) , for example: TO_DATE(’@{c0}’, ‘yyyy-mm-dd’
, in xml and it works! 
April 9, 2008 at 3:26 am
What’s the problem of my case?
I have finish all steps above,
I can update my data,but I can’t insert any data,why?
how to insert data? any other steps must be done when insert data?
my template like this:
——————————————-
INSERT INTO FACT_KPI_PROGRESS
VALUES(’@{c0}’,'@{c1}’,@{c2})
UPDATE FACT_KPI_PROGRESS SET STATUS=@{c2}
WHERE INDICATOR_CODE=’@{c0}’ AND STEP_CODE=’@{c1}’
——————————————-
April 16, 2008 at 5:01 pm
Excellent article.
But, is it possible to do write-back directly to Essbase BSO?
Should I use same SQL-instructions (insert/update)?
May 7, 2008 at 3:27 am
I believe I’ve completed all of the steps properly, but my writeback button is still greyed out. The cells are open for editing though.
What should I look for to determine why the Writeback button is not live?
May 14, 2008 at 3:18 pm
Hello,
Thnaks for the article , it was very helpful. i have a question, I’f i rearrange the columns of the report, I necessary have to modify the Template , there is a way to do that without modifying it?
Thanks
Camilo