How to Inject external data into Oracle Autonomous lakehouse via SFTP connector?

Question

How to Inject external data (Business central data) into Oracle Autonomous lakehouse via SFTP connector in FDI?

Bhaskar Konar · Answer

Hi @User_X8FWT,

Thank you for providing the additional details.

Based on your requirement, Dataset-type Data Augmentation followed by Semantic Model Extensions (SME) appears to be a suitable approach for creating an independent subject area for external data in FDI. Oracle provides a documented use case that demonstrates this approach using Google Analytics data.

I would suggest reviewing the following documentation and following the steps outlined below.

1. Data ingestion and storage

Use the SFTP connector to ingest the Business Central data files into FDI through Dataset-type Data Augmentation.

* Load Data from an SFTP Source into Fusion Data Intelligence (Preview)
* Create Dataset Augmentation Type

The Dataset augmentation copies the source data into a target table in the FDI analytics warehouse. After the initial incremental load has completed, you can use the resulting table in your semantic model extensions.

You can validate the loaded data by querying the target table in the associated warehouse, subject to the required database access permissions.

2. Data modelling and semantic model creation

Once the data has been loaded, use SME to define the logical model for your external dataset. This includes creating a custom fact, defining dimensions and their keys, establishing relationships and joins and specifying the appropriate measures and aggregation rules.

Oracle provides a step-by-step example here:

* Use Case 9: Create a Subject Area

This example uses external Google Analytics data to demonstrate how to create a custom fact, add a custom dimension, define the join between them, and expose the resulting model through a new subject area.

3. Creating an independent subject area

The same Oracle use case demonstrates creating a new subject area rather than extending an existing Fusion subject area.

You can follow the example to create a separate subject area for Business Central, selecting the relevant custom fact, dimensions, hierarchies and measures. The subject area can then be used to build reports and workbooks independently of the standard Fusion Subject Areas.

4. Publishing and reporting

After completing the modelling, apply and validate the customizations, publish the semantic model and verify that the new subject area is available when creating a workbook in Oracle Analytics.

The Use Case 9 documentation covers these steps, including validating the resulting report.

Overall, I would recommend starting with Oracle's Use Case 9: Create a Subject Area and adapting its sample fact-and-dimension model to your Business Central data. It provides a practical reference for the independent-subject-area requirement you described.

I hope this help.

Thank you.

User_X8FWT · Answer

We are trying to bring non-Fusion data from an external source into Oracle Fusion Data Intelligence (FDI). We are planning to ingest the data using the SFTP connector and use it for analytical reporting.

The requirement is to create a completely independent subject area based on this external data, without necessarily extending or combining it with existing Fusion ERP subject areas.

I would appreciate your guidance on the recommended approach for the following:

* Data ingestion and storage: After loading the data through SFTP using Dataset-type Data Augmentation, where is the data stored in the FDI environment, and how can we validate and access it for further modelling?
* Data modelling: What is the recommended approach for defining the required tables, relationships, primary keys, dimensions and measures for this external data?
* Semantic model creation: Can we use Semantic Model Extensions (SME) to create the required semantic structure for this independent dataset? If so, what are the steps and limitations?
* Independent subject area: Is it possible to expose this external dataset as a separate subject area for reporting in FDI? If so, what is the supported approach?
* Reporting and best practices: What is the recommended end-to-end architecture for making this data available for analytical reporting and dashboards?

My current understanding is that Dataset-type Data Augmentation can be used to ingest the external data, followed by semantic modelling using SME. However, I would like to confirm whether this is the correct approach for a completely independent subject area or whether a different architecture is recommended.

Any documentation, implementation examples or guidance on the supported capabilities and limitations would be greatly appreciated.

Bhaskar Konar · Answer

Hi @User_X8FWT,

Welcome to the Oracle AI & Analytics Community!

The following Oracle documentation may be useful as a starting point for this requirement:

Load Data from an SFTP Source into Fusion Data Intelligence (Preview):

https://docs.oracle.com/en-us/iaas/analytics-for-applications/doc/load-data-from-sftp-source-into-fusion-data-intelligence-preview.html

It provides the steps for configuring an SFTP connection in Fusion Data Intelligence, including the SFTP connection details, file layout, metadata refresh and connection testing, followed by using the SFTP source for data augmentation.

This could be relevant if the Business Central data is being extracted to files and made available on an SFTP server before being ingested into FDI.

Hope this help.

Thank you.