DATA MIGRATION – FROM SQL TO FORCE.COM AND INTEGRATION

General Overview

Avison Young Realty is a leader in Real Estate management operating with 80 offices throughout the US and Canada. With the size and number of offices Avison Young (AY) was seeking an optimized solution for their CRM, Accounting, and Employee Data to create a seamless and organized interface that would allow their data to be shared across all offices, as well as moving the Data to a lower cost Cloud based platform.

AY implemented a team of consultants and programmers to facilitate the migration of Data from the various source systems into the new cloud based platforms, and ensure the integration across all systems. This involved, cleansing data for accuracy, manipulating and condensing data in order to piece together the various source databases, As well as creating a unique new platform tailored to AY and the unique needs they required as an ever-growing leader in the Real Estate industry both across the US and abroad.

The Source systems are listed out below:

General Ledger

  • Corporate MRI

Accounts Payable

  • Corporate MRI
  • Yardi
  • RealBroker
  • NC MRI

Accounts Receivable

  • Corporate MRI
  • NC MRI
  • Yardi
  • CTS
  • RealBroker
These source systems were to be consolidated and migrated into (3) systems which are listed out below:

CRM System

  • Apto: Web based CRM software that utilizes the force.com platform, specifically targeted to the real estate industry

Accounting

  • NetSuite: Cloud based financial services software that allows AY to house all financial records in one place as well as integrations with Apto CRM System.

Enterprise Incentive Management

  • Callidus: Cloud based application system used to optimize sales cycle and sales practices across the entire organization.

Role

was brought into this project to work alongside a team of IT Professionals to facilitate the migration of data from all source systems into APTO CRM system, and ensure its integration with Callidus and NetSuite. was responsible for pulling all backend data from various Data sources and then running analysis to compile Data in SQL to allow loading into the Apto SOQL backend.

By using a series of SQL scripts we were able to manipulate large amounts of backend data to be prepared for upload to Apto. In the upload process worked alongside Apto developers to integrate with NetSuite and Callidus using API triggers to successfully integrate between the systems.

The AY Accounting team then went through validating data across all three platforms and providing us feedback so that we can update our scripts and load process to ensure data migration can be completed in an efficient and accurate manner.

was able to provide excellent communication to both the client and the rest of the consulting team, using a combination of Jeera project management software, SourceTree for quality control for Scripts, and the Google Suite of products for quick communication across all organizations involved.

Challenges

was able to map and understand mass quantities of data over a multitude of sources, and figure out how to they relate to the new fields and tables in the force.com platform. By running analysis on all of the existing backend databases and was able to come up with creative solutions to overcome the inconsistencies in the fields and tables between systems as well as general inaccuracies in the data itself due to user input errors to create a seamless user interface and integration within the new system.

Resolving of integration and load errors throughout the process, in an effective and accurate manner. As well as keeping detailed step by step documentation to ensure repeatability and accuracy moving forward. As well as providing logs to the rest of the team to share information of our findings to all organizations involved to eliminate redundancy errors.

was then able to use our findings to map the data to ensure repeatability and accuracy within our process. Communication across the entire team was crucial to reaching an efficient and process that allowed us to reach a timely and accurate solution to our client.

Outcome

utilizes a team of SQL Programmers, Project Managers, and Consultants to provide a very efficient migration while keeping the cost down to meet AY’s project goals. offers experience in both SQL programming and data management along with proficiency with the Force.com platform to provide a unique solution to our clients.

demonstrated through this project an ability to come up with creative solutions to fit the ever changing needs of a growing business. An ability to work with programmers to integrate across a multitude of systems using API triggers to ensure that data is accurately shared across the organization. Clear and Effective communication to both the client and our partner companies to reach consensus across all parties.