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. If a user doesn't have the 'Write Back to Database' privilege, then the write-back fields display as normal 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 is an error in either reading the template or in running the SQL command, then 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, 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 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 an insert command and an update command, even if both are not used. For example, if you're only performing an insert, you must include an empty update statement <update></update>, as in this XML code:

<?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(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>
            <update></update>
         </writeBack>
      </XML>
   </WebMessage>
<WebMessage name="SetForecastUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypeforecast VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</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.

  1. Set up your semantic model:
    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.
  2. Create an XML document with your write-back template (or templates).
    The XML document can contain multiple appended templates. This example shows an XML document containing two templates:
    <?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(@{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>
    <WebMessage name="SetForecastUseID">
          <XML>
             <writeBack connectionPool="Supplier">
                <insert>INSERT INTO regiontypeforecast VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>
                <update>UPDATE regiontypeforecast SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>
             </writeBack>
          </XML>
       </WebMessage>
    </WebMessageTable>
    </WebMessageTables>
    Note: You must include an <insert> element and an <update> element, even if both are not used. For example, if you're only performing an insert, you must include an empty update statement <update></update>.
  3. Copy the write-back template to your clipboard.
  4. Apply your write-back template in Oracle Analytics:
    1. Click Console, then click System Settings.
    2. In Writeback Template XML, paste in the write-back template that you copied in Step 2.
  5. 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.
  6. 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.
  7. 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.