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.
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.
- Confirm that the Analytics Publisher report has the CSV output format.
- Enable the Analytics Publisher connector in Fusion Analytics.
- Create an Analytics Publisher connection in Fusion Analytics.
- Review the Report Path.
- Review the Report Configuration.
- Test the Analytics Publisher connector in Fusion Analytics.
- Run the Metadata Extraction process.
- 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.
From the Diagram dropdown, select SQL Query.
Use the Query Builder to select the required table and columns and save the data model.
Enter a SQL Query to create a New Data Set and click OK.
Click Properties and select Enable CSV Output.
Click Data and then click 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.
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.
3. Create an Analytics Publisher Connection
Follow the detailed instructions in Loading Data from Analytics Publisher and create a 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.
- 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: Predefined Value Template: |
| 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.
Navigate to Data Configuration > Request History. Ensure the Test Connection request completed successfully.
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.
Navigate to Data Configuration > Request History. Ensure the Metadata Extract request completed successfully.
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.
When picking the report and proceeding in the wizard, all the columns are visible, and up to 100 can be selected.
Specify the primary key information, as Analytics Publisher does not provide it. Column names and data types are obtained.
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.