Co-Author: Saunnie Bell, Technical Manager, Oracle Consulting
Content validated on 12/18/2020 with
ODI Version 220.127.116.11.200304.2238
ADW Version Oracle Database 19c Enterprise Edition Release - Production Version 18.104.22.168.0
This blog is part of a series covering topics on how to utilize Oracle Analytics / ETL / Data Integration tools to extract data from various data sources. Future blog topics will cover extracting from: Oracle SaaS Applications, Oracle on-premise applications (e.g. EBS), and other Oracle applications such as Responsys, Eloqua and third party data sources like Salesforce or Workday.
The first blog on Salesforce detailed how to replicate Salesforce data into ADW using ODI Marketplace. This blog walks though how to configure Oracle Analytics Cloud (OAC) to replicate data from Salesforce into a custom schema in the Fusion Analytics Warehouse (FAW) Autonomous Data Warehouse (ADW) database.
The steps will require to :
Create connections to Salesforce and a custom ADW Schema
Create a Salesforce Data Set in OAC
Create a Data Flow in OAC to consume the Salesforce Data Set and persist its data in a custom schema table in ADW
Create and schedule a Sequence in OAC to have the Salesforce data refreshed in ADW
Suggested Prerequisite Reading Material:
Follow the steps in the first blog for instructions on ADW Configuration (Step 5-h, 5-i, 5-j and 6-d)
Create a custom schema (in our example SFDC) as per the guidelines here.
Access to a Salesforce instance. Salesforce Developer Edition can be created here.
Reset your salesforce Security Token as described in the first Salesforce blog.
The local OCI user must be granted these policies: "read buckets in compartment" and "manage objects in compartment".
Autonomous Database Requirements:
The Autonomous Database wallet needs to be downloaded prior to following the steps below
The following steps will guide you through creating a Salesforce connection in OAC
a) In FAW, click on Projects to open up OAC home page.
b) In OAC, click on Data to be able to create a connection to Salesforce.
c) In the Data section, click on Create > Connection.
d) In the Create Connection window, search for Sale, click on Salesforce.
e) Enter a name and a description for the connection. Fill in the username, password and security token details and Save.
The following steps will guide you through creating an ADW connection in OAC. The ADW wallet will need to be downloaded prior to completing the steps below. Also a custom user/schema will need to be created in ADW to support the Salesforce data ingestion, as per the guidelines here.
a) In the Data section, click on Create > Connection.
b) In the Create connection window, search for Autonomous and click on Oracle Autonomous Data Warehouse.
c) Enter a name and a description for the ADW connection. Drag and drop the ADW wallet the Client Credentials section. Type in the custom ADW user name (here SFDC) and password. Select a service name (here high) and click Save.
d) Verify that both the Salesforce and ADW connection are created.
Now that both connections are created, the Salesforce Data Set in OAC can be created.
The following steps will describe how to create a Salesforce Data Set in OAC.
a) In the Data section, click on Create > Data Set.
b) Select the Salesforce connection.
c) Give the Data Set a name (here SFDC_SOLUTION). Select the SFORCE>SOLUTION table and Add All Columns. Refresh the data. Click Add.
d) Confirm the Data Set is created.
Now the Salesforce Data Set in OAC is created, a Data Flow can be created and executed to the Salesforce data is loaded in ADW
The following steps will describe how to create and run Data Flow in OAC to extract data from Salesforce and load it to ADW
a) In the Data Section, click Create > Data Flow.
b) Add the SFDC_SOLUTION Data Set created the previous step and click Add.
c) Click the plus sign to add a Step to Save Data. Select to Save Data to a Data Set named SFDC_SOLUTION in the SFDC_ADW database connection. Select to Add new data to existing data. Leave the database name for the columns as default.
d) Save the Data Flow.
f) Run the Data Flow.
g) Inspect the Data Flow to verify it completed successfully.
h) Connect to ADW with a SQL client tool and verify the SFDC_SOLUTION Table is created and data loaded.
Now the Salesforce Data Flow in OAC is created and executed, a Sequence can be created and scheduled so the Salesforce data is regularly refreshed in ADW.
The following steps will describe how to create and schedule a Sequence in OAC to extract data from Salesforce and load it to ADW on a regular basis
a) In the Data Section, click Create > Sequence. Add the Data Flow created previous as a step of the Sequence and Save.
b) Once the Sequence, create a new schedule.
c) Enter the schedule details.
d) Inspect the Sequence Schedules to confirm.
This completes the activities in this blog.
Click here for more A-Team Oracle Data Integrator (ODI) Blogs
Click here to sign up to the RSS feed to receive notifications for when new A-team blogs are published
Click here to access the Fusion Analytics Warehouse Administration documentation library
This article walked through the steps to configure Oracle Analytics Cloud to replicate data from Salesforce into an Autonomous Data Warehouse (ADW) database.
This blog is the first in a series covering topics on how to utilize Oracle ETL / Data Integration tools to extract data from various data sources. Stay tuned for more to come!
Bookmark this post to stay up-to-date on changes made to this blog as our products evolve.