Write Back in OBIEE 10g/11g

Write Back in OBIEE 10g:

             Write Back is the ability to enter values directly into a report and have those values used in calculations and charts in the report. For example, a report can have Sales Quota Amount defined as a write back field, Sales Amount as a field from the data warehouse, and Percentage of Quota as a calculated field (Sales Amount/Sales Quota Amount). When viewing the report you can change the Sales Quota Amount and the Percentage of Quota field recalculates appropriately.
This document give a step-by-step guide with picture to help the implementation of this functionality.
You can use this functionnality only in a table view. If you want to use it on a pivot, you must first transform your table view as a pivot view : OBIEE - How to perform a pivot in a table view

For 11g, you must now explicitly select the Writeable option for each logical column for which you want to enable write-back.

Security, Privileges

First, you must grant your account with the privilege “Write Back to database” :

1. Login in OBI Presentation Service Website
2. Follow this links : Setting / Administration / Manage Privileges
3. Grant the privilege “Write Back to database” to the permitted group

Second, you must grant the direct access database to the user through the Administration Tool in the security part.

1. Login in OBI Administration
2. Go to the menu : Manage / Security
3. Grant the privilege “Execute Direct Database Access” to the permitted group of user

Configure Write Back Report

Table properties

You must choose a report in Answers to be able to writing back to the database.

1. Go to the Table Component
2. Click on the write back properties icon (a green database with a yellow pen). Remark that each column have on this head a letter C and a number (Ex. c1, C2, … )

1. Fill the template name with for example : SetWriteBackValue. (This name will indicate later the statement to update or insert in the database)

Column properties

• Set to “write back” the value integration type of the column you want to be able to update.

Write Back Template to define the DML statement

The write back template is an custom messages (XML-formatted) file that contains SQL commands needed to insert and update records in the write back table and columns you have configured. It must be store in the directory "OracleBI\web\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="SetWriteBackValue">
         <XML>
            <writeBack connectionPool="Connection Pool">
                <insert>INSERT INTO OTHER_SCHEMA.WRITE_BACK VALUES (@{c0})</insert>
                <update>UPDATE OTHER_SCHEMA.WRITE_BACK SET VALUE='@{c0}' WHERE PK=@{c2}</update>
     </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>

The line <WebMessage name="SetWriteBackValue"> contain the value of the template name. This value must match with the value filled in the table properties.
The line <writeBack connectionPool=“Connection Pool”> contain the value of the connection pool. You must have the same name in the repository.
The line <update>UPDATE WRITE_BACK SET VALUE='@{c0}' WHERE PK=@{c2}</update> contain the SQL statement to update the database.
Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}). To find the column ID, see the header columns labels in the table

The custom message are loaded when the OBI Presentation Service is started therefore restart it before testing.

When the update and insert statement run

• The insert command runs when the update value is null.
• Update command runs when a user modifies existing data.

How to update

You are now able to launch “Write Back” capabilities !

1. Change the value of the column that you have configured (Here above the VALUE column)
2. Click on the button in the bottom-right corner of the table (Here above the WRITE BACK VALIDATION button)

Support

the Update Button is grey

The write back button is grey when Oracle BI Presentation server hasn't found the XML template message.

Two reasons for this :

• The name of the template name in the report is different than in the XML message.

Example with the name : SetWriteBackValue

must be the same in the node WebMessage of the xml message :

<WebMessage name="SetWriteBackValue">

• Or may be you simply forgot to restart the OBI Presentation Service. (The messages are loaded during the initialization)

Write Back in OBIEE 11g:

Introduction:

As we all know we have a Writeback property in OBIEE with Insert and Update. But many of us don’t know that we can even implement delete Writeback which we made in our project as per our client’s requirement.

Here I am going to provide everyone a complete document on OBIEE Writeback which includes Insert, Update and Delete WRITEBACK.

Requirement:

We have a complete new table created for Writeback (No data) and in this we have to implement inserting new records, updating them and deleting the record entered. Data to be entered a fresh.

Changes in Instance Config File:

Add the following tag in instance config file.

<WebConfig>

             <ServerInstance>

                 <LightWriteback>true</LightWriteback>

            </ServerInstance>

RPD Changes for Writeback:

Before implementing Writeback following are the RPD changes.

1.      Import the created table in to RPD and make necessary joins in physical layer.

2.      Make necessary changes in the RPD business layer as per the design.

3.      In BMM Layer which ever column to be made editable please check writable option as shown below.

Fig 1: Enabling Writeable option in BMM Layer

