Tuesday, February 5, 2013

[OBIEE 11g] Write Back Setup

OBIEE Write back setup


As we all know we have a Writeback property in OBIEE with Insert, Update and deletion.

Here is the complete document on OBIEE11g Writeback Update
Step 1: created table 

CREATE TABLE countries_sample(
          Country_Name varchar2(20),
          Country_Region_ID Number NOT NULL)
Note: Country_Region_Id column is having primary key
looks like below screen

Step 2:
  Import countries_sample into RPD and create Alias for it and join each other using Country_Region_Id key column

Step 3:
  Uncheck the cacheable option --> physical layer -RPD table(Countries_Sample)

Step 4: 
 Make writeable on necessary  columns in BMM Layer(Double click on column)


Step 5: 
 Make sure permission read and write radio button in presentation Layer.
Double click on the table name in presentation layer(Countries_Sample) and then click on permissions and then choose Read/Write options for Authenticated User and BIAdministrator

Note: Must choose Read/Write option for BIAdministrator

And also Double click on the necessary columns and choose Read/Write options in Presentation Layer

  Execute Direct DB request access give it required Application roles/users


Step 8
  Add writeback tag in instanconfig.xml file refer the below,
C:\Oracle\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\ instanceconfig.xml (please take a back and do the changes)

Step 9:
  Restart all bi components by using OPMN command

Step 10: 
 Login to UI

Manage Privileges --> grand permission likes below

Step 11: 
Create Analysis 

Enable Write Back check box on Country_Name Column properties
It looks like


Step 12:
Edit the table properties and do the below


and save the changes.

Step 13:
 Find the column id reference and note it finally you have to update on your WriteBack.xml file

Go to edit analysis then Click on the advanced tab of the analysis

Here under the Analysis XML box we want to note down all the columnID's that we will be using to populate the Writeback table with
Make a note of the columnIDs for the required columns. These will be used in the WriteBack.xml file after update this you need to restart OPMN Services via opmnctl command


We can use column positions also like @1 for column1 and so on

Create XML file as below and place it in

Note: Need to create customMessages folder  

file name : WriteBack.xml and kept it below path

Note :This file name should match the Template Name in Table Properties

<?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="WriteBack">
            <writeBack connectionPool="Connection Pool">
                <update>update countries_sample set country_name = '@2' WHERE country_region_id = @1</update>
looks like below path

restart bi components (if any changes on your xml file)

Step 16:
Testing: Login into UI

  Very by modifying the data in Country_Name column and then click on update will update the modified data into the Database

Note: similarly you can try for insertion/deletion writeback comments


1 comment:

  1. Thank you for this useful topic. I have succeeded in setting up writeback for UPDATE STATEMENT. However, INSERT seems a little bit more tricky. Even with a very simple INSERT statement nothing happens. Any ideas on this issue?