writeback –> ability to modify data that is displayed in an analysis in oracle answers. very crude way of putting it. Please refer to the OBIEE docs from Oracle for the limitations.
Interestingly, this contradicts the basic concept of an OLAP system, which is primarily meant for fetching data rather than inserting data. However there are some special cases, where you would want this functionality. There are certain design considerations too that are involved with the data warehouse, which we will discuss in another post.
I’m creating a demo table for this purpose
create table JT_DEMO_WRITEBACK
(
CUST_KEY NUMBER PRIMARY KEY,
CUST_NAME VARCHAR2(255) NOT NULL,
CUST_RATING NUMBER NOT NULL
);
lets insert some data to look like this:
Login to Oracle Answers. got to Administration page. Under security click the “Manage Privileges” link
change the “Write Back to Database” privilege
start the BI Admin Tool and open your rpd. In the physical layer, create a new connection pool called “WB Connection Pool”. Import this table. create an alias for this table.
disable caching on your physical table that you want to write back to.
create a new Business Model to look like this
we want the Customer Rating field to be editable(writable) in our Answers page.
Create the new subject area in the presentation layer as shown below. for simplicity purpose, I’m setting an implicit fact for the WB Test Subject area.
set the permission to read write for the relevant users
save your rpd and check for consistency.
Enable writeback in the configuration file by Modifying the instanceconfig.xml file located in $(ORACLE_INSTANCE)\config\OracleBIPresentationServicesComponent\coreapplication_obips1
add the following to the instanceconfig.xml file
<WebConfig xmlns="oracle.bi.presentation.services/config/v1.1">
<ServerInstance>
<!-- added by JT -->
<LightWriteback>true</LightWriteback>
</ServerInstance>
</WebConfig>
Deploy your rpd and restart the BI services.
create a new Analysis as shown
go to the Advanced tab and note the column ids in the xml code. we will use these ids in our xml template to specify the SQL to update the customer rating field.
In the criteria tab, mark the customer rating column writable ( this option is enabled in Answers if you enabled the writable flag for the column in the rpd).
go to results tab –> edit view (pencil icon) –> Table view properties (the xyz icon) and enable writeback option and specify the template name
create your template file which contains the SQL code for the update.
<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
<WebMessage name="wb_cusRating">
<XML>
<writeBack connectionPool="WB Connection Pool">
<insert> </insert>
<update>
UPDATE BISAMPLE.JT_DEMO_WRITEBACK SET CUST_RATING=@{c8f6dc321eca7766c} WHERE CUST_KEY=@{c6657b10f6946b91b}
</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
save the above text to a new file called writebacktemplateJT.xml put it in the location $(ORACLE_INSTANCE)\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages
note:
- you may want to create customMessages directory if it does not exist.
- the line <WebMessage name="wb_cusRating"> specifies the template name.
- save your analysis to a folder as “WB Test”.
Restart OPMN services. login to Answers page with the user, whom you have given the necessary rights. open the “WB Test” analysis
notice that the Cust Rating field is editable, change the value and click on the update button. OBIEE updates the data to the physical database and refreshes the page with the new values.
note: you might get the writeback error in red as shown below
There seems to be a bug with the write back feature in OBIEE 11g. A patch
is currently available only for 64 bit Win systems. Bug 13067844, Patch
13067844. After installing the patch, the writeback works properly.
