Deploy Write-back

Write-back enables users to update data from analyses.

Topics:

About Write-back for Administrators

Write-back enables users to update your data directly from dashboards and analyses.

Users with the Write Back to Database privilege see write-back fields as editable fields in analyses. The values they enter are saved to the database. Users without the Write Back to Database privilege, see write-back fields as read-only fields.

If a user types a value in an editable field and clicks the write-back button, then the application runs the insert or update SQL command defined in a write-back template. If the command succeeds, the analysis is updated with the new value. If there's an error either reading the template or running the SQL command, an error message is displayed.

The insert command runs when a record doesn't yet exist and the user enters new data into the table. In this case, the user typed in a table record where the original value was null. The update command runs when a user modifies existing data. To display a record that doesn't yet exist in the physical table, you can create another similar table. Use this similar table to display placeholder records that a user can modify.

Note:

When you create write-back templates, you must include both an insert command and an update command, even if they're not both used. For example, if you're only performing an insert, you must include an empty update statement <update></update>, as in this XML code:

Here's a sample write-back XML file that contains two insert commands and two empty update statements. To find out more about how to create and structure write-back XML files, see Create Write-Back Template Files.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="oracle.bi.presentation/writebackschemas/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="SetQuotaUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@{c5f6e60e1d6eb1098},@{c5d7e483445037d9e},'@{c3a93e65731210ed1}','@{c6b8735ea60ff3011}',@{c0432jkl53eb92cd8})</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="SetForecastUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypeforecast VALUES(@{c83ebf607f3cb8320},@{cb7e2046a0fba2204},'@{c5a93e65d31f10e0}','@{c5a93e65d31f10e0}',@{c7322jkl93ev92cd8})</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>

Enable Write-back in Analyses and Dashboards

Administrators can enable users to edit the data in analyses and dashboards.

Set up your semantic model.

Note:
Follow these steps if you use Model Administration Tool to develop semantic models. If you use Semantic Modeler, see
Enable Write Back On Columns .
1. In Model Administration Tool, open your semantic model (.rpd file).
2. In the Physical layer, double-click the physical table that contains the column for which you want to enable write-back.
3. On the General tab of the Physical Table dialog, ensure that Cacheable isn't selected. Deselecting this option ensures that Presentation Services users can see updates immediately.
4. In the Business Model and Mapping layer, double-click the corresponding logical column.
5. In the Logical Column dialog, select Writeable, then click OK.
6. In the Presentation layer, double-click the column that corresponds to the logical column for which you enabled write-back.
7. In the Presentation Column dialog, click Permissions.
8. Select the Read/Write permission for the appropriate users and application roles.
9. Save your changes.

Create an XML document with your write-back template (or templates). See Create Write-Back Template Files.

Your XML document can contain multiple templates. This example shows an XML document that contains two templates (SetQuotaUseID and SetForecastUseID).

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="oracle.bi.presentation/writebackschemas/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="SetQuotaUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@{c5f6e60e1d6eb1098},@{c5d7e483445037d9e},'@{c3a93e65731210ed1}','@{c6b8735ea60ff3011}',@{c0432jkl53eb92cd8})</insert>
            <update>UPDATE regiontypequota SET Dollars=@{c0432jkl53eb92cd8} WHERE YR=@{c5f6e60e1d6eb1098} AND Quarter=@{c5d7e483445037d9e} AND Region='@{c3a93e65731210ed1}' AND ItemType='@{c6b8735ea60ff3011}'</update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="SetForecastUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypeforecast VALUES(@{c83ebf607f3cb8320},@{cb7e2046a0fba2204},'@{c5a93e65d31f10e01}','@{c5a93e65d31f10e0}',@{c7322jkl93ev92cd8})</insert>
            <update>UPDATE regiontypeforecast SET Dollars=@{c7322jkl93ev92cd8} WHERE YR=@{c83ebf607f3cb8320} AND Quarter=@{cb7e2046a0fba2204} AND Region='@{c5a93e65d31f10e01}' AND ItemType='@{c5a93e65d31f10e0}'</update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>

Note: You must include an <insert> element and an <update> element, even if both aren't used. For example, if you're only performing an insert, you must include the empty update statement <update></update>.

