WriteBack Configuration In OBIEE 12c

WriteBack in OBIEE 11g and 12c is somewhat different.. I think it is more simplified as there is no need to add any XML tag to instanceconfig.xml file. (Changing of any config files manyally is not recommended in any case, we can use  MBean Browser instead.)

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)

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

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

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

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 > Scroll down to the WriteBack privilege section at the 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"
• Select the Text Field Width as 10. (This box is not very much clear enough but you can just click over and type the value, this worked for me)

• Click on the Results tab.
• Go to properties of Table View.

• Enable WriteBack > Set Template Name

Configuring WriteBack.xml template

To place this template, there's a slight change in location than that was in 11g version. There is no default directory named customMessages where we are supposed to place WriteBack templates.

Create customMessages directory under C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\bidata\service_instances\ssi\metadata\content\msgdb\l_en

Note: 

You must also include SQL commands to insert and update records. These SQL commands reference the values passed in the write back schema to generate the SQL statements to modify the database table. Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}). Column positions start numbering with 1, whereas column IDs start with c0. If a parameter’s data type is not an integer or real number, add single quotation marks around it.

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. The insert tag is only necessary if there

are null values in the write back physical column. This example uses both elements. Oracle BI Server will choose between update and insert, depending on whether the column is null.

Create a WriteBack templete as below, (Change WebMessage name, connectionPool name, SQL code as per your requirement)

<?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 = '@1'

AND T.YYYYMMDD = '@3'

AND P.SPECIFICDESCRIPTIN = '@2') S

ON (F.CUSTKEY = S.NEWKEY 

AND F.PERIODKEY = S.YYYYMMDD

AND F.PRODKEY = S.PRODUCTKEY)

WHEN MATCHED THEN UPDATE SET F.ACTLEXTND  = '@4'

</update>

</writeBack>

</XML>

</WebMessage>

</WebMessageTable>

</WebMessageTables>

Test the result

Open a saved Analysis in another browser window and test whether the WriteBack is implemented correctly or not. If it is correctly configured, you will see a message saying "Update Successful" when you modify some values and click on Apply.

Happy Blogging..!!