by Bill Emerson | May 12, 2021

Solve Salesforce and SQL Server Data Challenges with DBAmp

Salesforce provides teams and companies a powerful cloud hub to understand their customers and coordinate customer interactions across sales, marketing, and support teams. For Salesforce to serve as an end-to-end customer ecosystem, organizations must connect Salesforce to the data they use across their business – whether that's on-premises or in the cloud.

Unfortunately, many organizations face a major challenge in connecting Salesforce with on-premises accounting, ERP, and other operational and transactional systems that rely on SQL Server to store and manage data. The result is data scattered across various cloud and on-premises data silos.

Typically, organizations need all these applications to share data in order to:

  • Connect data silos, because each business app contains elements of data needed by the other.
  • Access data from multiple silos for purposes of reporting.

CData DBAmp enables your organization to accommodate both types of data flows between Salesforce and your SQL Server-based systems.

Bi-Directional Connections Between Data Silos

Consider the case where your salespeople close deals and use Salesforce to track product sales. During the process, your team collects large volumes of data in the CRM, such as the account name, contact name, price quote and so forth. Salesforce must then send information to the on-premises ERP system to direct product inventory, and the on-premises accounting system to book the sale.

If that same customer comes back a few weeks later and wants to make an additional order, the sales team will want to use Salesforce to determine whether the previously ordered product has been delivered and the invoice paid. This information must be pushed from the ERP and accounting systems into Salesforce.

In this case, the data flow needs to be bi-directional between the Salesforce and on-prem systems. DBAmp allows Salesforce to exchange data with SQL Server-based ERP, accounting, and other systems bi-directionally.

Using SQL Server with DBAmp installed, developers can simply use their knowledge of T-SQL to build these bi-directional data flows. Because DBAmp takes advantage of SQL Server your developers and DBAs are more productive and can deliver the needed Salesforce integrations faster.

Business Intelligence and Reporting

Businesses often need to access data scattered among multiple data silos for use in BI and reporting tools. You have two options to do this:

  • Scheduled Data Replication: You can schedule data replications, such as creating a job in SQL Server to replicate data from Salesforce into a SQL Server database where it can be accessed by a BI or reporting tool. This method ensures that the data necessary for reporting is in one place and easily accessible.
  • Live Integration: Alternatively, you can use a tool that connects directly to Salesforce and queries the data in real-time for use in your BI and reporting systems.

Both methods have advantages and disadvantages. Replicating data to a central location makes it simpler for business users to access that data. But once replicated, the data in the central location immediately starts to become stale and possibly lose value. For example, if a business wants to look at up-to-the-minute opportunities, it may find that data copied from Salesforce into the data warehouse at 2am will have little value by 1pm.

Accessing data in real-time always gives you the most up-to-date data. But for large amounts of data, a real-time query may not perform fast enough to provide a good reporting experience.

DBAmp gives you the best of all worlds.

You have the choice of replicating data from Salesforce to SQL Server or accessing Salesforce data with SQL server in real-time. For many organizations, a hybrid approach using both methods can provide the best solution. For example, DBAmp can access highly dynamic Salesforce data in real-time while replicating large amounts of slow changing information on a set schedule. DBAmp contains all the logic necessary to incrementally refresh replicated data, easing the configuration of data mirroring from Salesforce to SQL Server. Through this hybrid approach, DBAmp provides an optimized solution for BI and reporting on Salesforce data.

An Example of the Hybrid Approach

We worked with a major network equipment provider that wanted to offer a real-time sales data dashboard to its sales representatives. The dashboard would be displayed on large monitors in their sales offices and give employees instant access to sales activities within the group.

Initially, the company used an approach that could only copy Salesforce data into a SQL Server data warehouse. The tool replicated two types of data from Salesforce: a massive, one million row Account table and a smaller Opportunity table. The dashboard then accessed the SQL Server data warehouse as a source for the charts and tables. But after the solution was deployed, the sales reps noticed a lag between when a sale was made and when it appeared on the dashboard.

They modified their solution to access data from the smaller Salesforce Opportunity table with a real-time query using DBAmp in a hybrid configuration. Since the Account table contains a large amount of data, they use DBAmp to replicate a copy of the Account table at night and incrementally refreshes the mirrored Account data during the day.

The result is a sales dashboard that uses a hybrid approach to both provide large-scale Account data access and motivate the sales team as they close opportunities in real-time.

Request a Demo of DBAmp

Many organizations have data in multiple silos, including on-premises SQL Server databases and cloud-based Salesforce tables. With DBAmp, developers can quickly build bi-directional integrations that meet demanding business needs, and support BI and reporting with a unique, hybrid approach to data access.

To evaluate how DBAmp can help your organization solve Salesforce data challenges, take advantage of a free trial of DBAmp.

Get DBAmp Free Trial