Don’t tell anyone who does data migration for a living, but getting data into Salesforce is a relatively straightforward practice. Getting that data back out of Salesforce in a format that allows for useful reporting and snapshotting is a different kind of adventure altogether.
There are many reasons why a company may want to report on historical data. Suppose you’re in charge of hiring sales reps for your company. You want to determine if the support staff you hired last year was a good investment by comparing the level of detail on contact records before and after the hires. Now imagine you run a product company, any product company. You already track inventory, and you have a sneaking suspicion that some months, weeks, and days see higher than average demand, but you want to back up that hunch with data. Or say you work in the IT department. A year after your company rolled out a big new piece of functionality, you’re about to roll out another. To estimate the level of support effort, your boss wants to know the total number of help desk tickets that had been logged a month after last year’s go live. Each of these situations presents a real challenge to Salesforce users because they all require access to historical data.
Below describes a cool approach we came up with to address snapshotting. We combined Heroku Connect’s capabilities with a little bit of PostgreSQL customization to solve a common problem in a straightforward and flexible way.
Working with our existing Salesforce org, we leveraged Heroku and a couple lines of code to pull data from our Opportunity object, push it into a PostgreSQL database, stamp that data with the current date, and do what can sometimes feel like the impossible: Take snapshots of Salesforce data.
Aside from adding the current date, the entire process relies on native Heroku functionality. Here’s the step-by-step of how it works.
Once logged into Heroku, we created an application.
Next, we used the Heroku Toolbelt (CLI) inside your new app to spin up a PostgreSQL database.
To up the ease of use factor even further, we leveraged an application called pgAdmin III, a front-end client for running commands against PostgreSQL databases to get ours up and running.
Now that we’re the proud owner of a database, we used the add-on feature to bring Heroku Connect into the app. As the name suggests, Heroku Connect bridges the gap between Salesforce and Heroku PostgreSQL databases. Once that connection is established, we chose the relevant table from the list of Salesforce objects, and configured a custom mapping. From this screen, one can also choose the frequency with which the connector performs the sync. To get comprehensive snapshots of our Opportunity data, we grabbed the Opportunity object and pulled in all available fields. Because a snapshot is only as good as the accuracy of its time dimension, we synced every ten minutes. There is an important thing to note in this step. This sync frequency is not the snapshot time. It is just the frequency that Salesforce and Heroku will stay in synchronization with each other. The periodicity of the snapshot will be setup in a few more steps.
Heroku automatically generates a Salesforce schema and tables for all synced objects. Here, and only here, did we pull out the custom code. Our process runs a java application that calls a stored procedure in the Heroku database. This procedure copies the Opportunity table in Heroku, and stamps each row with the current date, allowing us to take our snapshots.
– Tables auto-created by Heroku Connect
Java code to execute the stored procedure backup_opportunities()
Stored procedure to snapshot opportunity data
The new data set is stored in a different table in Heroku “sfdc_opportunity”.
In order to automate the process, we returned to the add-on feature one more time and pulled the Heroku Scheduler into our application.
We configured the scheduler to run daily and brought the fourth dimension to Salesforce.
While the ability to take snapshots of Salesforce data is its own kind of magic, the real power lies in the fact that once the data’s in a PostgreSQL database, you can do anything with it. Heroku provides a configuration friendly and streamlined way to take Salesforce data, and other data you might be housing in various applications, connect it, and reveal it for analysis.
Heroku’s set up to play well with Salesforce, but it can sync data from almost anywhere. And there are few limitations on the number of sources Heroku can integrate. Once all of that data is together and exposed in a database format, you’re free to access it and manipulate it without having to go through the UI of any given application. Instead, you have access to your data, all your data, in the same place, and in the format that presents the most potential for manipulation.
In our case, Heroku provided the means to solve Salesforce’s notorious data snapshotting challenges by by-passing Salesforce’s UI altogether.
If you need to report on historical data in Salesforce, let the consultants at Cervello help. In addition to our extensive analytics experience, we bring to bear a mix of deep technical and problem solving skills on the Salesforce platform that will help make your Salesforce reporting challenges a thing of the past.
Interested in learning more? Join our mailing list!