WriteBack in Pivot Table - OBIEE 12c / OBIEE 11g

Is it really not possible to have WriteBack functionality in OBIEE Pivot table? Oracle says a strict, "NO".

So let me tell you how we can get over this biggest limitations. Do you know what's the difference between Table View and Pivot View? At least in terms of OBIEE whereas everything it deals with is XML.!! If you ever see what XML it writes in Advanced Tab for both these views, you will get to know that there's no difference as such. So, I tried to do some analysis, and come up with the below solution. Please let me know whether this works for you to or not.

If you are at OBIEE 12c version, you no longer need to add <LightWriteBack>true</LightWriteBack> tag to instanceconfig file. There is a straight forword and simple way to implement WriteBack functionality.

Some basics of WriteBack feature:

Users of a dashboard page or an analysis have the ability to modify the data that they see in a table view. This ability is often referred to as "write back." To enable this functionality, below are some steps that we need to follow, (These are for Table view by the way..)

Setting up WriteBack in the repository

(Note: It is advisable that you do all the rpd changes in Online mode to get list of all the Users / Applciaion Roles in LDAP)

Physical Layer

• In Physical layer, Go to Physical Table that has Writable Field.
• In General table, make sure tab ‘Cacheable’ is un-checked.

BMM Layer

• In BMM layer, Go to Logical Table.
• Double click on the Writable column, check the flag for Writable

Presentation Layer

• In Presentation layer, Go to Presentation Table.
• Double click on the Writable column > Click on Permission > Assign Read/Write Access to desired Application Roles. (In general, it would be BIContentAuthor)

Direct Database Request

• Make sure to assign "Direct Database Request" to the desired Application Roles
• Go to Manage > Identity > Application Roles
• Double Click on BIContentAuthor (in my case)
• Permission > Query Limits > Set "Execute Direct Database Request" to "Allow"

Setting up Permission from BI Presentation Services

Login with Admin privileges

• Go to Administration > Manage Privileges under Security section > Sroll down to the WriteBack privillage section at te bottom of the page
• Default setting would be this, Change it to below configuration,

Create analysis with WriteBack field

• Go to Column Properties of a Writable field > WriteBack tab
• Check the box for "Enable Write Back"

Here onwards, the additional steps to configure WriteBack mechanism for Pivot view.

• Convert the data type of column from Criteria > Edit Formula. Please Note: Data type of column in database can be of any type, we are not going to change it.

• Change the data format as HTML to make it editable.

@[html]<input class="WBInput" type="text" novalue="false" size="10" onchange="obipswb.Grid.Change(event)" onfocus="obipswb.Grid.Focus(event)" value=@H origvalue=@H>

Note: This is the default formatting for all writable columns in OBIEE

• Add a Pivot view to the analysis.
• Change the XML code of the report from Advanced tab. This will allow us to use the standard WriteBack functionality in Table view that OBIEE supports.

Find the start of XML code for Pivot table, i.e.

<saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="false" rowsPerPage="25">

Add the below code to the next line,

<saw:displayFormat>            

<saw:formatSpec>               

<sawwb:writeBack xmlns:sawwb="com.siebel.analytics.web/writeback/v1" xsi:type="sawwb:tableProperties" enabled="true" toggleTableMode="false" templateName="SetWriteBack_Pivot" useTemplate="true" buttonPos="right"/>

</saw:formatSpec>

</saw:displayFormat>

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. For Table view, we specify the name of the write-back template to use in Properties of that Table View, whereas we are setting it directly in XML of that report.

Some standard consideration while defining columns in WriteBack template:

Values can be referenced by position such as @1, @2, @3, ETC.

If a parameter’s data type is not an integer or real number, add single quotation marks around it.

But don't you think so this will not be applicable to our (special..!!) Pivot table?? Yes, it doesn't. We cannot specify columns by their position. If you observe, OBIEE generates unique column IDs for each of the column, we can consider that while writing SQL statement in WriteBack template.

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

<XML>

<writeBack connectionPool="FedEX_CP">

<insert> </insert>

<update>

MERGE INTO D1_ORDERS2 F

USING (

SELECT C.NEWKEY, T.YYYYMMDD, P.PRODUCTKEY, F.ACTLEXTND 

FROM D1_ORDERS2 F, D1_CALENDAR2 T, D1_PRODUCTS P, D1_CUSTOMER2 C

WHERE F.CUSTKEY = C.NEWKEY 

AND F.PERIODKEY = T.YYYYMMDD

AND F.PRODKEY = P.PRODUCTKEY

AND C.NAME = '@{ca28e047766770c2c}'

AND T.YYYYMMDD = '@{cdcaf965d4e3a312d}'

AND P.SPECIFICDESCRIPTIN = '@{c543b919b7bb3b49b}') S

ON (F.CUSTKEY = S.NEWKEY 

AND F.PERIODKEY = S.YYYYMMDD

AND F.PRODKEY = S.PRODUCTKEY)

WHEN MATCHED THEN UPDATE SET F.ACTLEXTND  = '@{ce0127765131535c9}'

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

Modify wbpivotview.js file

Now we will let Pivot view to do some work like his younger brother.. Let's change a JS source code file for Pivot View then. i.e. wbpivotview.js from location

C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\servers\bi_server1\tmp\_WL_user\analytics\za01ic\war\res\b_mozilla\views\pivot

C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\servers\bi_server1\tmp\_WL_user\analytics\eiguw6\war\res\b_mozilla\views\pivot

Replace the function definition for UpdateWriteBackActionForEdge

obipswb.Grid.UpdateWriteBackActionForEdge = function(c, f, h) {

var g = false;

var b = c.getWriteBackRows(f);

if (f == obips.JSDataLayout.DATA_EDGE) {

     for (var d = 0; d < b.length; d++) {

         var a = b[d];

         if (!a || a.length == 0) {

             continue

         }

   for (var dd = 0; dd < a.length; dd++) {

             var aa = a[dd];

       if (!aa) {

            continue

       }

       var e = obipswb.Grid.getRecordValues(c, a, f, aa.getCoordinate().getLayer(), aa.getCoordinate().getSlice());

  

       if (obipswb.Grid.shouldDeleteRow(aa)) {

           h.deleteRecord(e)

       } else {

           h.updateRecord(e)

       }

       g = true

   }

     }

     return g   

    }

for(var d = 0; d < b.length; d++) {

var a = b[d];

if(!a || a.length == 0) {

continue

}

var e = obipswb.Grid.getRecordValues(c, a, f, a[0].getCoordinate().getLayer(), a[0].getCoordinate().getSlice());

if(obipswb.Grid.shouldDeleteRow(a)) {

h.deleteRecord(e)

} else {

h.updateRecord(e)

}

g = true

}

return g

};

Restart services & Test the results.

• Restart the BI stack to reflect the changes in configuration files. 
• Open a report in a new tab that you created earlier.
• Try updating values from a Pivot view

Original Report:

Updating Values:

Updated Results:

I hope you guys find this helpful. Please comment if you face any issues..

Happy Blogging.. :)