OBIEE Write-back, Glossary updates and approval process through OBIEE

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.