4.      Make the above changes for necessary columns and drag that to presentation layer and save it.

5.      Once above changes are done create the report in UI and do following change.

a.      Whichever columns required for editable enable Writeback option in the report as shown below and set the text field and save the report.

Fig 2: Enabling Writeable option in Report

Insert Writeback: For inserting records,

1.      Make sure that table should have a null record for inserting the data.

2.      Create an xml file as shown below and save with the same web message name used in this xml marked with yellow color. Also add insert syntax for which u r going to insert data in the insert tag.

<?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="DSR_Twist_writeback_insert">

         <XML>

            <writeBack connectionPool="BI Writeback Connection Pool">

 <update>

insert into BI_WRITEBACK(WRITEBACK_KEY,SUBJECT_AREA,DASHBOARD_NAME,PAGE_NAME,REPORT_NAME,COMMENT_TEXT,SORT_ORDER,REPORT_DATE,REPORT_REGION)

values (to_char(seq_writeback.nextval),'DSR Reports','DSR - TWIST','Global Sales','Market Overview','@{ce0535f1ee8fd6988}','@{c1771a2407a39a37a}','@{cfe255305c4bb1c5b}','@{c8eec6ef257ca3d52}')

 </update>

<insert>

insert into BI_WRITEBACK(WRITEBACK_KEY,SUBJECT_AREA,DASHBOARD_NAME,PAGE_NAME,REPORT_NAME,COMMENT_TEXT,SORT_ORDER,REPORT_DATE,REPORT_REGION)

values (to_char(seq_writeback.nextval),'DSR Reports','DSR - TWIST','Global Sales','Market Overview','@{ce0535f1ee8fd6988}','@{c1771a2407a39a37a}','@{cfe255305c4bb1c5b}','@{c8eec6ef257ca3d52}')

 </insert>

        </writeBack>

         </XML>

      </WebMessage>

   </WebMessageTable>

</WebMessageTables>

The above xml file to be placed in the following path

C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages

Create the customMessages folder in the above as it won’t be available here with same name shown above with all case sensitive’s.

Fig 3: XML File for insert and its location

Once the above step is done go to table properties and enable Writeback.

                                                              i.      Give the template name here whichever u give in xml file.

                                                            ii.      Disable Toggle Table Mode which will be enabled by default and this is because if we don’t disable it editing of column will not happen.

                                                          iii.      Also give our necessary naming conventions in the apply button which ever required.

                                                           iv.      Standards Insert for inserting and Update for updating remaining defaults as shown below and click ok.

                                       Fig 4: Passing xml file web message name in the table properties for insert

Update Writeback: For Updating records,

Create an xml file as shown below and save with the same web message name used in this xml marked with yellow color. Also add update syntax for which u r going to update data in the update tag.

<?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="DSR_Twist_writeback_update">

         <XML>

            <writeBack connectionPool="BI Writeback Connection Pool">

         <update>

update BI_WRITEBACK set

SUBJECT_AREA='DSR Reports',DASHBOARD_NAME='DSR - TWIST',PAGE_NAME='Global

Sales',

REPORT_NAME='Market Overview',COMMENT_TEXT='@{ce0535f1ee8fd6988}',SORT_ORDER=@{c1771a2407a39a37a},REPORT_

REGION='@{c8eec6ef257ca3d52}'

where WRITEBACK_KEY='@{c33a77793cd508cf9}' </update>

<insert> </insert>

        </writeBack>

         </XML>

      </WebMessage>

   </WebMessageTable>

</WebMessageTables

The above xml file to be placed in the following path

C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages

Create the customMessages folder in the above as it won’t be available here with same name shown above with all case sensitives.

Fig 5: XML File for update and its location

Once the above step is done go to table properties and enable Writeback.

                                                              i.      Give the template name here whichever u give in xml file.

                                                            ii.      Disable Toggle Table Mode which will be enabled by default and this is because if we don’t disable it editing of column will not happen.

                                                          iii.      Also give our necessary naming conventions in the apply button which ever required.

Standards Insert for inserting and Update for updating remaining defaults as shown below and click ok.

  Fig 6: Passing xml file web message name in the table properties for update

Once all the above changes are done restart the services for getting all the changed affected.

Finally Insert and Update looks like the following screenshot once u make insert or update we get a mess updates are successful.

                             

                                                                 Fig 7: Final layout of reportafter inserting and updating records

Delete Writeback: For Deleting records,

