Mastering Salesforce Data Relationships: Using Schema Builder for Effective Reporting

Sales Operations needs to understand the company data model before being able to create basic reports and dashboards. When you install Salesforce, there are few standard objects like account, contacts, and opportunities. As the company grows, you’ll need to create dozens of custom objects, and the data model will get harder to understand. The best way to understand Salesforce objects relationships is the Schema Builder.

How to grant access to the Schema Builder?

As I write, it still requires the permission “Customize Application”, which allows you to create and delete fields and objects. Only a few CRM administrators should have this permission, and you don’t want to grant this to your Sales Operations team. I suggest you create a dedicated sandbox for this purpose so the Sales Operations team can check the Schema Builder.

How can the Schema Builder help Sales Operations?

Let’s take an example. A sales representative needs visibility concerning his renewal opportunities. He needs a report with information concerning opportunities, the primary contact, the account and the assets that need to be renewed. Unless Sales Operations fully understand the data model, they won’t be able to create this custom report.

The first thing you should do is asking this question to the person who wants the report: what does one row represent? In this case, is it an account, an opportunity, a contact or an asset? The sales rep will probably answer “one opportunity” as he’s looking for insights about his renewal pipeline. Thus, looking at the Schema Builder, you know that one opportunity can have many assets as the “Renewal Opportunity” field on the Assets object creates a one-to-many relationship between the Opportunity object and the Assets object. Hence, so you need to create a custom report type on opportunities with assets.

An opportunity can only have one account and one primary contact, so you know you’ll be able to add account fields and contact fields via lookup. Plus, your report is going to be a summary report as a tabular report will show one asset per row. Each opportunity will have a related list of assets.

Best practices concerning lookup fields

You probably noticed that I named the lookup field on the asset object: “Renewal Opportunity”, which is quite straightforward. I also named the “Related List Label”: Assets (Renewal Opportunities), so it’s easier for analysts to create custom report types. I believe that having a naming convention for Related Lists Labels is important; something like “Child Relationship Name” & “Field Label” is quite all right.