Reference Architecture - Replicating Salesforce Data to FAW ADW using OAC

November 18, 2020 | 10 minute read
Matthieu Lombard
Consulting Solution Architect
Text Size 100%:

Co-Author: Saunnie Bell, Technical Manager, Oracle Consulting

 

Content validated on 12/18/2020 with

  • ODI Version 12.2.1.4.200304.2238

  • ADW Version Oracle Database 19c Enterprise Edition Release - Production Version 19.5.0.0.0

 Background

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:

Overall prerequisites

  • 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.

User/Policy/Key Requirements:

  • 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

 Create the Salesforce and ADW Connections in FAW OAC

Create the Salesforce Connection in FAW OAC

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.

 

 

Create the ADW Connection to SFDC custom Schema in FAW OAC

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.

 

 Create the Salesforce Data Set in FAW OAC

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

 Create and Run the FAW OAC Data Flow to Load Salesforce Data Into 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.

 Create and Schedule the FAW OAC Data Flow in a Sequence 

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.

 Want to Learn More?

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 

 Summary

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.

Matthieu Lombard

Consulting Solution Architect

The Oracle A-Team is a central, outbound, highly technical team of enterprise architects, solution specialists, and software engineers.

The Oracle A-Team works with external customers and Oracle partners around the globe to provide guidance on implementation best practices, architecture design reviews, troubleshooting, and how to use Oracle products to solve customer business challenges.

I focus on data integration, data warehousing, Big Data, cloud services, and analytics (BI) products. My role included acting as the subject-matter expert on Oracle Data Integration and Analytics products and cloud services such as Oracle Data Integrator (ODI),  and Oracle Analytics Cloud (OAC, OA For Fusion Apps, OAX).


Previous Post

Private DNS Implementation

Javier Ramirez | 15 min read

Next Post


SSO between Fusion and EPM Cloud

Ashish Singh | 3 min read