Validation

Content validated on 11/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

*** 01/10/20 - This feature is in early release. The A-Team continues to collaborate with Oracle Development on these configuration steps and will update the blog accordingly as new information is made available *** 

~

This blog is the second in a series covering topics on how to utilize Oracle 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 detailed how to replicate Oracle Fusion SaaS data into ADW using ODI Marketplace and BICC. This blog walks though how to configure Oracle Data Integrator (ODI) on Oracle Cloud Marketplace to replicate data from Salesforce into an Autonomous Data Warehouse (ADW) database.

Suggested Prerequisite Reading Material:

* Using Oracle Cloud Marketplace

Overall prerequisites

* Follow the steps in the first blog for instructions on:

Policies Assignment (Step 1)

SSH key-pair Generation (Step 4-b and 4-c)

ODI Marketplace Provisioning (Step 2)

ADW Configuration (Step 5-h, 5-i, 5-j and 6-d)

* Access to a Salesforce instance. Salesforce Developer Edition can be created here.

User/Policy/Key Requirements:

* It is strongly recommend to configure OCI, BICC, and ODI with a local (non-Federated) user.

* The local OCI user must be granted these policies: "read buckets in compartment" and "manage objects in compartment".

Autonomous Database Requirements:

* The Autonomous Database needs access to Object Storage to retrieve the View Object extracts.

 Reset Salesforce Security Token 

a) Connect to Salesforce.

b) Go to user settings.

c) Click on Reset My Security Token.

d) Click on Reset Security Token.

e) Check your email for the new token.

f) Create a Topology Definition table in Word or Excel to gather/document required credentials.

Add the Salesforce Security Token to the Topology Definition table.

Topology Definition Table:

 Apply Salesforce JDBC driver patch 

At the time of writing the current version of ODI Marketplace is 12.2.1.4. This version has a known issue with the Salesforce JDBC driver that causes an anomaly with the reverse engineering of the Salesforce tables into the ODI Datastore.

If the "Unique Constraint Violation Error on reversing Salesforce objects" error is shown when running the Selective Reverse Engineering process - download/install the following patch: Patch 30409972: Receiving Unique Constraint Violation Error on Reversing Salesforce from here. 

Follow the steps below to apply the patch:

a) Shutdown all ODI Components [ODI Studio & ODI standalone agent]:

b) Copy patch 30409972 onto the ODI Marketplace VM (using scp) -> Unzip the patch file:

c) Set the environment variables:

export ORACLE_HOME=/home/opc/oracle

export JAVA_HOME=/home/opc/jdk1.8.0_191

export PATH=$PATH:/home/opc/oracle/OPatch:/home/opc/jdk1.8.0_191/bin

d) Go to the patch top directory -> Run opatch:

cd 30409972

opatch apply -jre /home/opc/jdk1.8.0_191/jre

Verify the patch has been applied:

opatch lsinventory -jre /home/opc/jdk1.8.0_191/jre

e) Clear the ODI Studio cache:

nohup /home/opc/oracle/odi/studio/./odi.sh -clean -initialize &

f) Start the ODI Agent:

sudo systemctl start agentodi.service

 Set up Salesforce Topology in ODI Marketplace

a)  Create a new Salesforce Data Server:

Complete the data server definition - using the example below as a guide.

Note: the token is appended to the end of the password - no space between.

Add the JDBC settings shown below:

Save -> Test the connection.

b) Create a Physical Schema:

Add the Physical Schema settings as shown below:

c) Create a Logical Schema as shown below:

 Create ODI Models and Reverse Engineer Salesforce Datastores

a) Create a new model folder -> Create a new model.

b) Run selective reverse engineering -> Confirm that the datastores for Salesforce have been created.

 Update ADW diagram and create target ADW Datastore

a) Open the ADW diagram create during the previous blog -> Drag and drop the ACCOUNT Salesforce datastore onto the diagram -> Click OK.

b) Click Reverse Engineer to create the datastore on the ADW diagram.

c) Click OK.

d) Confirm that the datastore is created and appears in the ADW diagram.

c) Update data type for timestamps as needed [between 0-9]. Other columns may also need adjusting depending on what customizations were applied to the source tables. Complete a data architecture modeling exercise to ensure all data type are adjusted to respect the Oracle target constraints (reserved keywords, data type limitations).

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/datatype-limits.html#GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-SQL-Reserved-Words-and-Keywords.html#GUID-6A07BB21-AD82-4B47-80FA-9B1141CC23C2

 Create and run ODI Mapping

a) From Designer -> Under the Project -> Create a new Mapping named ACCOUNT

b) Drag and drop the Salesforce ACCOUNT datastore on the left side of the Mapping diagram.

Drag and drop the ADW ACCOUNT datastore on the right side of the Mapping diagram.

c) Connect the two datastores -> select "Match Options" = "By Name".

d) Click on the Physical implementation -> Select the first element in the target group.

Confirm the Loading Knowledge Module is set to LKM SQL to: Oracle (Built in).GLOBAL:

e) Select the second element in the TARGET_GROUP -> Confirm the IKM is set to IKM Oracle Insert.GLOBAL -> Ensure the CREATE_TARGET_TABLE option is set to true.

f) View the data on ACCOUNT source.

g) Save -> Run the mapping.

h) Verify the ACCOUNT table is created in ADW and the data is loaded into the ACCOUNT table.

 Want to Learn More?

Click here for more A-Team Oracle Data Integrator (ODI) Blogs

Click here for more detail around the ODI Salesforce integration capabilities

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 ODI Marketplace documentation library 

 Summary

This article walked through the steps to configure Oracle Data Integrator on Oracle Cloud Marketplace (ODI) with Salesforce to replicate data from Salesforce into an Autonomous Data Warehouse (ADW) database.

This blog is the second 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.