Writeback for dashboard commentary for OBIEE 11g

This is a first-pass solution for a question that invariably gets asked by all customers to add the capability to for a user to store comments against reports for OBIEE 11g.  It creates a “discussion thread” on a dashboard page to allow users to add comments to a page.

The solution is basic and requires a couple of requests to be added to each dashboard page that will have commentary enabled. The report need to duplicate and modify for each page as the page name needs to be added as a parameter. A session variable might be an option to implement this solution generically.

Database Changes

Create a write back table to store comments in Oracle DB schema, I have used the ‘BISAMPLE’ schema.

CREATE TABLE "WRITEBACK"."DASHBOARDCOMMENTS" (   "COMMENT_TS" TIMESTAMP (6) DEFAULT sysdate, "USER_NAME" VARCHAR2(50 BYTE),"DASHBOARD_NAME" VARCHAR2(200 BYTE),"COMMENTS" VARCHAR2(4000 BYTE));

-- Single Null row required for the insert writeback

insert into DASHBOARDCOMMENTS(USER_NAME, DASHBOARD_NAME, COMMENTS)values (null,null,null); -- Single Null row required for the insert Writeback

Repository Changes

The repository changes in steps for the configuration to write back to dashboardcomments table are detailed as follows. These steps remain same as far as the configuration steps are concerned for any table to make it be used for “Writeback” option.

Open the “BI Administration” tool and then go to File >> Import Metadata

Provide connection information/ import ‘DASHBOARDCOMMENTS’, then disable the cache for the table

Make the table columns “writeable” of the table ‘DASHBOARDCOMMENTS’ in Business Model layer

Assign the Groups to the  “writeable” columns in Presentation layer

Configuration File Changes

Create a folder called “CustomMessages” in the path shown in the below picture, if the folder is not available, then put an XML message file with write back tags as below.

<?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="wb_comment">
- <XML>
- <writeBack connectionPool="Sample Relational Connection">
  <insert>INSERT INTO DASHBOARDCOMMENTS(USER_NAME,DASHBOARD_NAME,COMMENTS) VALUES ('@1','@2','@3')</insert>
  <update>INSERT INTO DASHBOARDCOMMENTS(USER_NAME,DASHBOARD_NAME,COMMENTS) VALUES ('@1','@2','@3')</update>
  </writeBack>
  </XML>
  </WebMessage>  </WebMessageTable>
  </WebMessageTable> 

 

Add tag ‘LightWriteback” in instanceconfig.xml 

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
- <!--  Oracle Business Intelligence Presentation Services Configuration File -->
- <WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
- <ServerInstance>
   <LightWriteback>true</LightWriteback> - </ServerInstance>
  </WebConfig>

Write Back Access and Permissions

Set webcat permission for writeback for selected groups as below.

Commentary Answer Request

Create two reports, one report for data entry for comments while other one to display the comments entered by users.

Data Entry Report

Go to ‘Criteria” tab and create a request for comment entry screen as below.

Change the User Name column formula to VALUEOF (NQ_SESSION.USER) and Dashboard Name to ‘2.1 Simple Demo Dashboard' ( Name of dashboard/Page)

Set the write back property of column as shown below and set table properties as shown below.

Create a report to display entered comments by users by simply adding column user name, comment and time stamp.








Expose both the reports on dashboard, it looks something as below. This allows to enter the commnets on report by user and read the comments added by all users.