[OBIEE 11g] Write Back Setup

OBIEE 11.1.1.6.2 Write back setup

Introduction:

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

 

My System Config:

Windows 7 64bit, Google Chrome browser, OBIEE11.1.16.2BP1 and MS-SQL Server 2008

Step 1: created table

USE [obirep]

GO

Object:  Table [dbo].[Country_Adj]   

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Country_Adj](

            [Country] [varchar](20) NOT NULL,

            [Adjustment_SGD] [decimal](18, 0) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

looks like below screen

Step 2: imported table into RPD (just do joins, Mapping..etc)

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

Step 4: Make writeable on your commentary column

Step 5: Make sure permission read and write radio button.

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

 

that's all RPD end...:) :)-

Step 7: add writeback tag in your 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 8: Restart all bi components by using OPMN command

 Step 9: Example, try login below URL

http://localhost:9704/analytics/saw.dll?PrivilegeAdmin

Manage Privileges --> grand permission likes below

Step 10: Enable check box on your analysis view (commentary/writeback column) properties 

looks like below,

 

 

 

 

Step 11:Edit the table properties and do the below

and save the changes.

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

Go to edit analysis of your commentary/writeback report-->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

Step :13

 

Create XML file as below and place it in

C:\Oracle\Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb\customMessages

 

Note:

$MW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages directory if the analytics res directory has been deployed.

Please see the Deploying analyticsRes for custom Messages post that we have created that wont ever get overwritten by Oracle Patches etc

  

If you dont want to use the analyticsRes custom messages folder then you can use the seeded folder

file name of mine is:   WriteBack.xml and kept it below path

C:\Oracle\Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\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="WriteBack">

         <XML>

            <writeBack connectionPool="Connection Pool">

                 <insert></insert>

                <update>UPDATE dbo.Country_Adj SET Adjustment_SGD = @{c9d0b2f1347dbf526} WHERE Country = '@{c130d7af0c9f77c31}'</update>

            </writeBack>

         </XML>

      </WebMessage>

   </WebMessageTable>

</WebMessageTables>

  

looks like below path

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

Step 14:Testing:

http://deva-pc:9704/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FWriteback%2Ftest_writeback

Step15: Verify the updated comments

after update India 11111 to India adjustment amount then its affected in our DB table ...see below results

Note: similarly for insertion/deletion writeback comments