Load data from Fusion Apps using Autonomous Database

Loading data from Fusion Apps (ERP Cloud, HCM etc) to Autonomous Database is one of the most required use cases that ours customers ask for help.

We can use ODI to do it, you can find how to do it here. However, there are customers that can’t have ODI and they still need to analyze data from Fusion Apps.

If you are using Autonomous database you have a couple of tools for free that will help you to load data from multiple sources (included Fusion Apps).

There is no direct access from Autonomous Database to Fusion Apps. We still need to use BICC. In this article I’m not going to explain how to configure BICC. You can find on A-TEAM’s article how to configure BICC extractions.

Autonomous Database — Database Actions

Go to Autonomous Database and click on your autonomous instance. You have a tab named Tools. Click on it and you will see database actions. When you click on database actions you need to put your schema (Example: ADMIN) and after your password. Now you are on Autonomous Database — Database Actions.

Click on Data Load and after on Cloud Locations:

On Cloud Locations you will configure your object storage connection. Click on Add Cloud Storage and then fill out a couple of fields. In case you don’t have a token you have to create one (check here how to generate a token). In case you have already a credential you can choose the option Select Credential.

Now, we have a the connection between Autonomous and Object Storage created. Let’s configure our table to read data from Fusion Apps. Go to Database Actions home page and click on Feed Data.

Now, click on Create Live Table Feed.

Target Table Name it’s the table that it will be created and store data from all files with the pattern file_fscmtopmodelam_costelementam_costelementpvo-.*\.csv.gz

This pattern it will read all files that start by file_fscmtopmodelam_costelementam_costelementpvo-

These files are the ones that BICC is exporting to Object Storage. Choose the schema you want to create the table and cloud storage connection you created in previous step.

The Pooling is how often you want to refresh your data.

Click on create and check your database to see if you have the table created and data there:

Every time that BICC extracts new files to object storage this table is updated. You can check how many files were read it, using Live Table Feed Run Details:

Now, you can create as many tables as you want.

If you have any doubt, reach out to me through LinkedIn or Medium.