Augment Data in Oracle Fusion Analytics using the Oracle Analytics Publisher Connector

December 19, 2023 | 8 minute read
Krithika Raghavan
Director, Oracle Analytics
Text Size 100%:

Overview

Oracle Fusion Analytics (Fusion Analytics), a part of the Oracle Fusion Data Intelligence Platform, is a family of prebuilt, cloud-native analytics applications for Oracle Fusion Cloud Applications that provide ready-to-use insights to help improve decision-making. It is extensible and customizable, allowing customers to ingest data and expand the base semantic model with additional content.

Oracle Analytics Publisher (formerly Oracle Business Intelligence Publisher) is a pixel-perfect reporting solution used to author, manage, and deliver published reports and documents. It is available as a stand-alone application and in many Oracle Analytics services.

A frequently requested feature and common use case is augmenting Fusion Analytics using an Analytics Publisher report to bring additional attributes into Fusion Analytics for further analysis. Customers may have existing Analytics Publisher reports containing attributes that are not part of Fusion Analytics pre-built pipelines.

With the 23.R3 release of Fusion Analytics, the Oracle Analytics Publisher Connector is now a self-service preview feature. Other alternatives, like SFTP connectors, are no longer required.

Note: The connector only supports the Analytics Publisher in Oracle Fusion Cloud Applications for data augmentation.

This post demonstrates augmenting Fusion Analytics using an Analytics Publisher report.

Please note a few restrictions in the file format.

  • Only CSV (Comma-Separated Values) output formats are supported for datasets.
  • Report Configuration (a required field in the connection specification) is in JSON format.

Steps

The following steps are required to augment Fusion Analytics using an Analytics Publisher report.

  1. Confirm that the Analytics Publisher report has the CSV output format.
  2. Enable the Analytics Publisher connector in Fusion Analytics.
  3. Create an Analytics Publisher connection in Fusion Analytics.
    • Review the Report Path.
    • Review the Report Configuration.
  4. Test the Analytics Publisher connector in Fusion Analytics.
  5. Run the Metadata Extraction process.
  6. Load data into Fusion Analytics through data augmentation.

1. Confirm the Analytics Publisher Output Format

Follow these steps :

Log in to Analytics Publisher and navigate to Create > Data Model.

New Data Model

From the Diagram dropdown, select SQL Query.

Select SQL Query

Use the Query Builder to select the required table and columns and save the data model.

Use Query Builder

Enter a SQL Query to create a New Data Set and click OK.

New Data Set

Click Properties and select Enable CSV Output.

Enable CSV Output

Click Data and then click Save as Sample Data.

Save as Sample Data

Click Create Report and follow the “guide me” to create the report.

Click Edit Report, View as List, and select Data (CSV) as the output format.

View as List

Save the report.


2. Enable the Analytics Publisher Connector

In the Fusion Analytics Console, navigate to the Enable Features page, click Preview Features,and enable the Oracle BI Publisher connector.

Enable Features


3. Create an Analytics Publisher Connection

Follow the detailed instructions in Loading Data from Analytics Publisher and create a connection.

Create BIP Connection


Review the Report Path

List of Reports

  • Provide a list of comma-separated report paths that generate CSV output.
  • The report path format is <ReportDirectory>/<ReportName>.xdo
  • Examples: /lisa.jones/Department_Report.xdo, /lisa.jones /User_Report.xdo

Review the Report Configuration

This is optional. Provide the parameter default values in the report configuration.

Notes:
  • If parameters are not provided, and the report has a param name defined as lastUpdateDate, Fusion Analytics sets the value of __lastUpdateDate__ as above.
  • Ensure that “runInChunkMode” is ‘false’ by default.

 

JSON Parameter Value Comments Example
reportPath Report path The path of the report provided in the List of Reports "reportPath": "/lisa.jones/User_Report.xdo"
runInChunkMode true/false If the report content is large, set this parameter to true. "runInChunkMode": true
params Name/value array of parameters Provide the name and value of each parameter in the report.
Values can be static or a predefined value template.
Fusion Analytics replaces a value like __lastUpdateDate__
with the actual Initial Extract Date or Last Update Date value.

Static value:
"params": [{"name": "location", "value": "S1- Chicago"}]

Predefined Value Template:
"params": [ { "name": "lastUpdateDate", "value": "__lastUpdateDate__" }]

    An example for the three parameters for two reports [
{  "reportPath": "/lisa.jones/User_Report.xdo",  "runInChunkMode": true, 
"params": [   { "name": "lastUpdateDate", "value": "__lastUpdateDate__"   }  ] },
{  "reportPath": "/lisa.jones/Department_Report.xdo",  "runInChunkMode": false,  "params": [   { "name": "location", "value": "S1- Chicago"   }  ] }
]

4. Test the Analytics Publisher Connector in Fusion Analytics

Navigate to Data Configuration > Manage Connections. Select Test Connection from the elipse drop-down. This validates each report and that the report URL is accessible.

Test Connection

Navigate to Data Configuration > Request History. Ensure the Test Connection request completed successfully.

REQUEST HISTORY


5. Run the Metadata Extraction Process

Navigate to Data Configuration > Manage Connections. Select Refresh Metadata from the elipse drop-down.  The data store, column list, and column data types are fetched. The primary key is chosen in data augmentation.

Refresh Metadata

Navigate to Data Configuration > Request History. Ensure the Metadata Extract request completed successfully.

Metadata Refresh Request History


Analytics Publisher Data Type to Oracle Data Type Mapping

Analytics Publisher
Data Type
Oracle
Data Type
Size Precision Scale
xsd:string VARCHAR2 4000 0 0
xsd:date TIMESTAMP 0 0 0
xsd:integer NUMBER 0 38 12
xsd:decimal NUMBER 0 38 12
xsd:boolean BOOLEAN 5 0 0

6. Load Data into Fusion Analytics through Data Augmentation

Navigate to Data Configuration. Select Augmentation from the Create dropdown. Manage Connections. When augmenting data, a Source Table is displayed for each report listed. The following example lists one report.

DATA AUGEMENTATION

When picking the report and proceeding in the wizard, all the columns are visible, and up to 100 can be selected.

DA ATTRIBUTES

Specify the primary key information, as Analytics Publisher does not provide it. Column names and data types are obtained.

DA SCHEDULE


Call to Action

Once the Analytics Publisher connector feature is successfully configured, Fusion Analytics can be augmented using data from Analytics Publisher reports. Once tested on the development Fusion Analytics instance, Oracle suggests performing the same steps to enable the feature on the production Fusion Analytics instance.

Refer to Managing Data Connections for more details on Fusion Analytics Data Connnectors.

Schedule a meeting today to talk with the Oracle Analytics product team and learn more about deploying Oracle Fusion Analytics.

Krithika Raghavan

Director, Oracle Analytics

Dayne Carley


Previous Post

Oracle Hyperion Enterprise Performance Management System Release Update 11.2.15 Support for Application and Artifact Migration

Tanya Heise | 1 min read

Next Post


Extract key values with Oracle Analytics and OCI Document Understanding

Benjamin Arnulf | 5 min read