SFDC – Public Media – Oracle to Salesforce Data Migration – Success Story

Client Overview

The client is a US-based nonprofit Public Media organization, which operates Public Broadcasting Service (PBS) through public broadcasting stations and produces programs for regional and national television broadcast, along with operating numerous websites and producing rich media content for web distribution.

Business Challenge

Client needed to migrate their entire organization data, which was stored on Oracle database to Salesforce platform. The data migration process had the following challenges:

  • Mapping and conversion of Oracle tables with Salesforce objects

  • Scripting for data conversion

  • Design of the data migration flow

  • Deduplication and merging of matching data

  • Trigger handling for data migration

  • Creation of chunks of new record for migration

Technical Requirements

  • Apex

  • JDK 1.6

  • Oracle SQL Developer IDE

  • Procedural Language/ Structured Query Language (PL/SQL)

Solution Strategy

Eternus Solutions reviewed the objectives and worked with the client in the capacity of an Offshore Delivery Center, collaboratively taking care of the entire database design and development. Eternus Solutions database expert team designed and implemented their innovative ETL (Extract-Transform-Load) method for successful data migration. Listed below are some of the steps taken by Eternus Solutions team to achieve seamless data Migration from Oracle to Salesforce:

  1. Eternus Solutions team along with the client’s database experts reviewed the existing Oracle database source tables and the data stored within. Source tables and target objects combinations, along with total record count for each recorded data were identified.

  2. Client’s database experts used these results and analyzed the data that would not be needed to migrate. This analysis enabled Eternus Solutions team to determine the actual number of migration job effort needed for the migration of Oracle database.

  3. Oracle database backup was taken for all legacy data in the form of CSV files.

  4. Eternus Solutions database consultants wrote procedures and functions to hold the transformation views into the Oracle SQL developer IDE using PL/SQL scripting language.

  5. Configuration workbook was created as a guide for migration scripting with conversion logic, case translation values to map objects.

  6. Relational Junction tool was configured to create staging database with replication tables as a representative of Salesforce objects for data transformation.

  7. Translation object was created in Salesforce org to determine the values of the Oracle database objects after migrating to Salesforce.

  8. Relational Junction tool was used to push all the data of staging database to Salesforce in sequential order to complete the data migration process.

Salesforce-Relational Junction Integration Eternus Solutions project team was responsible for the integration with Relational Junction tool for data replication and integration into Salesforce. The Salesforce-Relational Junction integration enabled the client to migrate legacy data and build a Salesforce-based reporting warehouse by creating and synchronizing a local database which is an exact replica of their Salesforce data. The Salesforce-Relational Junction integration was bi-directional.

SupportThe project involved provision of support for the data transformation process, in order to create chunks of new data for data migration.

Development Methodology

  • 3-Step development methodology that consisted of development of Procedures and functions using PL/SQL scripting in Oracle SQL developer tool, testing in Sandbox or Production org and moving to the Production org

  • Design documents for procedures and functions written in SQL, along with source and target mapping documents and migration flow

  • Trigger handling for data migration process

  • Optimized joins for data deduplication

  • Removal of hardcoded values using transformation tables

  • Integration with data replication and integration tool Relational Junction

  • Development using SFDC best practices

Share This Case Study