Enable Write Back in Oracle Fusion AI Data Platform (formerly known as Oracle Fusion Data Intelligence)

With inputs from:

Jason Hall, Principal Solutions Architect, Oracle Analytics

Introduction

This article explains how to enable write back in Oracle Fusion AI Data Platform using the Semantic Model Extensions framework and Oracle Autonomous AI Lakehouse (formerly known as Oracle Autonomous Data Warehouse). Using a “Team Building Survey” example, users can submit their activity choice and comments, with only their latest response displayed on the dashboard.

Create database objects

1. While creating a custom database schema is highly recommended, we have used OAX_USER in these examples for simplicity. See Create a Custom Database Schema.
2. Migrate or Create Database Objects (including the required tables and views) to your Oracle Autonomous AI Lakehouse schema.
3. Grant semantic model access. See Grant Semantic Model Access.
4. Create the write back table and grant access: Sample Write-Back-Table-Script.txt.

Configure the semantic model

1. Configure Oracle Analytics Client Tools. See Configure Oracle Analytics Client Tools – Oracle BI Administration Tool.
2. Export the semantic model template or download the imported semantic model if you already applied external application customizations. See Export the Semantic Model Template or Download the Imported Semantic Model. 
3. Use the Oracle Analytics Client Tool to open the external application semantic model.

4. Temporarily set the connection pool to point to the Oracle Fusion AI Data Platform’s Oracle Autonomous AI Lakehouse. Ensure the Data Source Name uses _low.adb.oraclecloud.com. See Bring in Custom Database Objects.

5. Import the WRITE_BACK_ANNOTATIONS table into the physical layer. Create and import a dummy table WB_JOIN_TEMP, if required.

6. Join to the dummy table in both the physical layer and Business Model and Mapping.

8. Disable caching for the table.

9. Validate your model using the global consistency check.
   

2. Review Merge External Application for complete steps on merging external applications.

Import a new semantic model version for merging only if it’s missing changes from the current version; importing multiple versions may cause delays due to extra validation and merging. If your updated semantic model includes all prior changes, replace the model—by reusing the existing application name or replacing the application—to avoid creating unnecessary versions.

• Navigate to the folder that contains the external application with the newly imported write back table.
• Add a prefix and click Validate.

Oracle recommends you use the same external application name in the description field to replace the application (if it includes all previous changes).

3. Merge the external application.

Build the analysis

1. Add all columns (and rename, if needed) from WRITE_BACK_ANNOTATIONS:
   1. User: USER()
   2. Selected activity as a Dashboard Prompt Presentation Variable:  ‘@{p_activities}’
   3. Most Recent Entry Date per User: MAX(“WRITE_BACK_ANNOTATIONS”.”WRITEBACK_DATE” BY “WRITE_BACK_ANNOTATIONS”.”OAC_LOGIN”)

2. Enable write back on WRITEBACK_COMMENTS column in Column Properties (Write Back tab).

3. Filter results to display only the latest entry per user: MAX(“WRITE_BACK_ANNOTATIONS”.”WRITEBACK_DATE” BY WRITE_BACK_ANNOTATIONS”.”OAC_LOGIN”) = “WRITE_BACK_ANNOTATIONS”.”WRITEBACK_DATE”.

4. Create two table views on the Results page:
   1. Table 1: For user feedback and write back; add USER(), p_activity, and WRITEBACK_COMMENTS  (hide USER() and p_activity; exclude other columns).
   2. Table 2: To display inserted or updated results.

5. Enable Write back on Table 1: In View Properties (from the Results tab), go to the Write Back tab, check Enable Write Back, enter a template name, and uncheck Toggle Table Mode.

6. Table 2: Add OAC_LOGIN, FAVORITE_ACTIVITY, and REASON_FOR_CHOICE; hide USER(), p_activity, and WRITEBACK_COMMENTS; exclude all other columns.

7. Save the Analysis.

Create a dashboard prompt

• Create a dashboard variable prompt named p_activities (hide label) using radio buttons with custom values shown below; the selected value is stored in p_activities for use in analysis and write back.
  • AXE THROWING
  • ESCAPE ROOM
  • ROCK CLIMBING
  • TOP GOLF

Build a dashboard

• Add the dashboard prompt and analysis to the dashboard.
• Set Prompt Scope to Page and enable the Refresh report link to avoid caching issues.

Build and test INSERT/UPDATE statements

• In this example, each new submission inserts a row; only the most recent entry per user appears on the dashboard. Test the following statements before adding them to your write back template: INSERT_UPDATE Write-Back TemplateDownload

Update the write back template

1. Update the WebMessage name to match the name used in the Analysis Table Properties Write Back tab (e.g., TeamMeetingActivity).
2. Set the writeBack connectionPool to the appropriate write back connection pool defined in the physical layer of the RPD.
3. Replace the and statements with the tested Insert and Update SQL statements.
4. In the template, use variable columnIDs (not hard-coded strings); copy these from the Advanced tab in the Analysis (Analysis XML).

If you receive the following error, it’s most likely related to needing to reference the columns by columnID.

Write Back Error – The system was unable to generate appropriate SQL. Please contact your service administrator.

5. Navigate to Service Administration, System Settings, and then Analytic Content to copy the write back template. Here’s a Sample Write Back Template.

 Test the write back dashboard

• Add a new record and update an existing one.
• Confirm that results refresh automatically after clicking Apply, without needing to manually refresh the page.

Set write back permissions

Navigate to Classic Home, Manage Privileges, and then Write Back. Write back access can be controlled though granting or removing roles and users.

Summary

This article explained how to enable the write back feature using the external applications feature in Oracle Fusion AI Data Platform’s semantic model framework.

References

For additional information, see the following documentation and blog articles:

• Migrate to the Sandbox Framework for Semantic Model Extensions
• About Semantic Model Customization to learn more about extending the semantic layer using the Sandbox framework
• Use the Enhanced Semantic Model Extension Capabilities
• About Merging External Applications
• Working with the Developer Client Tool for Oracle Analytics Cloud (OAC) with ADW in a Proxy-enabled Network
• Oracle Analytics Cloud (OAC) – Using Write back in Analyses and Dashboards

Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas.

Contribution

Many thanks to Padma Rao Padala and Suzanne Gill for reviewing and all the useful feedback!