In this blog we will see how to enable Writeback in OBIEE
and one example like where users will be given option to choose values from
dropdown to select any one and update in backend rather than typing in freeform
text field in the OBIEE report. We will use Jquery to achieve it.
Below are the high level steps to enable write back in
OBIEE.
·
File level configurations
·
RPD changes
·
Report changes
File level configurations
Step 1: Edit instanceconfig.xml
Here is the path to find instanceconfig.xml file
<Middleware>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obipsn
And add below line under <DSN>
<LightWriteBack
>true</LightWriteBack >
Step 2. Create WriteBack template
We need to provide a template to the BI Server so that it
will handle the writeback operation based on logic provided in template.
And place this template in below location in the Middleware
server.
<Middleware>/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/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="Update_Comments">
<XML>
<writeBack
connectionPool="Oracle Data Warehouse Connection Pool">
<insert>
</insert>
<update>UPDATE
EMPLOYEES set COMMENTS='@2' WHERE EMP_NAME='@1'</update>
<!-- Identify the columns that are to be referenced. We can use
the column position like @1 for first column, or by column id as you see them
in the XML definition/Table view. -->
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
|
And name it like anything let say WriteBackTemplate.xml and copy to customMessages
folder in the server.
RPD Changes
Step 3: Enable Write Back in the RPD logical column
Navigate to to BMM table and expand column which you want
to use for writeback
And enable the Writable checkbox under general table of
logical column.
Navigate to Subject Area-> Presentation Table->
Writeable column (here column is comments in our example)
Double click on Permissions and select Read/Write for the
application role for which you like to enable writeback feature
Here for BIAdministrator Read/Write permission enabled on
this writable column.So that any user with this application role can writeback
data from obiee report.
Step 5. Allow “Execute Direct Database Request” to database.
We need to set the permission to application role so that
user with role should be able to contact database for insert/update data in
backend.
Navigate Manage > Identity > Application Roles >
BIAdministrator > Permission>Query Limits tab
And select “Execute Direct Database Requests” to Allow for
the Database where in which you writable table or column .
Step 6. Disable Cacheable option for physical table.
Navigate to Physical Table and uncheck Cacheable checkbox.
Step 7. Grant Writeback privilege to Apllication Role or
Users
Login Analytics and navigate to Administrator->Manage
Privileges
And search for Write Back property and “Write Back to Database”
Grant access to BIAdministrator role
Report Changes
Step 8. Create a OBIEE report
Create a report on this subject area and pull employee name
and comments and another dummy column (Select Comment)
Note: Here Comments is the real write back column.whatever the value we select from the dropdown will be updated into this writeback column.
Note: Here Comments is the real write back column.whatever the value we select from the dropdown will be updated into this writeback column.
Here we will use the JQuery to display drop-down on "Select Comment" column
Edit formula and add below code.
'<select id="user_comment" onchange=obipswb.Grid.Change(event)>
<option value="No Comment">Select
Comment</option>
<option value="Good">Good</option>
<option value="Bad">Bad</option>
</select>'
|
Make sure you selected checkbox for “Contains HTML markup”.
On clicking OK then it thrown error as
We can ignore this error and proceed further.
Next override default data format to HTML.
Now enable Writeback to the Comments column
Open table properties and provide WebMessage name (This should
be taken from your WriteBackTemplate.xml).
Check the report to test WriteBack feature.
Once you select any value from dropdown then Apply button
will be enabled and record can be updated on clicking apply. Note that drop-down column will not be updated with value you selceted and you can only see this value in the next column "Comments" which is write back column.ON clicking apply report will be refreshed and new value will be displayed on report.
