Thursday, May 16, 2013

OBIEE 11.1.1.7.0 Writeback Insert


OBIEE 11.1.1.7.0 Write back Insert implementation step by step


I have seen several posts for write back insert implementation on OBIEE 11g. Here am providing step for write back implementation.

created table 

create table POLICY_DETAILS
(  POLICY_ID NUMBER(10),
  LOB       VARCHAR2(20)
)tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );



 
I have inseted one null record in database look like below.

Insert into policy_details (LOB,POLICY_ID) VALUES (‘NULL’,NULL);

 




Import Policy_details table into RPD and create Alias for it and join each other using Policy_Id key column





  Uncheck the cacheable option --> physical layer -RPD table(Policy details)
 
 


Grant to connection pool permission (read/write) like below.


Make write able on necessary  columns in BMM Layer(Double click on column) like below.

Make sure table permission read and write radio button in presentation Layer.

Double click on the table name in presentation layer(Policy_details) and then click on permissions and then choose Read/Write options for Authenticated User and BIAdministrator,Weblogic.


Note: Must choose Read/Write option for Bi Administrator,Web logic user as well.
 
 




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




  Add writeback tag in instanconfig.xml file refer the below,

C:\OBIEE11G\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\ instanceconfig.xml (please take a back and do the changes) 

<LightWriteback>true</LightWriteback>



  
  Restart all bi components by using OPMN command


Manage Privileges --> grand permission likes below
 
Create Analysis 


 


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








Edit the table properties and do the below


and save the changes.

 Find the column id reference and note it finally you have to update on your Write Back.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 column Id's that we will be using to populate the Write back table with
Make a note of the column Ids 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




Create XML file as below and place it in
C:\OBIEE11G\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages

Note: Need to create custom Messages folder  

file name : WriteBack.xml and kept it below paths

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




C:\OBIEE11G\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages\WriteBack.xml

C:\Oracle\Middleware\Oracle_BI1\bifoundation\web\msgdb\customMessages

<?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="satyainsert">
         <XML>
            <writeBack connectionPool="Connection Pool">
        <insert>INSERT INTO policy_details (LOB,POLICY_ID) values ('@{c78fe17d55f8c7776}',@{ca4fd81deec1ec920})</insert>
                <update>SELECT NULL FROM DUAL</update>
            </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>

 



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




  Verify  inserting the data in Lob,policy_id column and then click on insert will insert the data into the Database

Note: similarly you can try for Updation/deletion write back comments
Update write back implementation please refer my post.
http://satyaobieesolutions.blogspot.in/2013/03/write-back.html

Hope this help's

Thanks,
Satya Ranki Reddy

2 comments: