Write Back in OBIEE11g

Also refer-http://gerardnico.com/wiki/dat/obiee/write_back
To configure for write back:

1.     Create a physical table in the database that has a column for each write-back field needed. In the table create statement, make the write-back fields non-null-able.

Note:

For optimum security, store write-back database tables in a unique database instance.

2.     Use the Oracle BI Administration Tool to configure the new table, as described inOracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

3.     Create a write-back template that specifies the SQL statements that are necessary to both insert and update values into the table that you created. For more information, see Section 19.8.3, "About the Write-Back Template."

4.     Add the LightWriteback element in the instanceconfig.xml file, as described inSection 19.8.2.1, "Setting the LightWriteback Element."

5.     In Oracle BI Presentation Services, grant the following write-back privileges to the appropriate users: Manage Write Back and Write Back to Database.

For information, see Section C.2.3.2, "Setting Privileges in Oracle BI Presentation Services Administration."

6.     Configure a write-back analysis, as described in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

19.8.2.1 Setting the LightWriteback Element

In order for users to write back values, you must manually add the LightWriteback element in the instanceconfig.xml file. Before you begin this procedure, ensure that you are familiar with the information in Section 3.4, "Using a Text Editor to Update Oracle Business Intelligence Configuration Settings".
To manually set the element for write back:

1.     Open the instanceconfig.xml file for editing, as described in Section 3.6, "Where are Configuration Files Located?"

2.     Locate the ServerInstance section in which you must add the LightWriteback element.

3.     Include the element and its ancestor elements as appropriate, as shown in the following example.

4.         <WebConfig>

5.             <ServerInstance>

6.                 <LightWriteback>true</LightWriteback>

7.             </ServerInstance>

8.         <WebConfig>

Note that this example does not include parameters that might exist in the file, but that are centrally managed by Fusion Middleware Control and cannot be changed manually.

9.     Save your changes and close the file.

10. Restart Oracle Business Intelligence.

19.8.3 About the Write-Back Template

The write-back template is an XML-formatted file that contains SQL statements that are needed to insert and update records in the write-back table and columns that you have created. You can create multiple write-back templates, customizing each one for the fields that are used in each specific analysis. In the table view properties, you specify the name of the write-back template to use.

19.8.3.1 How Write Back Works

If a user has the Write Back to Database privilege, then the write-back fields in their analyses can display as editable fields if properly configured. If the user does not have this privilege, then the write-back fields display as normal fields. If the user types a value in an editable field and clicks the appropriate write-back button, then the application reads the write-back template to get the appropriate insert or update SQL command. It then issues the insert or update command. If the command succeeds, then it reads the record and updates the analysis. If there is an error in either reading the template or in executing the SQL command, then an error message is displayed.
The insert command runs when a record does not yet exist and the user enters new data into the table. In this case, a user has typed in a table record whose value was originally null.
The update command runs when a user modifies existing data. To display a record that does not yet exist in the physical table to which a user is writing back, you can create another similar table. Use this similar table to display placeholder records that a user can modify in dashboards.

19.8.3.2 Requirements for the Write-Back Template

The write-back template must meet the following requirements:

·         To meet security requirements, you must specify the connection pool along with the SQL commands to insert and update records. These SQL commands reference the values that are passed in the write-back schema to generate the SQL statements to modify the database table. Values can be referenced either by column position (such as @1, @3) or by column ID (such as @{c1234abc}, @{c687dfg}). Column positions start numbering with 1. The use of column ID is preferred. Each column ID is alphanumeric, randomly generated, and found in the XML definition of the analysis in the Advanced tab of the Analysis editor.

·         You must include both an <insert> and an <update> element in the template. If you do not want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. For example, you must enter the element as

·                <insert> </insert>

rather than

<insert></insert>

If you omit the blank space, then you see a write-back error message such as "The system cannot read the Write Back Template 'my_template'".

·         If a parameter's data type is not an integer or real number, then add single quotes around it. If the database does not do Commits automatically, then add the optional postUpdate node after the insert and update nodes to force the commit. The postUpdate node typically follows this example:

·                <postUpdate>COMMIT</postUpdate>

·         Store the write-back template files in the analyticsRes directory that the administrator has configured for static files and customer messages:

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obipsn/analyticsRes/customMessages

While XML message files that affect a language-specific user interface must be localized, the XML file that is used for configuring a write-back template is usually not translated, because it is language-independent.

In the rare cases where write-back template files must be language-dependent (for example, if a user logging in using the l_es (Spanish) locale would use a different SQL command then a user logging in using l_fr (French) locale), then the write-back template messages should exist in appropriate language directories. For information, see Section 22.5, "Customizing the User Interface Using XML Message Files."

·         The write-back template files can have any name of your choosing, because the system reads all XML files in the CustomMessages folder. To ensure that write back works correctly, include in the WebMessage element of the file the name of the SQL template that you specified when you created the write-back table. You can have multiple WebMessage elements in one file, with each element specifying one SQL template.

The following example shows the specification of the SQL template that is called "SetQuotaUseID."

<WebMessage name="SetQuotaUseID">

19.8.3.3 Example: Write Back Template

A write-back template might resemble this example:

<?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="SetQuotaUseID">

      <XML>

         <writeBack connectionPool="Supplier">

            <insert>INSERT INTO regiontypequota VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>

            <update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>

         </writeBack>

      </XML>

   </WebMessage>

   <WebMessage name="SetQuota">

      <XML>

         <writeBack connectionPool="Supplier">

            <insert>INSERT INTO regiontypequota VALUES(@1,@2,'@3','@4',@5)</insert>

            <update>UPDATE regiontypequota SET Dollars=@5 WHERE YR=@1 AND Quarter=@2 AND Region='@3' AND ItemType='@4'</update>

         </writeBack>

      </XML>

   </WebMessage>

</WebMessageTable>

</WebMessageTables>

file path:

writeback.xml under
C:\BI_HOME\instances\instance3\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\WEB-INF\customMessages
and it should be kept under
C:\BI_HOME\instances\instance3\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages