The native connectivity between Salesforce and CRM Analytics makes it easy to bring data from Salesforce into CRM Analytics. At the start of any CRM Analytics project, the first step is to select all Salesforce Objects and their respective fields within local connection that are needed for reporting, which then can be used for building datasets using recipes and dataflow (currently available, but may be retired soon).
In the initial stages, it’s common to feel that we need more fields for the dashboard design than actually required and we end up selecting several fields from each of the objects within the local connection. However – the more the fields, the more data is being pulled into CRM Analytics, which will result in longer time for the local connection sync to run and update data in the data layer.
If the objects and fields are not documented, it will be difficult to revisit the recipes and dataflows to deselect any fields that are not used on the dashboard or transformation within recipes and dataflows.
Now, let us review two approaches (and why one approach is the better way to go).
Approach 1 – Objects and fields are not documented
The process may feel like you’re taking the easy route, by not maintaining a document with objects and fields brought in using local connection. However, it can soon become challenging when you notice that the local connection takes longer to run. At that stage, one may have to go through all the dashboards, noting all fields from all objects and comparing the fields that are selected in the recipe and dataflow nodes. If there are any fields from any objects that are not used on the dashboard, then the fields need to be deselected from the recipe and local sync.
The process of getting all the fields from different objects used on the dashboard may sound easy, but it is a very time consuming task. The complexity increases when there are many Salesforce objects used within the dashboard, and each dashboard has many queries which are built with SAQL. The process is further complicated when there are many dashboards that use many different datasets.
Once the entire list of fields from all objects is obtained from all the dashboards, it needs to be compared with the fields used in all input nodes within the recipe and dataflow. After that, unused fields need to be deselected from the column list within the recipes and dataflow nodes, finally from local connection.
We’re tired just thinking about it.
Approach 2 – Objects and fields are documented using ERD tool
At the start of CRM Analytics projects, we highly recommend that you list all the Salesforce objects and fields that will be required for dashboard design and derived fields. An ERD (Entity Relationship Diagram) Tool allows you create a visual model of your database schema. Using an ERD tool, you can create a data flow diagram similar to the flows in recipe and dataflow.
For example, if you are building an Opportunity Dashboard, you may use fields from Salesforce objects: Opportunity, Account, User and UserRole. It will be helpful to have a flow built in any ERD tool listing all fields used, such as the example shown below:
After the dashboard is built and approved by the user, the fields used on the dashboard can be compared easily with the data flow diagram. This allows you to check for scope, remove any fields that are not in use, and update the flow diagram with fields removed.
Remember that the data flow diagram should always be updated, whenever any new fields are added to be pulled using local connection.
There are many options out there for ERD Tools. You may want to try a few out to see which one works best for you.
Summary
When creating a dashboard – sometimes having fewer fields leads to a more effective report! Having an ERD data flow diagram at the start of CRM Analytics projects gives more clarity about the fields (columns) that will actually be used in the recipe/dataflow nodes, and improves the speed of the local connection job, making your processes more efficient.
Have questions about Salesforce and CRM Analytics? Want to chat about your organization’s Business Intelligence goals? Get in touch! We would love to hear from you.