In this technical blog post, we’ll take a look at various options for reports and dashboards when designing a data model, considerations you should keep in mind, and best practices to ensure your success.
Objects
Standard Objects v/s Custom Objects
Designing a data model should consider object types (standard, custom, or external) along with record types, organization-wide default settings for object visibility, owners of the records within each object, and estimated number of records per object that can exist at any given period. Review available standard objects from Salesforce to document required fields, relationships, and license types.
Salesforce provides a rich collection of standard report types that you can tailor to your unique requirements. Consider the tradeoffs such as built-in security, standard report types, or out-of-box features if decided to create custom objects. Also, you would have to create custom report types for custom objects.
Relationships
Master-Detail v/s Lookup
Consider the requirements when choosing relationship types. For instance, master-detail relationships offer benefits such as out-of-the-box roll-up summary fields but they also enforce tighter links between objects such as cascade deletes of child records or enforcing security on child records. Lookup relationships, on the other hand, allow the use of platform sharing capabilities and also allow you to make the relationship required or optional.
Normalization v/s Denormalization
Normalization of the data model splits data into multiple objects that help to reduce data redundancy. In contrast, the Denormalization of the data model combines data into objects that help to speed up the retrieval process.
When designing a data model, the Denormalization approach could be advantageous with tradeoffs such as data redundancy, but with benefits such as data storage cost or optimal queries because of simple queries and fewer joins. Also, keep in mind the number of objects that can be joined in a single report.
License Dependencies
Make sure you understand and document license dependencies when designing data models. If the managed package object has a report running on an object from the Salesforce Field Service (FS), then your managed package has an FS dependency. If your package has reports running on the standard objects and FS objects, then your package installation will fail in a customer environment that does not have an FS package.
One way to solve this problem would be to create packages without license dependencies. In the above example, we can create a base package with reports running only on standard objects. Then create an extension package to add reports that would run on standard and FS objects.
Large Data Volume
Reports can show slow performance when an object has a large number of rows. You can start analyzing the data model to find Large Volume Records.
Estimating the data volume for each of the objects, if available, could help you understand or identify parent-child data skew or ownership data skew issues.
A parent-child data skew could result when there are a large number of child records (10,000 or more) associated with a single parent record.
An ownership data skew results when a single user owns more than 10,000 rows. One of the reasons for slow performance is due to implicit sharing enforced by the Salesforce platform to support collaboration. For instance, granting read-only access to the parent record for a user with access to a child record.
Designing or changing the data model to resolve parent-child data skew or ownership data skew could improve the performance.
Archiving
Archiving large data volume records would help with selectivity as well. Records could be archived on the platform using Big Objects or off-platform using platforms such as Heroku.
A Big Object stores and manages massive amounts of data on the Salesforce platform. It is performant over millions of records. To build reports or dashboards, you can use Async SOQL to query a smaller subset of the data and store the results in a custom object. You can then build reports and dashboards on this custom object.
Big Object only supports fields and object permissions, so you will have to consider the tradeoff of losing the record-level security and then re-adding it to the custom object for the respective reports and dashboards. Also, keep in mind considerations around creating SOQL queries for Big Objects. You can query the fields in a Big Object’s index using a subset of standard SOQL commands, such as including system fields like CreatedById or CreatedByDate or not including parameters such as Like, Not In.
Heroku is a cloud platform that allows you to build external applications and databases and seamlessly sync data between them and your Salesforce org, allowing you to encapsulate certain functionality. You can export the data to Heroku using an ETL tool. You can also provision PostgreSQL, which is a relational database management system to store the data in Heroku.
If you want to access archived records, you can use Salesforce Connect to retrieve them from the archive database in Heroku and work with them as external objects.
You can also use Heroku Connect, an add-on that synchronizes data between your Salesforce organization and a Heroku Postgres database. Consider the Heroku Connect option if you want to archive as well as build a custom application that interacts with Salesforce data.
Another option is to use the ETL tool to create summarized records and store them in a custom object.
You can run reports on these external objects or custom objects. When archiving to off-platform you will lose the security and sharing enforced on these objects. So you will have to consider and re-add the necessary security and sharing for the required reports and dashboards.
If the need is to design and develop analytics, then Tableau CRM could also be a very useful option. It’s an important platform if you have external data along with Salesforce data to develop powerful analytics and insights.
Custom Index
Custom Index could help to improve the performance of reports. Salesforce uses Lightning Platform Query Optimizer to effectively optimize queries by identifying the best index from which to drive the query. Salesforce maintains standard indexes to speed up queries.
Query Optimizer uses the standard index if the report filter matches less than 30% of the first million records and less than 15% of additional records, up to a maximum of one million records. You can create a custom index by contacting Salesforce support or making the field an external id. Query Optimizer will use the custom index if the report filter matches less than 10% of the total records, up to a maximum of 333,333 records.
Skinny Tables
You can also use skinny tables if you are looking to improve performance beyond what is improved using Custom Indexes. Skinny tables can optimize the querying of commonly used standard and custom fields by reducing the underlying join that is needed to return standard and custom fields. But skinny tables have a limited number of fields, can’t cross multiple objects, and can’t be enabled on a standard object such as Asset. Also, you’ll need to contact Salesforce support to enable it.
We hope this post has given you some valuable information on best practices and considerations for reports and dashboards when you’re designing a data model!
CodeScience has helped build more than 10% of the apps on the AppExchange. Contact us today to learn how we can help your business thrive.