Delete Writeback is not possible directly as we did for Insert and Update. There is no provision in the xml file for delete as we have tags for insert and update.

We generally create 2 reports for deleting records.

Steps for deleting the records which are Inserted and Updated:

1.      Create a main report with the corresponding columns as shown below. For better delete operation use at least a primary key column of that table i.e., a unique key column. In the below screen shot we have a unique column called Writeback Key and this is a varchar in our case.

Fig 8: Main report created for delete writeback

2.      Create a function in the backend as shown below.

create or replace function writeback_delete_geo(r_key in varchar2,to_delete         in varchar2)

            return varchar2

            is pragma autonomous_transaction;

            v_geo varchar2(100);

            begin

            if to_delete = 'yes' then

            delete bi_writeback where writeback_key = r_key;

            commit;

            return 'deleted';

            else

            return 'no delete';

            end if;

            exception

            when others then        return 'failure';           end;

In the above function delete operation is handled on a unique column where there are no repetitions of data else if repetitions are there, more than a record is deleted which is not correct. So always make it on a unique identifier.

In our case I made this key varchar because I passed character values like failure, delete and no delete in the function as return values and this will holds good only when the unique identifier can be a char.

3.      Create another report which will be drill down from main report created previously and make the unique identifier is prompted in the drill report.

In the child report drag 2 columns one unique identifier column and other dummy column as shown below.

Fig 9: Sub report creted for delete writeback

In the other column called delete writeback we need add the following formula when u create the report i.e.,

EVALUATE('WRITEBACK_DELETE_GEO(%1,''No'')',"Writeback"."Writeback Key")

Here for evaluate function pass the function that we created in the backend and pass parameter No on the key that we have created the function with delete statement.

Once we create this report and display the results report looks like

                                                                Fig 10: After creating Subreport in table view

4.      Now add narrative and type  The row is @2 successfully.

The layout looks like

                                                                                           Fig 11: Adding Narrative view

5.      Now put only narrative by deleting table view from the above screen shot which looks like

                                                                                             Fig 12: Only Narrative View

6.      Once done go to criteria and change the delete writeback column to the following formula which is

EVALUATE('WRITEBACK_DELETE_GEO(%1,''Yes'')',"Writeback"."Writeback Key")

Once this is done don’t run the report just save the report if we run the report then whole data in that table is lost. We need to re insert the data in the table. Make sure we don’t delete data while creating the intermediate report itself.

7.      Once these changes are done navigate the intermediate report from the main report on the unique column and save it.

8.      Once we click on the main report the result is as below.

Fig 13:Main report after enabling drill on writeback key

Click on Writeback Key where link is enabled the result is as

Fig 14: Message after deleting a row

After deleting the report looks like

Fig 15: Final Report

Record is being deleted.

In this way delete Writeback is implemented.

Note:

1.       If this Writeback key is not unique and repeating then all those records will be deleted.

2.       There are some bugs like while inserting and updating records some time it    throws error but when u edit report and insert or update and save then it never throws error in either in answers or dashboard but this is not always only some times.

Final Layout looks like

Fig 16: Dashboard Layout with Insert,Update and Delete Strategies

Another way of using delete writeback

Delete Writeback: For inserting records,

1.      Make sure that table should have a null record for inserting the data.

2.      Create an xml file as shown below and save with the same web message name used in this xml marked with yellow color. Also add insert syntax for which u r going to insert data in the insert tag.

<?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="DSR_Twist_writeback_insert">

         <XML>

            <writeBack connectionPool="BI Writeback Connection Pool">

 <update>

delete bi_writeback where writeback_key = '@{ce0535f1ee8fd6988}';

</update>

<insert>

delete bi_writeback where writeback_key = '@{ce0535f1ee8fd6988}';

 </insert>

        </writeBack>

         </XML>

      </WebMessage>

   </WebMessageTable>

</WebMessageTables>

The above xml file to be placed in the following path

C:\app\product\obiee\Oracle_BI1\bifoundation\web\msgdb\customMessages

Create the customMessages folder in the above as it won’t be available here with same name shown above with all case sensitive’s.

Once the above step is done go to table properties and enable Writeback.

                                                             v.      Give the template name here whichever u give in xml file.

                                                           vi.      Disable Toggle Table Mode which will be enabled by default and this is because if we don’t disable it editing of column will not happen.

                                                         vii.      Also give our necessary naming conventions in the apply button which ever required.

                                                       viii.      Standards Insert for inserting ,Update for updating remaining defaults and Delete for deleting records as shown below and click ok.

Thanks