OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.
1. The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.
2. The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.
3. Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.
4. Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.
5. Once this is done, go to the column properties of sales_quota and enable writeback.
6. After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is
The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.
7. Go to the table properties of the report and click on the write back option. Enter in the template details.
8. Once this is done, enter in some data and test out the write back.
More details can be obtained from the BI Server and Presentation Services guides.
Posts
