The Oracle of the Cloud, the Fellowship of the Data Integrator

Robin Dublon -

Oracle Cloud Infrastructure has a suite of integration tools comprising of Data Quality, Oracle Streams, GoldenGate, Data Integrator classic and Data Integrator Web Edition (which is also named Data Tranforms) all of which have capabilities that make ETL and ELT work far simpler and quicker, as it is designed to offer high-performance capabilities to handle complex, demanding workloads. But sometimes your requirements aren't so complex or demanding, and rather than three movies worth of epic storytelling, you actually just need something quick, clean and easy to use to get data from Source A to Target B. That is where the Web-Based Edition of Data Integrator comes into play.

Now you may be aware of the immense power of Oracle and fully setting up the application either on-premise or online (maybe via a Virtual Machine) but I am going to go over some of the main benefits of going for the web edition if you need something simpler to use:

  • Quick to set up, and can be done from anywhere so long as you have an Oracle Cloud Account.
  • Easy-to-use graphical interface that can be used to design graphical data transformations for data integration.
  • Can be used to design data transformations in the form of data loads, data flows and workflows without needing to write any code.

Here are some examples of creating a Data flow, to start here, is a very simple Source to Target:

On the left-hand side are the Source and Target tables, with the graphical image being the flow of source to target.

Here we have a similar flow, but with multiple joins in the graphical flow created:

As before, but with the multiple joins you can see that even if it does require joining multiple sources to load a target, it can handle it in a simple manner.

With this interface, it is a simple select, drag and drop functionality to build these flows. Each of these points on the above table can be selected and inspected to indicate the attributes being used, what joins might be happening with the source tables and what the transformations entail, even being able to view the SQL behind it all if you needed to.

There are more options that you can find with this tool, and while it is mainly for simple ETL, it can have a surprising level of depth to its capability for a web-based tool, all without requiring a single bit of code!

If you have used classic Data Integrator before, this will be a very simple tool to get to grips with, but even if you are a fresh-faced Oracle Dev, you will be able to use it very easily since once connected to the source and target locations it will be easy to create your data flows within minutes.

But how do you get it all set up I hear you ask, well worry not, there is an Oracle guide that is simple and easy to use here...

But to make it even easier, here are the steps to get you all setup:

1. In the Oracle Cloud Marketplace, search for the Data Integrator: Web Edition, (link to the page is here) and when found, click Get App. You will need to select the OCI Region, Tenancy Details and Sign In to your Oracle Cloud Account to Launch the Instance.

2. Once you launch the instance, you will be navigated to the Create Compute Instance page. Here you choose a name for the instance and what Compartment it will be created in (which defaults to the compartment you have selected on the Oracle Data Integrator for Oracle page). You will also need to select one of the availability domains, and Click Change Shape to choose the template you want to base the instance on.

3. You'll then need to configure the Network Settings, starting with Primary Network.

4. Following that then you will need to configure Subnet for the Network Settings.

5. Finally for Network Settings there is the Public IP Address, of which you'll either need to assign an address or not.

6. The last important step is to add an SSH Key so that you can connect to the instance using SSH. This has a few options on what to do here, but essentially it is to either generate, or input an SSH Key you have generated yourself, or not use an SSH key at all.

7. Then just click Create and the OCI console will start the job. It should take 5 minutes to create the instance of which you will get a successful confirmation from the job.

Now you should be able to access Data Integrator and get started!

Now I warn you, this may be the gateway tool into the more expansive world of ODI, since it is free when you load into Autonomous Databases and initially may want only simple transformations and workflows. But in time you will crave more in-depth and complex transformations and workflows for your data which is where many probably go on their fantastic journey with the precious- I mean to the ETL solutions that give the best support to discovery, reporting, analysis, and decision-making with your data.

So I leave you with this simple message for your upcoming future with Oracle Data Integrator, a quote from Gandalf (if he was an Oracle Developer instead of a wizard) "All we have to decide is what to do with the time that is given us."

Which I am pretty sure is how he would react when a client needs their data as soon as possible. Though with Data Integrator, he won't need as much time as say trying to get an evil ring into the fires of Mount Doom.

Not sure why he still refuses to use Eagles but always good to see if Oracle can help...