OBIEE 10G/11G - Writeback Capabilities (Table of Pivot)

Security, Privileges

First, you must grant your account with the privilege “Write Back to database” :

1. Login in OBI Presentation Service Website
2. Follow this links : Setting / Administration / Manage Privileges
3. Grant the privilege “Write Back to database” to the permitted group

Second, you must grant the direct access database to the user through the Administration Tool in the security part.

1. Login in OBI Administration
2. Go to the menu : Manage / Security
3. Grant the privilege “Execute Direct Database Access” to the permitted group of user

Write Back Template to define the DML statement

The write back template is an custom messages (XML-formatted) file that contains SQL commands needed to insert and update records in the write back table and columns you have configured. It must be store in the directory:

• 10g: "OracleBI\web\msgdb\customMessages".
• 11g: The path of the CustomerResourcePhysicalPath node in the instanceconfig.xml + /customMessages. This is the directory for static files and customer messages. Example: ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obipsn/analyticsRes/customMessages

<?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="SetWriteBackValue">
         <XML>
            <writeBack connectionPool="Connection Pool">
                <insert>INSERT INTO OTHER_SCHEMA.WRITE_BACK VALUES (@{c0})</insert>
                <update>UPDATE OTHER_SCHEMA.WRITE_BACK SET VALUE='@{c0}' WHERE PK=@{c2}</update>
	    </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>

The line <WebMessage name="SetWriteBackValue"> contain the value of the template name. This value must match with the value filled in the table properties.

The line <writeBack connectionPool="Connection Pool"> contain the value of the connection pool. You must have the same name in the repository.

The line <update>UPDATE WRITE_BACK SET VALUE='@{c0}' WHERE PK=@{c2}</update> contain the SQL statement to update the database.

Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}). To find the column ID, see the header columns labels in the table

When the update and insert statement run

• The insert command runs when the update value is null.
• Update command runs when a user modifies existing data.

How to update

You are now able to launch “Write Back” capabilities !

1. Change the value of the column that you have configured (Here above the VALUE column)
2. Click on the button in the bottom-right corner of the table (Here above the WRITE BACK VALIDATION button)

Reload the change online

The files are loaded in memory. You can reload them via Admin > Reload Files and Metadata link.

10G: the Update Button is grey

The write back button is grey when Oracle BI Presentation server hasn't found the XML template message.

Two reasons for this :

• The name of the template name in the report is different than in the XML message.

Example with the name : SetWriteBackValue

must be the same in the node WebMessage of the xml message :

<WebMessage name="SetWriteBackValue">

• Or may be you simply forgot to restart the OBI Presentation Service. (The messages are loaded during the initialization)