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.
