Writeback Analysis in OBIEE 11g - part 1

writeback –> ability to modify data that is displayed in an analysis in oracle answers.  very crude way of putting it. Please refer to the OBIEE docs from Oracle for the limitations.

Interestingly, this contradicts the basic concept of an OLAP system, which is primarily meant for fetching data rather than inserting data. However there are some special cases, where you would want this functionality. There are certain design considerations too that are involved with the data warehouse, which we will discuss in another post.

I’m creating a demo table for this purpose

create table JT_DEMO_WRITEBACK
(
    CUST_KEY    NUMBER  PRIMARY KEY,
    CUST_NAME VARCHAR2(255) NOT NULL,
    CUST_RATING NUMBER NOT NULL
);

lets insert some data to look like this:

Login to Oracle Answers. got to Administration page. Under security click the “Manage Privileges” link

change the “Write Back to Database” privilege

start the BI Admin Tool and open your rpd. In the physical layer, create a new connection pool called “WB Connection Pool”. Import this table. create an alias for this table.

disable caching on your physical table that you want to write back to.

create a new Business Model to look like this

we want the Customer Rating field to be editable(writable) in our Answers page.

Create the new subject area in the presentation layer as shown below. for simplicity purpose, I’m setting an implicit fact for the WB Test Subject area.

set the permission to read write for the relevant users

save your rpd and check for consistency.

Enable writeback in the configuration file by Modifying  the instanceconfig.xml file located in $(ORACLE_INSTANCE)\config\OracleBIPresentationServicesComponent\coreapplication_obips1

add the following to the instanceconfig.xml file

<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
   <ServerInstance>
      <!-- added by JT -->
      <LightWriteback>true</LightWriteback>

</ServerInstance>
</WebConfig>

Deploy your rpd and restart the BI services.

create a new Analysis as shown

go to the Advanced tab and note the column ids in the xml code. we will use these ids in our xml template to specify the SQL to update the customer rating field.

In the criteria tab, mark the customer rating column writable ( this option is enabled in Answers if you enabled the writable flag for the column in the rpd).

go to results tab –> edit view (pencil icon) –> Table view properties (the xyz icon) and enable writeback option and specify the template name

create your template file which contains the SQL code for the update.

<?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="wb_cusRating">
<XML>
<writeBack connectionPool="WB Connection Pool">
<insert> </insert>
<update>
UPDATE BISAMPLE.JT_DEMO_WRITEBACK SET CUST_RATING=@{c8f6dc321eca7766c} WHERE CUST_KEY=@{c6657b10f6946b91b}
</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

save the above text to a new file called writebacktemplateJT.xml put it in the location $(ORACLE_INSTANCE)\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

note:

1. you may want to create customMessages directory if it does not exist.
2. the line <WebMessage name="wb_cusRating"> specifies the template name.
3. save your analysis to a folder as “WB Test”.

Restart OPMN services. login to Answers page with the user, whom you have given the necessary rights. open the “WB Test” analysis

notice that the Cust Rating  field is editable, change the value and click on the update button. OBIEE updates the data to the physical database and refreshes the page with the new values.

note: you might get the writeback error in red as shown below

There seems to be a bug with the write back feature in OBIEE 11g. A patch
is currently available only for 64 bit Win systems. Bug 13067844, Patch
13067844. After installing the patch, the writeback works properly.