Business Intelligence – Oracle

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.

About these ads

65 Responses to “Oracle BI EE 10.1.3.3/2 – Write Back Option – Budgeting/Planning”

  1. [...] 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 [...]

    • Manikumar said

      Hi Venkat,

      Your WriteBack Document is helpfull and I tried this all steps are finished but when I entered the values and click the submit button it display a Erro Message.Please tell me how to solve this error.

      Thanks.

  2. Nanacy said

    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

  3. ia said

    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!

  4. [...] 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 [...]

  5. Kurt Wolff said

    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.

  6. Rob said

    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?

  7. V Murali said

    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

  8. Prash said

    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

  9. Joe said

    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

  10. Joe said

    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

    • Sine said

      Hi Joe

      I have the same error.You should recheck in the report.The WebMessage name is the actual XML template name.
      The filename of the XML file can be anything. I tried it and it works.

    • Siva said

      I have an issue in following thir url at step 6. There is folder customMessages in my directory structure and there are no XML templates are being created. Do we need to mannually create the folder and as well the XML templates ?

      Please suggest.

      Thanks
      siva

  11. Vincent said

    Hi Venka,

    This article is excellent. My write back finally works because of your article.

    Regards,

    Vincent

  12. Will R said

    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 :)

  13. Will R said

    Anyway, I use TO_DATE(‘@{c0}’,”) in xml and it works! :P

  14. Will R said

    Anyway, I use TO_DATE(’@{c0}’, date format) , for example: TO_DATE(’@{c0}’, ‘yyyy-mm-dd’) , in xml and it works! :P

  15. fireJAVA said

    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}’

    ——————————————-

  16. Ivan said

    Excellent article.
    But, is it possible to do write-back directly to Essbase BSO?
    Should I use same SQL-instructions (insert/update)?

  17. Mark said

    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?

  18. Camio said

    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

  19. farzin barazandeh said

    I think the dir to put the template should be OracleBIData\web\msgdb\customMessages.

  20. Yogesh said

    Hi Venkat
    I have tried this with tera data.I have set everythng but the answers give me the error

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27024] The EXECUTE PHYSICAL statement must specify a physical SQL statement to execute. (HY000)
    SQL Issued: EXECUTE PHYSICAL CONNECTION POOL DEV

  21. Dhavan said

    Hi,

    I have windows Vista Business Installed in my Laptop.. Even I tried to instll Windows Version of OBIEE but while installing its throwing an error..”The installer
    is unable to run in this Graphical mode.Try running the installer with the =console
    or -Silent Flag”

    Any pointer on the above mentioned query will be great help for me.

    Thanks,

  22. santhosh said

    Hii,
    i did all the steps as mentioned in this page. But when i try to click and update a value, the error message is displayed as ” the system is unable to read the write back template dev10. Please contact your system administrator”
    Any Suggestions Please???

  23. Jaimeen Shah said

    Hi,

    This document is useful when I use Write Back in my report.

    But there is one error which is coming like “Unable to Write Back. Associated Write Back SQL Template not found on Server.

  24. Jaimeen Shah said

    Hi,

    please give me the solution to the above query.

  25. Jaimeen Shah said

    The system is unable to read the Write Back Template ‘SAME’. Please contact your system administrator.
    What is the solution or how to solve this query.

  26. Jaimeen Shah said

    Hi Venkat,
    I am getting the same error that joy has getting.
    please help me.
    The system is unable to read the Write Back Template ‘Test’. Please contact your system administrator.

    Regards,

    Jaimeen

  27. Rachna D said

    Hi venkat

    I am trying to use this write back template , for my whatif analysis,in which user enters the value in a column , and the value entered in that column is used for calculation.
    i have created a table in datawarehouse with column having no data, to be used as writeback column.
    I have tried all the steps above but its not working.
    can you please help me , suggest me what to do

    Thanks
    Rachna

  28. Sarat said

    Hi Venkat,

    I did everything as mentioned by you. Though the write back columns are editable, but am not able to insert the same in to my DB. System displays “Unable to Write Back. Associated Write Back SQL Template not found on Server.

    Pls advise as how to get rid of this error. Any addl steps required?

    Thanks in advance

    Sarat

  29. Sarat said

    HI VENKAT,

    AM ABLE TO FIX THE ABOVE PROBLEM. BUT AM GETTING THIS ERROR NOW.

    The system was unable to generate appropriate SQL. Please contact your system administrator.
    Error Details
    Template: ‘ insert into “SALES” (PROD_ID, CUST_ID, PROMO_ID, TIME_ID, CHANNEL_ID, SALES_QUOTAS) values (@{c0},@{c1},@{c2},@{c3},@{c4},@{c5}) ‘

    Record: ’1323501998-08-05 00:00:003444′

  30. Dhavan said

    Hi Venkat,

    I am not able to find out any template on mentioned directory
    i.e C:\OracleBI\web\msgdb\customMessages
    Does it will be created automaticaly once we enable the WriteBack feature or we have to manualy create and place it
    in the same path,
    Your articles are really usefull

    Regards,
    Dhavan

  31. Sainath said

    Hi Venkat,

    I enable write back, I used Venkats blog post:

    i am eable to update my database but i am getting the below error message

    Error:

    An error occurred while writing to the server. Please check to make sure you have entered appropriate values. If the problem persists, contact your system administrator.
    Odbc driver returned an error (SQLExecDirectW).
    Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27024] The EXECUTE PHYSICAL statement must specify a physical SQL statement to execute. (HY000)
    SQL Issued: EXECUTE PHYSICAL CONNECTION POOL Sales COMMIT

    please suggest..

    Regards
    Sainath

    • nag said

      please follow the link that will correct you, where you are doing mistake…
      1. Login in OBI Administration
      2. Go to the menu: Manage  Securitycorresponding user(ex: nag) and double click on it.
      3. Set the permissionsclick on Query Limitsand allow “Execute Direct Database Access” to the permitted group of user(ex: nag)
      4. and click ok

  32. Amrita Pal said

    Hi Jaimeen Shah,

    For Error:Unable to read the writeback template ” “.Please contact your system administrator.
    Solution:
    Cross check the xml file tags (case sensitive):
    connectionPool
    writeBack

    Template name in answers,message name in xml and xml file name should be same.
    Restart the server whenever you change in XML file.

    Regards,
    Amrita

    • Hi Amrita,

      Thanks a lot for your suggestion regarding xml file gags (connectionPool) and (writeBack).

      I spend more then a day because of this and it got fixed after looking at your post.

      Regards,
      Amit Kurchania

    • Hi Amrita,

      Thanks a lot for your suggestion regarding xml file tags (connectionPool) and (writeBack).

      I spend more then a day because of this and it got fixed after looking at your post.

      Regards,
      Amit Kurchania

  33. Ramesh Pabba said

    Hi venkat,

    Do we have any Automation process for RPD deployment.

    thanks
    Ramesh P

  34. Robert said

    My report only can update. This is my xml:

    INSERT INTO H1 VALUES (@{c0}, @{c1})
    UPDATE H1 SET V1 = @{c1} WHERE id = @{c0}

    Any Suggestions?

    Regards,
    Robert.

  35. Ken Guzzetta said

    Hi Venkat,

    Great article. We have write back working fine, but wanted to know if you have ever done anything with different types of input mechanisms. The only way I see is just a plain text box where the user manually types in a value. Do you know if there is a way to have a drop down with pre-seeded values that the user could select and have that value passed to the write back column? We thought it might be great in certain circumstances to have just a few values in a drop down that the user could choose from and have those stored. We can created the drop down with values, but it won’t pass it to the db. Are there any approaches to this that you or anyone else have come across?

    • Govind said

      Hi Ken,
      Did you by any chance figure out a way to do this.I’m have a simillar kind of requirement.

      Thanks
      Govind

    • Ankit said

      Can you please let me know if you have any solution for drop down value to pass the Write back…

      Thanks
      Ankit

  36. Vijaya Vaishu said

    Hi Venkat,

    Can you please help me how can I remove the default Print(HTML format) from the dashboard page. Also I would like to delete the same for the individual print options in the dashboard.

    Thanks in advance!
    VJ Vaishu

  37. Marcin said

    Hallo Venkat,

    I’ve implemented WriteBack funkctionality according to above steps. I need to be able to put number-values. However, it should also be possible to put empty values (null). When I try to do that I got “Invalid input value:” error. Is it some kind of validation? On which level validation take place? Field that I want to update is checked as nullable in physical layer… How can I pass it? I appreciate your suggestions.

    Marcin

  38. [...] Write Back Option – Budgeting/Planning page_revision: 9, last_edited: 1243634889|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append who watches backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an "edit" link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = "UA-68540-5"; _udn="wikidot.com"; urchinTracker(); _qoptions={ qacct:"p-edL3gsnUjJzw-" }; [...]

  39. This is absolutely awesome.
    Can you add one thing about xml that tags are case sensitive.

    Correct is = connnectionPool & writeBack

    Most of the people encounter administrator error because of this.

    Regards,
    Amit Kurchania

  40. Gary said

    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?

  41. Alex said

    Hi everyone:-)

    I have installed EPM 11 and OBIEE…currently newest version. I manage to pull my cube into Answers through Admin server, but when trying any quiery I get the follow error:

    Odbc driver returned an error (SQLExecDirectW).
    Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. Essbase Error (HY000)
    SQL Issued: SELECT Caffeinated.”Gen1,Caffeinated” saw_0 FROM “Basic#1″ ORDER BY saw_0

    Any help is appreciated.

    Regards,
    Alex

  42. prabusankar said

    hi,
    i did above steps but not able to get the result.the error was Associated Write Back SQL Template not found on Server.
    please to help me

  43. SaiNath said

    Hi Venkat,

    iam able to do all the steps but i that update button is not enable in my case.

    i checked in my connectional pool ‘Writeback” is disable. can u plz suggest that why it happend.

    why “Update” field is disable.

    rest of all things i done..

    find the query which i used–

    <WebMessage name="SHNEW"

    INSERT INTO Product (Brand,Color, Finish, LevelX, ProdDesc, Seq,SizeX, TYpe)
    Values (@{c0},@{c1},@{c3},@{c4},@{c5},@{c6},@{c7},@{c8})

    please suggest on this.

    Thanks,
    Sainath

  44. SaiNath said

    <WebMessage name="SHNEW"

    INSERT INTO Product (Brand,Color, Finish, LevelX, ProdDesc, Seq,SizeX, TYpe)
    Values (@{c0},@{c1},@{c3},@{c4},@{c5},@{c6},@{c7},@{c8})

  45. Anwar Ali said

    Hi Venkat,

    Thanks for the detailed description on how to apply the writeback feature.

    Would be please help me on if I Can update more than one table with multiple update statements on single update tag.

    Thanks & Regards
    Anwar Ali

  46. Anand said

    Hi Venkat
    I am working on OBIEE/HRMS Domain, APPS-OLTP as Source. I dont know how to Develop BMM Layer when there is snowflake schema.
    I have merged some tables and made it as a star. Does it effect the Performance?
    Or do we have any alternative to develop Hierarchies and work on direct snowflake schema?

    Please Guide me
    ThanQ

  47. vinod said

    i did same as u told but intsert button is in disable mode and u had write the tigger right where should i execute the tigger

  48. Bill said

    Since the fact table is indexed by the dimension keys, do the dimension keys need to be added to the presentation layer and on the answer request in order for the update on the fact table to work? Also Is there a way to flush which “WebMessage name” is being used by the report since the report keeps erroring and specifying a merge statement that isn’t in my xml file in the customMessages file?

  49. Satish said

    HI Venkat

    Thanks for the description on how to apply the writeback feature how ever i am getting the “No Results
    The specified criteria didn’t result in any data. ”

    I followed all the steps and manually created the xml file and saved it in the Custommessages.. folder as u mentioned and in the table properties after i have added the Template name and clicked on OK nothing happens and this error occurs.

    Please let me know what i am missing .

    Here is the XML i have created

    Insert into Comments (Input) values (@{c0})

  50. Devarasu said

    Dear All,
    I have followed all your writeBack Steps,the Inser/update button is enabled now then i tried to update button that time i am getting Writeback Error :The system is unable to read the Write Back Template ‘shnew’. Please contact your system administrator.

    I did given steps only :
    1) connection pool writeback properties section default settings ididn’t change anything.

    2) I have set the report_comments table not cacheable

    3) writeback privilege i have set Administrator

    4.)File : shnew.xml

    INSERT INTO report_comments (USERNAME,COMMENTS) values(‘@{c0}’,’@{c1}’)
    UPDATE report_comments SET comments =’@{c1}’ WHERE uid=@{c0}

    5) The XML Templates i have put it c:/ORACLEBI/web/customMessages/ folder

    6) i have restarted my server.then finally i have tried that time i am getting given error like :
    writeBack error :
    The system is unable to read the Write Back Template ‘shnew’. Please contact your system administrator.

    please suggest on this.

    Thanks,
    Devarasu
    7stl

  51. Devarasu said

    Dear All,
    I have followed all your writeBack Steps,the Insert button is enabled now then i tried to update button that time i am getting Writeback Error :The system is unable to read the Write Back Template ‘shnew’. Please contact your system administrator.

    I did given steps only :

    1) connection pool writeback properties section default settings ididn’t change anything.

    2) I have set the report_comments table not cacheable

    3) writeback privilege i have set Administrator

    4.)File : shnew.xml

    INSERT INTO report_comments (USERNAME,COMMENTS) values(‘@{c0}’,’@{c1}’)

    5) The XML Templates i have put it c:/ORACLEBI/web/customMessages/ folder

    6) i have restarted my server.then finally i have tried that time i am getting given error like :
    writeBack error :
    The system is unable to read the Write Back Template ‘shnew’. Please contact your system administrator.

    please check and suggest on this.

    Thanks,
    Devarasu
    7stl

  52. Ian said

    Used a stored procedure — all good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: