With Inputs from:

Nitin Deo, Senior Principal Product Manager

Introduction

Oracle Fusion Analytics (Fusion Analytics), powered by Oracle Autonomous Data Warehouse and Oracle Analytics Cloud, 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. This packaged service consists of a data pipeline, data warehouse, semantic model, and best-practice content such as data visualizations and workbooks. Blending the data residing in CSV or Excel files with Fusion Analytics might be needed for analytical reporting. Example use cases may be bringing in survey data, additional cost center data, budget data, employee job grade data, etc.

One method to blend the data from CSV or Excel files consists of using Secure File Transfer Protocol (SFTP) Connector. This method comprises of the following:

1.  Connect to the data files securely.
2.  Bring the data into Oracle Autonomous Data Warehouse (ADW).
3.  Build a semantic model.

Securely connecting to the data files requires configuring the SFTP Connector. This article focuses on configuring the SFTP Connector. 

Configuration Steps

Following are the steps required to configure the SFTP Connector in Fusion Analytics:

1. Complete the prerequisites.
2. Configure SFTP.
3. Test SFTP and refresh metadata.

1. Complete the prerequisites

• Open a service request with Oracle Support to add the SFTP Internet Protocol (IP) address to the allowed list.
• Ensure that SFTP server supports user authentication using a username and private key.
• Set up the FTP folder:
  • Create a base folder (for example, “faw”) on the SFTP server.
  • This folder contains a subfolder (for example, “ITEMS”) and a META_DATASTORES.csv file. Multiple data files have corresponding subfolders.
  • The subfolder, also called as data store folder, corresponds to the name of the CSV or Excel file.

• The META_DATASTORES.csv contains the following three fields: DATA_STORE_NAME, DATA_STORE_LABEL, and IS_EXTENSION. Each record in this file corresponds to each data store.

                                            Example contents of the META_DATASTORES.csv file:

• SFTP data store set up:
  • Each source file has its datastore folder.
  • Each data store folder contains two files: 1. Source file to load 2. Metadata of the source file that contains the column definition.
  • The metadata file naming convention is META_DATASTORES_<DATA_STORE_NAME>_COL.csv
  • The header record of the META_DATASTORES_<DATA_STORE_NAME>_COL.csv are as follows: DATA_STORE_NAME, COLUMN_NAME, COLUMN_LABEL, DATA_TYPE, WIDTH, PRECISION, SCALE, KEY_SEQUENCE, and IS_EXTENSION.

                   Example contents of the META_DATASTORES_ITEMS_COL.csv file:

• The user name used in the authentication has full permission (read, write, and execute) on the top level folder (for example, “faw”).

2. Configure SFTP

• Enable SFTP Preview Feature

  1. In Fusion Analytics, navigate to the Console and then click Enable Features.

     

  2. Within Enable Features and Connectors, click the toggle button next to SFTP.

• Set up SFTP Connection

  1. In Fusion Analytics, navigate to the Console and then click Data Configuration.

     

  2. Within Data Configuration, select SFTP as the data source and then click Manage Connections.

     

  3. Within Manage Connections, click Create and then select Connection.

     

  4. Within Create Connection, select SFTP as the connection type.

     

  5. Enter the values for * (required fields) and click Save. Required fields are Connectivity Type, Remote Host, User Name, Private Key, and Remote Host Extract Files Directory (for example, the “faw” directory created in the pre-requisite).

     

3. Test SFTP and refresh metadata

• Test SFTP connection

  1. In Fusion Analytics, navigate to the Console and then click Data Configuration. 

     

  2. Select SFTP as the data source within Data Configuration and then click Manage Connections.

     

  3. Within Manage Connections, SFTP as Connections Name, click Action and select Test Connection.

     

  4. Navigate to Data Configuration, select SFTP as the data source, and then click Request History within Activity History.

     

  5. Validate that the Status for Request Type  “Test Connection” is completed.

     

• Refresh Metadata

  1. In Fusion Analytics, navigate to the Console and then click Data Configuration.

     

  2. Select SFTP as the data source within Data Configuration and then click Manage Connections.

     

  3. Within Manage Connections, SFTP as the connections name, click Action icon and then select Refresh Metadata.

     

  4. Navigate to Data Configuration, select SFTP as the data source, and then click Request History within Activity History.

     

  5. Validate that the Status for Request Type “Meta Data Extract” is completed.

SFTP Connector is configured and ready to use for data augmentation.

Call to Action

Follow the steps in this blog to securely connect to the source data file using the SFTP connector feature in Fusion Analytics. For more information, see Oracle Fusion Analytics Warehouse documentation.