Detailed Write Back steps in OBIEE 11.1.1.6.2

Step 1

Amend the instanceconfig.xml and add the LIghtWriteback entry as shown below with in the server instance tags

The instanceconfig.xml file is located in $MW_HOME/instances/instance1/config/OracleBIPresentationServicesComponent
/coreapplication_obips1 directory

Add the LightWriteback tag just above the /ServerInstance tag as in above example.

<LightWriteback>true</LightWriteback>

Restart the OPMN services for the instanceconfig amendment above to take effect

Login to Analytics

http://<hostname>:9704/analytics

Set privileges within Administration screen

Add roles or users to the Write back privileges above

Step 2

Create a table used to store the write back table and import it into the physical layer in RPD

Ensure the connection pool user has required privileges to insert / update data on this table

Step 3

 

 

 

 

 

 

 

 

 

 

In Business Model, switch on the Writable property for all required columns

This completes the one time setup required to carry out write back functionality. Following steps are required for setting up an actual write back

Step 4

Create a new analysis with the required columns

Select the Column Properties using the drop down arrow on each column

Choose the column property and Tick the Enable Write Back check box on the Write Back tab for the required column. In my case, I have enabled only for USD Amount.

Note: If the ‘Writable’ flag is not enabled in RPD then this check box also cannot be enabled

Step 5

 

 

 

 

 

 

 

 

 

 

Note that only the USD_AMT column is displayed as editable

Choose the Edit Table View option and click on Table View Properties

 

 

 

 

 

 

 

 

 

 

Tick the Enable Write Back on enter a Template Name. I entered ‘RPA’ and this can be any user given name. Note this name is case sensitive. Below you will see how to create this template called RPA used here

Step 6

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 next step

Step 7

 

 

Create XML file as below and place it in

$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

If the directory ‘customMessages’ does not exist then create one in the path mentioned

 

--------Start of 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="RPA">

         <XML>

            <writeBack connectionPool="Writeback">

                 <insert></insert>

                <update>update encumbered_rpa set usd_amt = @{cbe48633dbeb2bda8},update_date = sysdate,updated_by = '@{cb26f41933b0c7d7c}' where product_id = @{c9c0a2069f38dda8c}</update>

            </writeBack>

         </XML>

      </WebMessage>

   </WebMessageTable>

</WebMessageTables>

-------End------

 

Within the update tags have the update statement that gets executed in the database using the columnIDs picked from the Advanced tab

Save the XML as RPA.xml

Bounce the OPMN service for this XML to be loaded and available for use

 

 

Step 8

 

 

Run the report that was created in Step 4

 

If all the steps are carried as they should be then you will see two buttons as in the above screen shot. The Update functionality is ready.

Amend the value of any editable field and hit the Update button then the change is carried out in the database and the screen gets refreshed with the new value. The statement that is given in the XML template between the update tags gets executed.

Revert button is used to undo any changes that are not yet sent to the database.

If you have noticed, the insert statement is left blank. But the same XML can be amended to provide the functionality of insert. If the amended record exists in DB, then the update statement is issued and if the amended record does not exist in DB then the insert statement is issued.

You don’t necessarily have a insert statement with the insert tags. You can use a function call here or you can issue an update statement to a different table. Using this base functionality you can deliver tailor made solutions fit for purpose.