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
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
Security, Privileges
First, you must grant your account with the privilege “Write Back to database” :
Second, you must grant the direct access database to the user through the Administration Tool in the security part.
- Login in OBI Presentation Service Website
- Follow this links : Setting / Administration / Manage Privileges
- 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.
- Login in OBI Administration
- Go to the menu : Manage / Security
- 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.
- Go to the Table Component
- 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, … )
- Fill the template name with for example : SetWriteBackValue. (This name will indicate later the statement to update or insert in the database)
Column properties
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".
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
<?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 !
- Change the value of the column that you have configured (Here above the VALUE column)
- 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.
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.
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
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
Hi,
ReplyDeleteNice piece of information shared to know more about OBIEE 10g and 11g http://www.wiziq.com/course/22309 is providing a online course starting in the month of June so anyone interested to learn online can enroll on this site.
Where is the demonstration of calculated columns based on writeback? If you have any resource on it please provide to me
ReplyDelete