Copy the XML document containing your write-back templates to the clipboard.
Apply your write-back template in Oracle Analytics:
1. Click Console, then click System Settings.
2. In Writeback Template XML, paste the write-back template that you copied in Step 3.
Grant permissions to use the write-back code:
1. Navigate to Classic home, then click Administration.
2. Under Security, click Manage Privileges, and navigate to Write Back.
3. Grant Write Back to Database to Authenticated User.
4. Grant Manage Write Back to BI Service Administrator.
To enable write-back in columns:
1. In the analysis editor, display the Column Properties of the column on which you want to enable write-back.
2. In the Column Properties dialog, click the Write Back tab.
  If the column has been enabled for write-back in the semantic model, then the Enable Write Back box is available.
3. Select the Enable Write Back option.
4. Specify the value of other options if you want to change the default.
5. Save your changes.
The column is enabled for write-back in any analysis that includes this column.
To enable write-back in table views:
1. In the analysis editor, open the table view for editing.
2. Click View Properties.
3. In the Table Properties dialog, click the Write Back tab.
4. Select the Enable Write Back option.
5. Select the Template Name box, specify the value of "WebMessage name=" in the write-back template that you specified in Step 2.
  For example, the Template Name for the example template in Step 2 is 'SetQuotaUseID'.
6. Save your changes.

Write-Back Limitations

Users can write back to any data source that allows the execution of SQL queries from Oracle Analytics .

As you configure for write back, keep the following limitations in mind:

Numeric columns must contain numbers only. They mustn't contain any data formatting characters such as dollar signs ($), pound signs or hash signs (#), percent signs (%), and so on.
Text columns must 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 isn't 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 isn't 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 might cause problems.
Any field in an analysis can be flagged as a write-back field, even if it's not derived from the write-back table that you created. However you can't successfully run the write-back operation if the table isn't 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 doesn't support or recommend the use of any statements other than insert or update.
Oracle Analytics performs only minimal validation of data input. If the field is numeric and the user enters text data, then Oracle Analytics detects that and prevents the invalid data from going to the database. However, it doesn't 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 run, 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 isn't allowed."
The template mechanism isn't suitable for entering arbitrary new records. In other words, don't use it as a data input tool.
When creating a table for write back, ensure that at least one column doesn't include write-back capability but does include values that are unique for each row and are non-null.
Write-back analyses don't support drill-down. Because drilling down modifies the table structure, the write-back template doesn't 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.

Create Write-Back Template Files

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've 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.

Requirements for a Write-Back Template

A write-back template must meet the following requirements:

WebMessage: You must specify a name for the write-back template using the name attribute in 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.

This example shows a write-back template called SetQuotaUseID.
```
<WebMessage name="SetQuotaUseID">
```
connectionPool: 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: Column values can be referenced either by column ID or column position. The use of column ID is preferred.

Surround string and date values with single quotes. Single quotes aren't required on numerical values.
- Column ID - Each column ID is alphanumeric and randomly generated. You can find column IDs in the XML definition of the analysis that's available in the Advanced tab of the analysis editor. For example, column ID values such as: @{c5f6e60e1d6eb1098}, @{c3a93e65731210ed1}, '@{c6b8735ea60ff3011}'
  
  When you use column IDs, write-back continues to work even when the order of columns change.
- Column position - Column positions start numbering with 1. For example, column position values such as: @1, @3, '@5'
  
  If the order of columns changes, write back no longer works and this is the reason why column IDs are preferred.
You must include both an <insert> and an <update> element in the template. If you don't 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 can't read the Write Back Template 'my_template'".
If a parameter's data type isn't an integer or real number, then add single quotation marks around it. If the database doesn't 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 Write-Back Template File Using Column ID Syntax

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(@{c5f6e60e1d6eb1098},@{c5d7e483445037d9e},'@{c3a93e65731210ed1}','@{c6b8735ea60ff3011}',@{c0432jkl53eb92cd8})</insert>
            <update>UPDATE regiontypequota SET Dollars=@{c0432jkl53eb92cd8} WHERE YR=@{c5f6e60e1d6eb1098} AND Quarter=@{c5d7e483445037d9e} AND Region='@{c3a93e65731210ed1}' AND ItemType='@{c6b8735ea60ff3011}'</update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>

Example Write-Back Template File Using Column Position Syntax

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>