Friday, March 29, 2013

Write Back

Write Back Implimentation on OBIEE 11g Step by Step

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

Create table policy_details
(policy_id  number(20) not null,
LOB varchar2(20),
constraint policy_id PRIMARY KEY (policy_id) );
looks like below screen










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)

 



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






 Login to UI

http://satya:7001/analytics/saw.dll?PrivilegeAdmin





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="satya">
         <XML>
            <writeBack connectionPool="Connection Pool">
                 <insert>SELECT NULL FROM DUAL</insert>
                <update>UPDATE policy_details SET policy_id = @{c4c8d3c64f4ff47c7} WHERE LOB =
'@{c1943e58ea9b2e36b}'</update>
            </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>






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




 Login into UI

 
  Verify  modifying the data in policy_id column and then click on update will update the modified data into the Database

Note: similarly you can try for insertion/deletion write back comments


Hope this help's

Thanks,
Satya Ranki Reddy





2 comments:

  1. Thanks, you guys that is a great explanation. keep up the good work.. home security service
    home security solution

    ReplyDelete
  2. Hi Satya, thanks a lot for sharing this blog. Your content is just outstanding.
    Write Back is the ability to enter values directly into a report and have those values used in calculations and charts in the report. For example, a report can have Sales Quota Amount defined as a write back field, Sales Amount as a field from the data warehouse, and Percentage of Quota as a calculated field (Sales Amount/Sales Quota Amount). When viewing the report you can change the Sales Quota Amount and the Percentage of Quota field recalculates appropriately.
    Would like to suggest the newbies looking for more information to visit this page as well-https://intellipaat.com/oracle-obiee-training/

    ReplyDelete