In this blog post, I will share my recent experience leveraging Power BI (PBI) to perform analysis on Customer Relationship Management (CRM) data captured using Salesforce, such as performance trends and opportunities for a client.
Our client primarily lives in this CRM and has custom reports created using standard and custom objects and fields. The organization’s Salesforce administrator exports quarterly reports to Excel. My goal was to recreate these specific reports using the built-in Salesforce connectors within Power BI Desktop.
Power BI currently has two native Salesforce connectors:
Salesforce Objects (standard and custom) – In Salesforce, objects are essentially tables containing related fields or columns. Examples include opportunities, leads and tasks. These objects can then be pulled into the Power BI data model for reporting and mapping.
Salesforce Reports – Users can also create custom reports within Salesforce which can be connected directly from Power BI. The value in connecting directly to a report is that it will already contain the objects and fields needed for your analysis, cutting the need for some data manipulation and relationship mapping when modeling data.
It is important to understand and be aware of the limitations of each connector type and how they may have an impact on achieving your business objective. Here are a few limitations of Salesforce’s Objects connectors included in the Microsoft documentation:
- Salesforce session settings can block the Salesforce.com (SFDC) to PBI integration. Ensure that the Lock sessions to the IP address from which they originated setting is disabled
- There’s a limit on the number of fields a query to Salesforce can contain. The limit varies depending on the type of the columns, the number of computed columns and so on
- Custom fields of type “Picklist (Multi-Select)” are not supported by “Create record” and “Update record” operations
The Microsoft documentation also outlines some limitations in the Salesforce Reports connectors, which include:
- The number of rows you can access in Salesforce Reports is limited to 2,000 by Salesforce
- Salesforce trial accounts do not have Application Programming Interface (API) access
The Challenge
In my situation, because the client had custom reports that they exported into Excel showing the prior three months, my initial instinct and course of action was to use the Salesforce Reports connector as it would bring in everything I needed. I was incorrect in this assumption.
The reports connector creates a real-time connection from Salesforce to Power BI Desktop, bringing in all objects, fields, and parameters used in that report. Each time a refresh is made from within Power BI, it outputs what is shown in Salesforce in real-time with those parameters. Since our client essentially takes a snapshot in time and exports it to Excel, this connector would not work as the date parameter used in the report only goes back 90 days as of the current date. I also ran into the hurdle of record limitation as their records exceeded the 2,000 record limit.
Our Solution
My solution was to use the Salesforce Objects connector and begin to identify the objects that contain the fields with the needed values to recreate the report from within Power BI. The process of locating custom objects and fields within Salesforce does take additional digging around the Object Manager to identify matching API names to custom fields and object names. A good tip here would be to link up with the Salesforce administrator or other organization personnel that understands the Salesforce data. Once I was able to locate the objects and fields in Power Query, I was then able to match the parameters used in the Salesforce report in Power BI Desktop using page filters and slicers. I also created a custom calendar table and created the relationship to the date filed used in the Salesforce report enabling filter context and the ability to filter by the prior three months. Check out my blog Unlocking the Power of DAX in Power BI for Data Modeling where I discuss filter context in more detail! Though it took more time poking around behind the scenes using the objects connector, I had more flexibility for what I was able to pull into the data model without record limitations.
Conclusion
Using the built-in Salesforce connectors in Power BI is a great way to tap into your CRM data and perform in-depth analysis to find trends, anomalies and outliers and make important decisions about your business with speed and accuracy. Understanding the capabilities and limitations of both the objects and reports connectors will allow you to better formulate a strategy to extract, transform, and load (ETL) your Salesforce data into Power BI. Additionally, understanding the data source (in this case, Salesforce) and the role of the administrator or knowledgeable personnel who oversee the environment as the “go-to” for questions is an important step that can save your organization a lot of time and duplicated work.
Contact Us
Capture the real power of Power BI’s integration with Salesforce and other CRMS! Contact a member of Withum’s advisory team today.
