The proliferation of integration products & tools today, combined with the API-first architecture of Salesforce, make it easier than ever before to integrate all of your data, from all of your disparate systems, as well as your legacy systems, to Salesforce. Everything from your granular historical data, campaign responses, email response info, peer-to-peer fundraising history, and each touch point your constituents have had with your organization, since the beginning of time. Many organizations feel they need everything, to get a full picture of constituent interactions with the organization. However, just because you CAN integrate/migrate that data into Salesforce, doesn’t mean you SHOULD.
While Salesforce is infinitely scaleable and extensible, or as I’ve often referred to it, “elastic,” there are costs to storing and accessing exorbitantly-high volumes of data. This extends beyond the actual monetary storage costs – after all, Salesforce is billed based on volume and permission limits – but also performance costs. Running a report that returns millions of records, as you might expect, can take a long time. It may unnecessarily tax resources, including system resources to render such a report, but it’s also inefficient from a background server processing perspective. To be sure, it’s important to have a historical picture of your organization’s constituent interactions, donations, and insights; but it’s important to approach the achievement of that picture strategically.
What is more practical is to be thoughtful of such considerations and think about the long-term goals of your implementation or integration project, while looking at it through a more strategic lens. There are a number of ways to strategically think through how best to maximize performance, cost, and your overall investment in Salesforce. Things like archiving data, using roll-ups, mashups, as well as doing some summarizing BEFORE migrating (in a migration scenario) your data. For instance, let’s say you’re moving from Raiser’s Edge to Salesforce, and you have 20+ years of historical data. That can be voluminous! It would make more sense to come up with a convention for summarizing maybe the first X years of history, and keeping only the most recent few years. This will vary from organization-to-organization, but thinking through these sorts of things can make your data project far more streamlined. It can also help with performance assistance, as mentioned above.
Another example includes peer-to-peer fundraising and events. Suppose you’re an organization that has generated revenue from peer-to-peer events across multiple locations, including multiple affiliates. Even with just the many affiliates, the data volume is going to be large; but you also have data regarding the event itself, and the participants, donors, teams, donations, registrations, and so-on. This is yet another potentially gigantic data volume scenario. If an organization were to consider summarizing some of this data, or grouping it in such a way that it was streamlined, that organization would again likely have a better Salesforce experience.
Even after an organization has summarized & streamlined data prior to migration, it can still be a huge volume of records. Let’s look at some of the methods & strategies to optimize performance, more specifically:
- Using Mashups: Instead of moving all of your data into Salesforce, consider an approach of storing that data externally – either in its original source system, or in a data warehouse, You could also integrate an external website and access it as needed. This is done either through exposing an external website in Salesforce, or through callouts to an external system. This way, your data isn’t ever “stale.” It’s not a perfect solution, but it is a great option.
- Deleting Data: There are definitely cases where the data is no longer relevant, or is redundant, and can be deleted. Salesforce supports “soft deletion,” which means moving the data to a recycle bin, where it remains resident in the Salesforce database for 15 days before a hard delete. It also supports “hard deletion” through the Bulk API, which bypasses the Recycle Bin altogether. Both methods should, of course, be well thought-out, before action is taken.
- Skinny Tables: Salesforce can create skinny tables to contain frequently used fields and to avoid joins. For each object table, Salesforce maintains other, separate tables at the database level for standard and custom fields. This separation ordinarily requires a join when a query contains both kinds of fields. A skinny table contains both kinds of fields and does not include soft-deleted records. Skinny tables can be created on custom objects, and on Account, Contact, Opportunity, Lead, and Case objects. In addition, they can enhance performance for reports, list views, and SOQL1
- Indexes & Index Tables: Salesforce supports creating indexes of fields, tables, as well as External IDs, to speed up queries. These can be enabled and created through Salesforce support, and can significantly increase performance with large data volumes, and commonly-executed queries.
This is by no means a comprehensive list, nor does it include all of the caveats, details, or methods to employ such methods, but it provides examples of commonly-used techniques and tools to maximize performance, should an organization need to maintain large volumes of data, even after streamlining data prior to migration or implementation.
For more information, see the following document from Salesforce, which provides more information and examples of ways to maximize an organization’s investment in Salesforce. Choose your implementation wisely, as it requires diligence in evaluating and analyzing an organization’s unique scenario and needs, both present and future.
You may also be interested in reading: