Error Capturing in OBIEE Writeback

OBIEE Writeback

The OBIEE write back process does a bad job in capturing the errors that happen when writing back data. And often times we need to capture the error. Here is a quick way to capture the error message.

1) Set up writeback as directed in the Oracle documentation.

2) Create an error logging table using the dbms_errlog.create_error_log package. “ERR_LOG_TABLE”

3) Modify the SQL in your inset or update statement from the writeback template to include LOG ERRORS INTO WB_POC_ERR_ORA (‘Write Back’)

For e.g my insert statement that looked like

<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) </insert>

Will change to.

<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) LOG ERRORS INTO ERR_LOG_TABLE (‘Write Back’)</insert>

Now that you have these errors in a table in the database, you could have triggers, procedures, reports,javascripts built to further give the users meaningful error messages in the front end.

Advertisements

Row wise initialized variable in OBIEE 11g and VALUELISTOF function

OBIEE up to 10g had a limitation around session variables which are row wise initialized ,in that you cannot use that variable to initialize another subsequent session variable.  Good news!! 11g seems to have a fix for this.

For e.g. lets say you have a session (row wise initialized)  variable called “REGION” initialized as follows

select ‘REGION_LIST’ ,region_id from region_table.

Now lets say you want to initialize another row wise initialized variable based on the ‘REGION_LIST’ variable you had initialized earlier. For e.g. all the accounts belonging to the ‘REGION_LIST’ variable.

Here is the new way of doing it in  11g

select ‘ACCOUNT_LIST’ ,account_id from accounts where

region_id in (VALUELISTOF(NQ_SESSION.REGION_LIST)).

Keep in mind this will go back to the database as an in list, coma separated and the values would be in single quotes.