How to Configure Write Back in OBIEE Report?

Follow the steps, screenshots and other referenced posts to configure write back in OBIEE reports.

Table Properties

First, you should choose a report in Answers to be able to writing back to the database.

1. Go to the Table Component
2. Click on the write back properties icon.Remark that each column have on this head a letter C and a number (Ex. c1, C2, … )

1. Fill the template name with for example:SetWriteBackValue. (This name will indicate later the statement to update or insert in the database)

 

Column Properties

1. Set to “write back” the value integration type of the column you want to be able to update.

Also refer the below mentioned topics to get more knowledge on Write Back.

• Write Back inOBIEE
• How Write BackWorks in OBIEE
• Write-BackTemplate Example in OBIEE
• Write-BackLimitations in OBIEE

Using Merge Option While Loading Data

While Loading from Source to Target its not necessary that all the records are fresh inserts. For example when we load the data for the transactions table there will be both insert as well as Updates. There will both new transactions as well as old transactions to be updated. If ETL tool is Informatica, we can use Update strategy for implementing both Inserts and Updates. The same we can implement using MERGE SQL statement. Now we will see how we can use MERGE for Update and Insert Process:

MERGE INTO Customer USING new_Customer

ON (Customer.customer_id = new_Customer.customer_id)

WHEN MATCHED THEN

UPDATE SET Customer.name = new_Customer.name, Customer.loc = new_Customer.loc

WHEN NOT MATCHED THEN INSERT (name,loc)

VALUES (new_Customer.name, new_Customer.loc);

Insert will insert the entire record from new Customer table into the actual customer table. Update will update the columns loc and name for all those record which already exists in the customer table.

In cases where we don’t want insert to happen in the target table and only updates then in such case we can omit the Insert clause from the MERGE statement. And in cases where we need only insert to happen then we can omit the Update clause.