Users of a dashboard page or an analysis might have the ability to modify the data that they see in a table view.
This ability is often referred to as "write back." As the administrator, you assist the content designer in configuring write back for users.
The following sections provide information about how to configure for write back:
If a user has the Write Back to Database privilege, then the write-back fields in analyses can display as editable fields if properly configured.
If the user does not have this privilege, then the write-back fields display as normal fields. If the user types a value in an editable field and clicks the appropriate write-back button, then the application reads the write-back template to get the appropriate insert or update SQL command. It then issues the insert or update command. If the command succeeds, then it reads the record and updates the analysis. If there is an error in either reading the template or in executing the SQL command, then an error message is displayed.
The insert command runs when a record does not yet exist and the user enters new data into the table. In this case, a user has typed in a table record whose value was originally null.
The update command runs when a user modifies existing data. To display a record that does not yet exist in the physical table to which a user is writing back, you can create another similar table. Use this similar table to display placeholder records that a user can modify in dashboards.
Note:
If a logged-on user is already viewing a dashboard that contains an analysis where data has been modified using write back, the data is not automatically refreshed in the dashboard. To see the updated data, the user must manually refresh the dashboard.
Refer to the following example which demonstrates how the Oracle BI Administrator works with the content designer to configure write back processes.
Users can write back to any data source (except for an ADF data source) that allows the execution of SQL queries from the Oracle BI Server. As you configure for write back, keep the following limitations in mind:
Numeric columns must contain numbers only. They should not contain any data formatting characters such as dollar signs ($), pound signs or hash signs (#), percent signs (%), and so on.
Text columns should contain string data only.
If a logged-on user is already viewing a dashboard that contains an analysis where data has been modified using write back, the data is not automatically refreshed in the dashboard. To see the updated data, the user must manually refresh the dashboard.
You can use the template mechanism only with table views and only for single-value data. The template mechanism is not supported for pivot table views or any other type of view, for multiple-value data, or for drop down columns with single-value data.
All values in write-back columns are editable. When displayed in non printer friendly context, editable fields are displayed as if the user has the Write Back to Database privilege. However, when a logical column is mapped to a physical column that can change, the logical column returns values for multiple level intersections. This scenario can cause problems.
Any field in an analysis can be flagged as a write-back field, even if it is not derived from the write-back table that you created. However you cannot successfully execute the write-back operation if the table is not write-back enabled. The responsibility for correctly tagging fields lies with the content designer.
A template can contain SQL statements other than insert and update. The write-back function passes these statements to the database. However, Oracle does not support or recommend the use of any statements other than insert or update.
Presentation Services performs only minimal validation of data input. If the field is numeric and the user enters text data, then Presentation Services detects that and prevents the invalid data from going to the database. However, it does not detect other forms of invalid data input (values out of range, mixed text and numeric, and so on). When the user clicks the write-back button and an insert or update is executed, invalid data results in an error message from the database. The user can then correct the faulty input. Content designers can include text in the write-back analysis to aid the user, for example, "Entering mixed alphanumeric values into a numeric data field is not allowed."
The template mechanism is not suitable for entering arbitrary new records. In other words, do not use it as a data input tool.
When creating a table for write back, ensure that at least one column does not include write-back capability but does include values that are unique for each row and are non-null.
Write-back analyses do not support drill-down. Because drilling down modifies the table structure, the write-back template does not work.
Caution:
The template mechanism takes user input and writes it directly to the database. The security of the physical database is your own responsibility. For optimum security, store write-back database tables in a unique database instance.A write-back template file is an XML-formatted file that contains one or more write-back templates.
A write-back template consists of a WebMessage element that specifies the name of the template, the connection pool, and the SQL statements that are needed to insert and update records in the write-back tables and columns that you have created. When content designers enable a table view for write back, they must specify the name of the write-back template to use to insert and update the records in the table view.
You can create multiple write-back template files. You can include multiple write-back templates in a template file, customizing each one for the fields that are used in each specific analysis. However, the best practice recommendation is to include only one template in a file.
A write-back template must meet the following requirements:
You must specify a name for the write-back template using the name attribute of the WebMessage element.
For write back to work correctly, when enabling a table view for write back, a content designer must specify the name of the write-back template to be used to insert and update the records in the view.
The following example shows the specification of the write-back template that is called "SetQuotaUseID."
<WebMessage name="SetQuotaUseID">
To meet security requirements, you must specify the connection pool along with the SQL commands to insert and update records. These SQL commands reference the values that are passed in the write-back schema to generate the SQL statements to modify the database table. Values can be referenced either by column position (such as @1, @3) or by column ID (such as @{c1234abc}, @{c687dfg}). Column positions start numbering with 1. The use of column ID is preferred. Each column ID is alphanumeric, randomly generated, and found in the XML definition of the analysis in the Advanced tab of the Analysis editor.
You must include both an <insert> and an <update> element in the template. If you do not want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. For example, you must enter the element as
<insert> </insert>
rather than
<insert></insert>
If you omit the blank space, then you see a write-back error message such as "The system cannot read the Write Back Template 'my_template'".
If a parameter's data type is not an integer or real number, then add single quotation marks around it. If the database does not do Commits automatically, then add the optional postUpdate node after the insert and update nodes to force the commit. The postUpdate node typically follows this example:
<postUpdate>COMMIT</postUpdate>
Example 17-1 Examples: Write-Back Template Files
A write-back template file that references values by column ID might resemble this example:
<?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="SetQuotaUseID">
<XML>
<writeBack connectionPool="Supplier">
<insert>INSERT INTO regiontypequota VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>
<update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
A write-back template file that references values by column position might resemble this example:
<?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="SetQuota">
<XML>
<writeBack connectionPool="Supplier">
<insert>INSERT INTO regiontypequota VALUES(@1,@2,'@3','@4',@5)</insert>
<update>UPDATE regiontypequota SET Dollars=@5 WHERE YR=@1 AND Quarter=@2 AND Region='@3' AND ItemType='@4'</update>
</writeBack>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>