Introduction
Oracle has recently introduced a new Redwood based Data Extraction tool for Fusion Applications. It uses RODS, the “Read-Optimized Data Store”, which is a replicated read-only layer used for extracting Fusion application data into an Autonomous Data Warehouse (ADW).
RODS is positioned as the direction of travel for Fusion bulk data extraction.
Oracle’s documentation describes the new Data Extraction tool as supporting business views and extraction views, full and incremental extracts, scheduled or on-demand runs, status monitoring and data export.
What About BI Cloud Connector?
BI Cloud Connector has been available for years as a process to extract data from Oracle Fusion. RODS is a different as it has a similar flow to BICC – find the view objects you want to use, create an extract schedule, run it and process the data it returns. The major differentiator is that the data is being extract not from the Fusion OLTP database, but from a replicated version on an autonomous database.
For new extract work, the sensible position I think is:
If the data is available through RODS, use RODS.
If it is not available yet, keep using the existing route and plan the migration.What About BI Publisher? ( BIP )
A recent A-Team blog reminds us that “Note: BIP is a reporting tool and not recommended for large-scale or integration-grade data extract” and that is going to be subject to an article all of itself.
Creating a simple extract
Assuming the security has been setup to enable the user to access the extract mechanism, then the new data extract option should appear under Tools.
This gives us a redwood screen, similar in style to the AI Agent studio, with the tabs along the bottom. Here we can create a new extract definition, select the source object, select the attributes and optionally add some rules.
For this example I created an extract for Invoice Holds. I selected the business view I wanted from a drop down and then selected the attributes that I desired.
We can specify the initial extraction date, the format in which we want the data to appear ( CSV or also JSON format )
There are a couple of important details worth considering here. Oracle says timeCreated and timeUpdated are selected by default and must remain selected for the extraction process. Basically these are the date the record was created and the date it was last updated, which we need for incremental processing. The Initial Extract Date can also be used to exclude records created before a specified date. If no records are created or updated after that date, the job can complete successfully but produces no output.
In the screen definition for the extract object we can also add filter rules to filter the data as we may see fit. These can be inclusion or exclusion rules.
We can see the attribute names from the object we have selected in RODS and also the names the extract will give them.
These column names differ from the ones assigned in BICC, so if you are migrating from BICC there’s a really useful option to swop in the BICC names. By pressing this button you’ll see that the column names change to the BICC ones.
Create a schedule
Now we have a simple object set up, we can define an extraction schedule for it. We can set the frequency, which days to include/exclude, if it is full or an incremental extract, etc. We can also specify where the extracted datafile is to go. Here I am going to use the default of Oracle OCI, but as this is a 26C instance I could also set up customer managed OCI and put the data in my own bucket.
I can set it off running immediately, and we see then the extract is “in progress”
I can look at my schedule to see which parameters it is using to run with.
Extract Jobs
Clicking on the jobs tab, I can see my job has run successfully and it was an incremental and I can also see the time it took to do that. Remember that this is is running on an autonomous database containing near real-time data not the OLTP database from the Fusion system.
The Action button gives us the ability to download the files produced by the extract. There is a metadata “manifest” and the actual downloadable file. It had a very long name, being a concatenation of various elements.
I downloaded it to my laptop to look at it.
The zip decompressed into two files.
As I chose CSV for the output I now get to see the data in CSV format.
REST APIs and Automation
For manual testing, the in-screen download is great as it lets you prove the extract definition, check the schedule, download the ZIP and validate the data.
For anything programmatic, the REST APIs are the obvious route as we can control the process as we did with BICC to orchestrate a suite of extracts at the extraction “heatbeat” desired by the customer to run the schedule, monitor the job and take the extracted data to refresh a lakehouse/make available for user consumption.
The important point is authentication as the Data Extraction REST APIs requires OAuth authentication. The OAuth client also needs permissions for Oracle SaaS Batch Cloud Service and Oracle Boss Cloud, and the Client ID must be configured as a Fusion user with the required custom job role.
Summary
My initial view is that RODS Data Extraction is going to become an important part of the Fusion data landscape.
The data extraction “burden” is removed from the live Fusion pod and takes place against a autonomous database that is better placed to serve up that data. The setup and interface follows Redwood standards and we have full and incremental extract automation available through REST APIs.