Write Back In OBIEE

Write Back:

   This option is useful to us whenever changes should be done in the Report level that can effect even in the database level also. For that we need a certian permission in the RPD level .

The first step is to enable write-back on the table columns you require to be updated, in the table design view open the column properties, Column Format tab, and from the ‘Value Interaction’ drop down list select ‘Write-Back’. 

Once you have set the write-back property on the columns, click the write-back icon in the top left corner of the design pane, this is where you set the path to the template that provides the SQL to update the table, and edit the text for the command button to action the write-back. 

Save your table with the amended settings and preview the results, the fields with write-back enabled now have a text box type appearance. Now you need to create the xml template to be used, once created this resides in OracleBI\web\msgdb\customMessages; the templates appear to be the main cause of problems experienced with write-back. This is the template I have used with this table: 

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

<XML>

<writeBack connectionPool="OBI Security">

<insert>

</insert>

<update>

UPDATE tbl_UserAssignments SET UserID='@{c1}', VariableID='@{c2}', Value='@{c3}' WHERE ID='@{c4}'</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

The final part of this journey into write-back is to put this to some use, in this case, an admin page for maintaining values assigned to variables held in a relational db table. This brings us to what appears to be a problem point, inserting rather than updating a record. To ease this we decided to seed a row with ‘Add record’ type messages and write a trigger to update the table, on update, with these messages.