Data Modeling to Facilitate Reports

The report engine in Salesforce relies on the relationships that are explicitly defined in order to join together data from multiple objects. This means your data architecture determines what native Salesforce reports will be capable of producing. Here are four concepts to consider for data modelling that will allow you to build the reports you desire.

1. Conceptualize your data model as a hierarchy

Data architecture is referring to how your data is related to each other. In Salesforce, the master-detail and lookup fields you define allow you to relate a record to another record. Records about your customers and records about your organization are very important. Many of the other records you store in Salesforce will need to reference either something about your customer or something about your organization. Given their importance, these objects and records should be positioned somewhere near the top of your data model. Any details related to these records should be stored on a layer below these two objects – often times joining together your customer and your organization.

Think about a few standard objects in Salesforce – accounts, opportunities, and users. An account record represents your customer. Each time they plan to buy something from your organization, an opportunity record is created. That opportunity record is owned by someone, a user (perhaps a sales representative), at your organization. This opportunity record has created a junction between your customer and your organization – one that is specific to a certain sale on a certain date for a certain amount proposed by a certain sales representative.

2. As you work your way down the hierarchy, your records should be getting more and more specific

The details of that sale sit lower in the hierarchy. There may be many products that were sold to that customer by that sales representative. Here we see another junction situation – we need to associate the products our organization sells with this specific opportunity. The product object keeps track of what our organization sells, and the “opportunity products” (or “opportunity line items”) object relates those product records to that sale. From a single opportunity product record, we can work up our data architecture hierarchy in order to understand how this record relates our customer to our organization.

Following the opportunity relationship, we know when the sale was made, what the total amount was, the stage this sale is in, and any other details on the opportunity record. Going two levels up – traversing the opportunity relationship and then the account relationship – we can then understand which customer purchased this product. That account record also stores contact information for the account, what state they are in, summary information about previous sales, and any other details on the account record. If we go back to that opportunity product record and follow the product relationship, we know what product was sold, what family it belongs to, and any other details on that product record.

Because each of these records at the bottom of the architecture is so specific, there are generally more records needed to support storing all the necessary information. The result is a pyramid-shaped set of data. For any one customer account record, there are many opportunities. For any one opportunity record, there are many opportunity products. You will also likely have other detail records nested underneath the account record. There are many contacts related to that account. Then as you need to relate the contacts to opportunities, you use a junction object like opportunity contact roles to store the many relationships between contact records and opportunity records. Everything becomes related in order to support all the details for the record at the top of the hierarchy. 

3. Use relationship fields

The further you build out your architecture to suit your needs, keep in mind that you will likely want to reference something about your customer and/or something about your organization. The key is to relate these new objects to your existing structure. Use lookup fields if you are defining an optional relationship. Use a master-detail field if you want to tightly couple two objects together and allow for the parent object to control some aspects of the child object. If you try to store information about another record on an object without using a relationship field, you are setting yourself up for a disconnect in the future. Remember – the Salesforce reporting engine can only join objects together when an explicit relationship exists. Be on the lookout for fields storing a text value of a record ID, or a unique identifier that identifies another record in your instance. These fields should at a minimum be paired with a relationship field that actually relates the two records together. It’s even better if you can eliminate the text field and replace it with a relationship field instead.

Some organizations are organized by location. Some have different divisions. You will likely want to relate your sales, or your customers, or products, or some aspect of your business to your organizational structure. Using records and relationships to facilitate this will make it easier to add attributes to those records. Your locations will have different addresses, may have different hours, may be in different time zones, and you will want to make decisions based on that data. You may have a need to generate a report for records related to a certain division. Actually having these records in place will make that reporting request a breeze. Flesh out your organizational structure and be sure to relate your operational processes to that data as needed.

4. Create Consistency

Knowing what to expect in your data will make it easier to plan how you will design and build a report. There are likely many scenarios in your organization that have similar needs and just differ on a few key points. If you can identify these similar situations and find a common solution to address them, not only will you improve the development time needed to implement the solution, you will make it easier for users to understand the system and create the reports they need based on that data. This often comes up when designing a solution for payments/transactions. Think of all the ways your organization facilitates transactions. You may be accepting credit card payments – some automated and some input by users. You may have invoices that are paid by check. You might be accepting cash payments. All of these situations are similar in that they involved a transaction of currency. When designing a solution, think of how you will want to report on this data. You will likely want to know how much money you’ve received no matter the type of transaction or source. It becomes very important to use a single object to store the records for your transactions. Certain AppExchange packages may have their own objects for tracking transactions or payments, so you may need to utilize automation to ensure all of these records are triggering the creation of a record in your master “payment” or “transaction” object. Coupling this single source of truth with the previous topic of relating your data to your customer and organization appropriately will allow you to create fantastic reports.

Another area where consistency is important is when you allow for multiple ways to relate records together. Take opportunities and contacts for example. Many organizations may be using opportunity contact roles to keep track of which contacts are involved with an opportunity. Others may set up a lookup or master-detail field on the opportunity to identify the primary contact. Some organizations do both. This is all fine, depending on your needs, as long as you create consistency somewhere. When the majority use-case is having a single primary contact for an opportunity, but there are a handful of situations where multiple contacts are related, find a way to automate having all the contact-opportunity relationships stored the same way across all records. Generally this means automating the creation and sync of the record in the junction object, which will allow you to rely on those junction object records anytime a report question comes up surrounding “which contacts were involved in opportunities”. Taking the time to address this will pay huge dividends in the long run, or else you’ll find yourself resorting to multiple reports or joined reports to answer what should be a simple question.

Putting it all together

As you plan to create a report, think about what level of detail you want to see for your data. Do you need a list of account records? A list of the many opportunities related to those accounts? Or maybe you need even more detail – a list of opportunity products that are related to opportunities, which are in turn related to accounts. The right level of granularity allows you to summarize the detailed information you need from a set of records and reference values from records that sit higher in the data architecture hierarchy. With customer data and our organization data serving as two pillars for our architecture, you will be able to answer any question that is asking for details about how your organization interacts with customers.

Related Trailhead Modules

• Data Modeling
• Understand Sales Cloud Data Models