Basic Implementation For Write Back
One of the interesting attributes that OBIEE provides is the facility of providing the users to add/update the data back to the database. The user could have a column for which the values can be entered in the user interface (UI) part of their platform and it can be updated in database. This could be useful in more than one ways as the end users may want to rank their clients, or rate their regional business based on their performances and use them from time to time. This makes OBIEE a reporting tool and also as a mini application for modifying business data.
Implementing write back requires configuring multiple objects within the architecture i.e. Database, Connection Pool, Presentation, BMM and Physical Layers, UI privileges, Column/Table properties etc.
Let use see how this can be done-
We have created a new table in the database “Write Back” and added a column “WBVAL”. Later mapped it to another table within the present schema i.e. customers table and provided a foreign key relation by adding a customer key column to the write back table as shown below.
If it is an Oracle database you are using, then no need to make any modifications in your connection pool’s 4th tab i.e. write back. Leave it as it is. Once it is done import the new table to your physical layer and create direct and logical joins between the two tables as shown below. For achieving optimum performance set your physical layer table properties to non-cashable, by un-checking the ‘Cashable’ check box for both the tables which include your columns.
After creating the joins, drag both the columns to you BMM layer and Presentation layer. Check for consistency and save the changes. Start the BI server and log in to your presentation services.
Once you log into your presentation services, Go to your ‘Settings’ tab and select ‘Administration’. It will open an ‘Oracle BI Presentation Services Administration’ window. Select ‘Manager Privileges’ from the activities section; a ‘Privilege Administration’ window should open. At the bottom you will find a ‘Write Back’ component of the OBIEE. Grant permissions for activating this feature to the administrator or the desired users. As shown below.
Once the above steps have completed successfully navigate to your Answers section in your presentation services and create your request as shown below.
From the selected columns click on the column properties tab of the column to which the Write back is to be done and select the write back option for that particular column as shown below. Do mention the field length which gets popped below the option.
Once you have enabled the write to the column navigate to the table design section of the request or go to the table view and select edit table properties tab. You will find a write back icon as marked in red circle below. Click on the icon and the Write Back window pops open as shown below.
Select the ‘Enable Write Back’ check box. In the space given for the ‘Template name’ mention the name written for your web message name tag in your xml template. ‘<webMessage name=”supplier2wb”>’
In the ‘Button Text’ specify any text you wish to display for the write back process to be triggered. Save your changes and exit.
Once the above changes are done at the column and the table level successfully. Browse to the DriveLetter:OracleBIwebmsgdbcustomMessages section and save your xml template for it to be sued by the request at runtime. This xml template of your will hold the SQL you need to insert or update to your database. See the below screenshot for the path and the xml template. The tag of webMessage name is high lightened in the below image which has an entry of ‘supplier2wb’ which is the name to be specified at the Template name section when you are enabling the Write Back option at the table view. The name of the xml file has no relation while being accessed by the server.
Once the XML template is saved the desired location and the template has the logic correctly mentioned without any errors proceed to your answers section. Select the earlier saved request and select the results tab to view the request in your Compound Layout, it should be displayed as shown below; with the edit box field available for each row, and the Button text at the bottom.
Insert the values the user wishes to write back to the database and click on the ‘Insert’ tab. The values entered by the user should get updated to the data base table. Browse to your database using sqlplus and view the updates as shown below.
By following this simple method, write back can be activated on your OBIEE. The entire logic of what needs to be written and how is supposed to be mentioned in your XML template you use.
I hope this helps all of you. In case you have any questions or feedback, don’t hesitate to contact me at firstname.lastname@example.org
Know more about our OBIEE Services