OBIEE -- Writeback update while keep the update history

Hello

I implemented the writeback in OBIEE as mentioned in this post, however the writeback feature in OBIEE is very basic, it does simple overriding of the data and it doesn't keep the historic changes. I have tried playing around with the writeback.xml file with insert or update tags, but i had no luck with it. Therefore, I decided a different approach.

The original table that I was using for writeback and reporting will be kept as it is. Now I am going to create another table with the same table structure. Then I will create a trigger that will insert records to this table when the original table gets a writeback update. Then last but not least, I will create a view which unions these 2 tables and use it in OBIEE rpd. Let me illustrate here:

The original table being used in the older post is FA_Table, which is being used in rpd.

Create a new table called FA_Table_AUX:

CREATE TABLE FA_TABLE_AUX
  (
LEDGER_NAME                   VARCHAR2(30),  
PERIOD_NAME                   VARCHAR2(15),  
JE_HEADER_NAME                VARCHAR2(100),
JE_LINE_NUM                   NUMBER,      
.
OTHER COLUMNS
.
.
)

Then create the trigger called FA_WRITEBACK:

CREATE OR REPLACE TRIGGER FA_WRITEBACK
BEFORE UPDATE
   ON FA_TABLE
   FOR EACH ROW
 
BEGIN
INSERT
INTO FA_TABLE_AUX
  (
    LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
  )
  VALUES
  (
    :old.LEDGER_NAME,
    :old.PERIOD_NAME,
    :old.JE_HEADER_NAME,
    :old.JE_LINE_NUM,
    :old.CURRENCY_CODE,
    :old.CURRENCY_CONVERSION_RATE,
    :old.CURRENCY_CONVERSION_TYPE,
    :old.CURRENCY_CONVERSION_DATE,
    :old.POSTED_DATE,
    :old.JE_BATCH_ID,
    :old.JE_CATEGORY,
    :old.JE_SOURCE,
    :old.ENTERED_DR,
    :old.ENTERED_CR,
    :old.ACCOUNTED_DR,
    :old.ACCOUNTED_CR,
    :old.EXCHANGE_RATE_USD,
    :old.TRANSACTION_LOCAL_AMOUNT,
    :old.TRANSACTION_USD_AMOUNT,
    :old.JOURNAL_LINE_DESCRIPTION,
    :old.EFFECTIVE_DATE,
    :old.COMPANY,
    :old.COST_CENTER,
    :old.COST_CENTER_NAME,
    :old.ACCOUNT,
    :old.ACCOUNT_NAME,
    :old.PRODUCT,
    :old.PROJECT,
    :old.PROJECT_NAME,
    :old.INTERCOMPANY,
    :old.GEOGRAPHY,
    :old.GEOGRAPHY_NAME,
    :old.FUTURE2,
    :old.INVOICE_NUM,
    :old.INVOICE_AMOUNT,
    :old.INVOICE_LINE_DESCRIPTION,
    :old.VENDOR_NAME,
    :old.INVOICE_DATE,
    :old.INVOICE_GL_DATE,
    :old.INVOICE_PERIOD_NAME,
    :old.DIST_GL_PERIOD_NAME,
    :old.PO_NUM,
    :old.RECORD_TYPE,
    :old.PERIOD_NUM,
    :old.ASSET_NUMBER,
    :old.GLOBAL_TRANSFER,
    :old.REGION,
    :old.INTERIM_CATEGORY,
    :old.CAPEX_CATEGORY,
    :old.CAPEX_YES_NO,
    :old.RECORD_ID,
    :old.REVISION_NUMBER,
    'N',
    'Trigger',
    SYSDATE,
    :old.RAW_DATA_FLAG,
    'N');

:new.REVISION_NUMBER := :old.REVISION_NUMBER+1;
END;

--------------------------

Here as you can see, the old records from FA_Table gets inserted into FA_Table_AUX, and the last 5 fields will be hard-coded. It also updates the revision_number by 1 increment just to indicate the update versions.

Now create a view that union both of these tables. The BOLD columns will be included in the report and it will reflect the historical changes because the columns in FB_TABLE_AUX will be inserted based on trigger.

create or replace view FA_TABLE_AUD_TRAIL
    (LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG)
    as
    select
    LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
from FA_TABLE
union
select
LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
    from
    FA_TABLE_AUX

Then in the rpd file, simply replace the physical layer table with FA_TABLE_AUD_TRAIL. No need to change the writeback.xml, because the writeback.xml is still using update FA_TABLE as implemented from the older post, When it is trying to update FA_TABLE with user inputs, the trigger will be invoked and therefore the same row before user updates will be inserted into FA_TABLE_AUX table while FA_TABLE will have the updated data.. Since we are reporting against the view which unions both table, then from the OBIEE dashboard, you will get to see both new and older records.

If you are in doubt, you can always query against FB_TABLE_AUX from the DB, if it is blank, that means no insertion occurs, if it returns 1 row, that means user has trigger the update..

Thank you

Until next time..