Tuesday, February 5, 2013

[OBIEE 11g] Write Back Setup


OBIEE 11.1.1.6 Write back setup

INTRODUCTION :

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
  





Step:6:
  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)
<LightWriteback>true</LightWriteback>





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

Or 

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

Step:14
Create XML file as below and place it in
D:\Obiee\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

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

D:\Obiee\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages\WriteBack.xml
<?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">
         <XML>
            <writeBack connectionPool="Connection Pool">
                 <insert></insert>
                <update>update countries_sample set country_name = '@2' WHERE country_region_id = @1</update>
            </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>
looks like below path






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


Step 16:
Testing: Login into UI





Step17:
  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?

    ReplyDelete