Loading Data from Oracle Object Storage into Oracle Fusion Analytics

 Oracle Fusion Analytics 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 article outlines the steps for loading datasets to Oracle Object Storage Service (Object Storage Service) and ingesting the data into Fusion Analytics, which include:

1. Create an Object Storage Service bucket and dataset folders
2. Create metadata for each dataset
3. Obtain the fingerprint of the API key of the user
4. Enable the Object Storage Service connector in Fusion Analytics
5. Create an Object Storage Service connection within Fusion Analytics
6. Load the data into Fusion Analytics using data augmentation

Note: Only XLSX and CSV formats are supported for the datasets. Each dataset must have a corresponding metadata file with a predefined structure. In the example in this article, there are 3 dataset files named Items.xlsx, Stores.xlsx, and Sales.xlsx.

A.  Create an Object Storage Service bucket and dataset folders

1. Log in to the Oracle Cloud Infrastructure (OCI) console. Click Storage, then Buckets.
   
2. Click Create Bucket.
    ​
3. Find the bucket namespace in Bucket Information, then General. 
   
4. Build the URL using the bucket name and the namespace in the format shown below.  Save the URL in a text editor for a later step.  URL format:   https://objectstorage.region.oraclecloud.com/n/<bucketnamespace>/b/<bucketname>
   
    
5. Click More Actions and then Create New Folder to create a parent folder within the new bucket. The parent folder holds subfolders for each dataset.
   
   Note: Make a note of the folder name for a later step. The parent folder would be specified as the "Remote Host Extract Files Directory" in Step E. Make sure there is Delete permission on the folder. Without delete permission, metadata will not be extracted.

This example includes three data files – Items.xlsx, Stores.xlsx, and Sales.xlsx. Use the same names as the datasets for the subfolders – Items, Stores, and Sales. Ensure that the final structure is similar to the following figure.

B. Create metadata for each dataset    

1. Create metadata files for all the datasets, including the parent folder.
2. Create a local file named META_DATASTORES.csv (parent folder metadata) with the following columns:                      

• DATA_STORE_NAME – A column that holds the name of the dataset. In this example, the dataset files are Sales, Stores, and Items.
• DATA_STORE_LABEL – A column that holds the dataset description (optional).

          ​

3. Each subfolder needs a metadata file corresponding to the dataset and the actual dataset file.  Name the metadata file META_DATASTORES_name of the dataset_COLS.csv. The Items dataset in this example has the following data.

         ​

4. Create a local metadata file named META_DATASTORES_Items_COLS.csv with the following column names, then upload the file to the Object Storage Service bucket:

• DATA_STORE - Name of the dataset.
• COLUMN_NAME – Column name in the dataset.
• COLUMN_LABEL – Description of the column (optional).
• DATA_TYPE – Data type of the column.
• WIDTH – String length (mandatory for VARCHAR2 only).
• PRECISION – Applicable to NUMBER data type. Make sure to set the precision for decimal values; the default value is 18 (optional).
• SCALE – Applicable to NUMBER datatype (optional).
• KEY_SEQUENCE – Identifies the primary key. In this case, Item_Id is the primary key.

Note: Repeat the subfolder metadata step for each dataset.

5. Upload the datasets and metadata files to the Object Storage Service Bucket.

6. Ensure that the final structure is similar to the following figure.

C. Obtain the fingerprint of the API key of the user

1. Click Identity & Security and then Users. Identify the user with access to the bucket created in Step (1) of the Create Object Storage Service bucket and the dataset folders section.

        ​

2. Click Copy. Paste the OCID of the user to a text editor for later use.

         ​

3. Click Resources, API Keys, and then Add API Key.

          ​

4. Ensure Generate API Key Pair is selected. Click Download Private Key and then Add. This generates the private key and the fingerprint.

        ​

5. Open the downloaded private key using a text editor.
    
6. Copy the contents of the private key to a text editor for later use. Be sure to copy -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY-----
    
7. Copy the fingerprint of the user under API Keys to a text editor for later use.

         ​

8. Click Profile, Tenancy and Tenancy Information to obtain the tenancy ID.

                   ​

9. Click Copy to copy the Tenancy OCID and save it to a text editor.

D.  Enable the Object Storage Service connector in Fusion Analytics

1. Log in to Fusion Analytics as a user with a Service Administrator role.
    
2. Click Console and Enable Features.

         ​ 

3. Enable Oracle Object Storage Service.  

E.  Create an Object Storage Service connection within Fusion Analytics

1. 1. Click Console, Data Configuration, and then Manage Connections.

          ​

1. 2. Select Oracle Object Storage Service as the connection type.

        ​    

• 3. Complete the following connection details:
• Connectivity Type - Standard
• Remote Host Extract Files Directory - Parent folder underneath the bucket.
• URL - https://objectstorage.region.oraclecloud.com/n/<bucketnamespace>/b/<bucketname>
• User ID - User OCID.
• Fingerprint - Fingerprint of the user.
• Tenant ID - Tenancy OCID.
• Private Key - Contents of the private key. Ensure it includes BEGIN and END sections.

           ​

        This example uses .xlsx datasets, therefore the File Type is specified as xlsx.

          ​

Note: File Type can be either XLSX or CSV. Don't specify the file type as CSV for XLSX files. Make sure to specify the CSV delimiter, CSV Date Format, and CSV Timestamp Format for the CSV File Type.

1. 4. Click Save.
       
   5. Click the ellipsis button next to the newly created Object Storage Service connection and click Test Connection followed by Refresh Metadata.

          ​

 Note: Data augmentation won't work until you refresh the metadata. This ensures that the datasets are available during data augmentation.

1. 6. For Connection and Refresh Metadata statuses, click Navigator, Data Configuration, Activity History, and Request History.

F.  Load the data into Fusion Analytics using data augmentation

Load the dimension tables first and then the fact table. In this procedure, you load one dimension table and one fact table.  Repeat these steps for each dimension table.

To load the Items Dimension table:

1. Select “Oracle Object Storage Service” as the Data Source. Navigate to Console, then Data Configuration, and then Data Augmentation

         ​

2. Click Create to create the data augmentation:

• Augmentation Type - Create Dimension
• Source Data Type - Supplemental Data.
• Pillar - OBJ_STORAGE.
• Source Table Type - System Provided.

        ​

3. Select the primary key and the attributes.

          ​

4. Enter Name, Description, and Table Suffix. Leave the Subject Area field blank. Click Finish.

          ​

To load the Sales Fact table:

1. Create data augmentation for the Sales Fact table.

          ​

2. Select the primary key and the attributes.

           ​

3. Select “Treat as” as Dimensions for foreign key columns.

          ​

4. Specify the required joins.

          ​

5. Click Finish.

           ​

Once the data augmentation is complete, use OAX_USER to connect to the Oracle Autonomous Data Warehouse (ADW) that Fusion Analytics and verify the data in DW_OS_X_ITEMS_DIM and DW_OS_X_SALES_FACT (table names used in this example). Refer to How to Connect to ADW for guidance.

           ​

Call to action

Follow the example in this article to try it yourself!  See Load Data from Oracle Object Storage into Fusion Analytics Warehouse for additional details. Refer Upload Datasets to Oracle Object Storage to automate the data upload using Python.