OBIEE write-back feature is an option which has very limited
feature and I am sure you would get stuck multiple times when you try to do
write-back even to do smallest of the change.
My idea was to maintain reporting business glossary using obiee-write
back. With the code I was able to create a write-back process for user to enter
comments for report columns and an approver could approve these comments and
display the approved glossary for end-user within same OBIEE screen. This blog
assumes you already know the use of write-back functionality and goes into
details of how to implement this.
Step
1:
Enable
write-back process by adding the required tag in ‘instanceconfig.xml’ file:
<LightWriteback>true</LightWriteback>
Step
2:
Physical
table property [on which write back would be performed] should have the below
property checked.
We are not checking ‘Cacheable’ property to make sure write-back does not fetch cache
data.
Step 3:
Any logical
column that needs to be written back should have the ‘Writeable’ property
checked:
Step
4:
Make sure
the required group in presentation layer has access to do write-back by
checking the ‘Read/ Write’ option for the relevant group. I had to check
multiple groups since there were multiple groups who had to do write-back. [one
group who submits request and the other who approves it].
Step
5:
Go to
Manage- Identity and make sure ‘Execute Direct Database Requests’ is Allowed
for the relevant ‘Database’:
Step
6:
Log into
OBIEE reports, and enable ‘write-back’ privilege :
Step
7:
We are all
set to do the write-back. We now need an XML file which will facilitate the
write-back process. I place the xml file in 3 different places:
§
C:\Oracle\OBIEE11.6\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages
§
C:\Oracle\OBIEE11.6\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb\customMessages
§
C:\Oracle\OBIEE11.6\Oracle_BI1\bifoundation\web\msgdb\customMessages
Step
8:
A screenshot
of my XML file which has many update statements:
I use
different write-back messages for different report and hence you see lot of
update statements. I did not have an insert requirement and I filled the
placeholder with a select statement. Put your ‘insert’ statement based on your
write-back need.
The last
step is to check ‘writeable’ option within column property and also to enter
the web message name in table properties:
Steps are
completed and here is how the report behaves:
Once the
update was successful, it shows the below message:
This record
is sent for approval and you can see the other column where the message
reflects:
The above
portion which I explained are good and easy part of write-back. Now let me go
through the set of tweaks I had to make to make some feature work.
Whenever
there were NULL values in the column that you are writing back, I had issues
[where the update statement was not getting executed] and I had to write column
formula in RPD and Report as:
- RPD:Case when ALS_REPORTFIELDGLOSSARY_FACT.WRITE_BACK_GLOSSARY is null then 'ENTER COMMENTS' else ALS_REPORTFIELDGLOSSARY_FACT. WRITE_BACK_GLOSSARY end
- Report: case when "XYZ"."Write Back Glossary"='ENTER COMMENTS' then ' ' else "Dimension"."Write Back Glossary" end
For the
write-back column in RPD, I replaced NULL value with some value ‘ENTER COMMENTS’ in RPD and again replaced
this value with a ‘space’ in the
report. With this the issue was resolved and the update was happening properly.
Please be careful when you do write-back, it is always good to create a backup table and test your functionality on this table before you start writing into the actual ones. There could be better way to achieve this functionality and leave the applicability part to the readers discretion.
