How to update report and automatically store updated by and updated time through writeback in OBIEE

Today I am going to talk about writeback in OBIEE. As you know, writeback is a feature in OBIEE that allows users to update reports that they see in OBIEE without having to deal with the ETL process and reloading table. 

There can be reasons why users would want to be able to update the records they are seeing in the report. There could be a known error that users are aware of in their accounting system that is producing the wrong Journal Entries that they are seeing, long term solution will be to correct the data from the system, but for auditing purposes at quarter-end, they are just going to manually update the record through OBIEE report so that the numbers will balance out. 

Another user case can be to have users manually update their username and password in OBIEE on their own. I know that in big enterprises, the user credentials are typically synchronized with their other credentials so it would not likely to have such requirement to let users change their passwords, but in some cases where you may have external users who are not part of the company, they may want to have their own password that can be changed. They can do so by using writeback against the user table to have username and password fields updated.

Anyways, the set up of writeback is pretty standard, which I am not going to get in too much details. Google around and you will find many articles that shows you how to do so. Basically, it comes down to the following steps:

1.you update the instanceconfig.xml file to enable writeback feature. 

2. Enable writeback on the columns you want in rpd (BMM Layer)

3. Give permissions to the application roles to have writeback, done in RPD. (Check permission identity manager and presentation column)

4. Create writeback template and save it as writeback.xml file and put it in following directory in the obiee server machine  /opt/app/OBIEE/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages/  
 

5. Unable writeback in presentation service by checking the setting in column property

6. Define writeback template name in table properties (Only Table view setting)

So here I am only going to talk about step 4, how to create writeback template so that not only it updates the columns, but also it automatically update the username and timestamp when any user executes the writeback.

In my case I have 7 update-able columns, and I have created 3 columns to help with the writeback: last_updated_by, last_updated_date, record_ID. Last_updated_by will store a default system generated value initally and Last_Updated_Date will store the system timestamp of the day that data was loaded to the table through ETL. Record_ID is like Row_ID, it is a unique identifier of each row of the table. 

With the requirement, I am first creating my report in OBIEE 

Noticed I added a new column called 'User'. The expression of this column is 'VALUEOF(NQ_SESSION.USER)', this gives you the user ID of the user who is using the report (and of course, who is likely going to update record next). If you have successfully configured user authentications in rpd, you will have this session variable, it could be named differently in your environment. 

In Advance Tab, you will find the XML code for this report, there you will pick up the column ID for each of the columns you are interested. I will show you an easy way to identify the column IDs:

So here is a part of the entire XML code:

Now if I want to find the column ID of Record ID, I will then do control+F and type 'Record ID' in my search box. You will find the column name highlighted by the computer search, then find the column_ID right above this column name, it will be the column ID of this column name:

Note: Record_ID is the physical column name and Record ID is the displayed column name that I made:

Using this method, I have found all of the column IDs of the columns that I am interested. 

Now let's start writing the writeback template:

Template:

 ------ The name of your template, you name it however

 ---- This is the name of connection pool that your physical table 

SELECT NULL from DUAL                                                        

UPDATE fa_table SET JE_CATEGORY = '@{c4532bb3c8bf3fa07}', COST_CENTER = '@{c2133b77cfebd0a7c}', PRODUCT = '@{cfd5c9816d8ca43fa}' ,

PROJECT = '@{c06bafe4ddddd622e}', GEOGRAPHY = '@{c55fdf58fcd9b827e}', FUTURE2 = '@{c44356274c12d521f}', YES_NO = '@{c1e7217420772545b}', LAST_UPDATED_BY = '@{cf5dd00aa4eeabdf3}',

LAST_UPDATED_DATE = sysdate

WHERE RECORD_ID= @{c70c57d49eba128a6}  

Let's talk about this part:

UPDATE fa_table SET JE_CATEGORY = '@{c4532bb3c8bf3fa07}', COST_CENTER = '@{c2133b77cfebd0a7c}', PRODUCT = '@{cfd5c9816d8ca43fa}' ,

PROJECT = '@{c06bafe4ddddd622e}', GEOGRAPHY = '@{c55fdf58fcd9b827e}', FUTURE2 = '@{c44356274c12d521f}', YES_NO = '@{c1e7217420772545b}', LAST_UPDATED_BY = '@{cf5dd00aa4eeabdf3}',

LAST_UPDATED_DATE = sysdate

WHERE RECORD_ID= @{c70c57d49eba128a6}

fa_table is the physical table name. The following 7 columns are to be update-able by users:

JE_CATEGORY, COST_CENTER, PRODUCT, PROJECT, GEOGRAPHY, FUTURE2, YES_NO

So each of these columns are equal it's column ID.

Now column LAST_UPDATED_BY = cf5dd00aa4eeabdf3. This column ID is not for last_updated_by, it's actually the column ID for column USER. The column ID of last_updated_by is never used.

The idea here is that last_updated_by will be updated with the value of column USER, which is the username of the session. 

Now last_Updated_Date = sysdate. This means whenever user is updating the record, it's happening at the present moment, therefore this column will be updated with whichever date the update happens.

In the where clause I am using record_id as the criteria because I want to ensure that when user makes an update, the update is only applied to 1 row, not a whole bunch of rows. Without record_id in the where clause, then when user updates cost center record, then all of the rows with the same cost center name that the user is trying to modify will get updated. 

After putting this writeback.xml in the right place and restarting OBIEE, let's go back to the report. Here it is import to uncheck the writeback on column last updated by and last updated on:

Define the template name in the table view properties:

Now let's run the report and make some updates:

Updating JE Catogory with random stuff:

After done and the record is updated. Moreover the last_updated_by column is automatically updated with my username.

Now it is important to sort the report based on record ID column. Because every time you update a text field with different value, the row will be re-sorted because text field is always going alphabetically. To avoid having your updated record 'disappeared' on you after you save, just sort your report by record ID.

Thanks and until next time.