Tuesday, February 5, 2013

[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





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:










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
 
 




7 comments:

  1. hi Shri,,nice article. I have followed same steps but error appears as : The system is unable to read the Write Back Template 'WriteBack'. Please contact your system administrator. please help

    ReplyDelete
  2. Hi Srini.. We have deployed analyticsRes folder hence I have saved the xml file there. But the system gives "The system is unable to read the Write Back Template 'WriteBack'" error. Checked the syntax and it looks good.

    ReplyDelete
  3. Useful article . I was fascinated by the specifics . Does someone know if I can obtain a sample a form document to edit ?

    ReplyDelete
    Replies
    1. Hi Gisele, my assistant completed a template a form version at this place http://pdf.ac/2JOqSN.

      Delete
  4. this works great unless you have null value in the data. I need help with adding value to a null value spot

    ReplyDelete
    Replies
    1. Error
      An error occurred while writing to the server. Please check to make sure you have entered appropriate values.

      Delete
  5. Mobilunity is a main reevaluating organization with more than 11 years of involvement. We spend significant time in an enormous assortment of improvement arrangements, including giving committed Shopify engineers to seaward versatile application advancement administrations and website architecture arrangements, for clients all over the planet. Our fundamental spotlight is on giving areas of strength for organizations groups that can surpass assumptions.

    At Mobilunity, we invest wholeheartedly in approaching an ability pool of in excess of 200,000 exceptionally experienced and taught Ukrainian programming engineers. They are driven by a learned, talented and experienced group that generally exceeds all expectations to effectively convey excellent arrangements and productivity to all clients>> Mobilunity

    ReplyDelete