Admins

Salesforce External Object Reports using OData

By Sumit Sarkar

Salesforce shops, including ourselves, have been eagerly anticipating external object support with reports. Starting in Winter ’17, you can build native reports with on-demand access to external data sources such as Oracle, SQL Server, or Amazon Redshift. External objects are powered by Salesforce Connect and provide clicks-not-code data access for admins, devs and general users.

And the data that gets connected to Salesforce is only ever accessed in real-time, so we never make a copy of the data in Salesforce.

What are External Objects?

In Salesforce, data is stored in what they refer to as Objects. There are standard objects like Accounts, Contacts, Opportunities and Cases that come out of the box with the Sales Cloud, Service Cloud, Community Cloud and App Cloud. Developers and admins can also build Custom Objects to track data like Projects, Milestones, Timesheets, Budgets. Salesforce Connect introduces a 3rd type of object, an External Object.

External Objects act much like Standard and Custom Objects, only the data isn’t stored in Salesforce, the data is stored in an external system like SAP, Oracle, Workday or Amazon. Salesforce Connect calls out to external OData endpoints, an industry standard for building and consuming RESTful APIs, and gathers information about the data structure it exposes, and then stores metadata within an External Object.

How Reporting works with External Objects

Salesforce users can now run reports that access external data from cloud and on-premise sources including warehouses, data marts, data lakes and more. With Salesforce Connect external objects, you get real-time connectivity that is self-service for Salesforce Report developers.

Who Does What?

Salesforce Connect:

External Object Reports is a feature of Salesforce Connect. Salesforce Connect is an offering from Salesforce that provides seamless integration of data across boundaries.

OData endpoint:

External objects require an OData adapter for reporting databases, and this is provided by Progress DataDirect. My shameless plug: Progress was the first member to join the OData technical committee and provides connectors distributed by eight of the top nine leaders in Gartner’s Magic Quadrant for BI.

Reporting Considerations

There are various reporting considerations when dealing with External objects in Salesforce. I’ve listed these at the bottom of this post

Getting Started With External Object Reporting

Notes: Tutorial assumes an external data source was created using DataDirect Cloud. A self-hosted version of the Salesforce OData service is also available as Hybrid Data Pipeline.

Setup Home > External Objects > Edit

Select “Enable Reports” on the external object.

Enabling reports creates the following in the Other Reports report type category.

•A report type for the external object

•A report type for each lookup relationship in which both objects allow reports

Custom fields from external object “Orders” displayed as dimensions and measures..

Blended Standard / External Objects

Setup > Custom Report Types (Optional)

Combine external objects with standard objects and categorize for easy access..

Orders has indirect relationship on Customer ID with Accounts

Now we have a report blending standard Salesforce objects with external objects stored outside the platform. When I close the browser, the external data is not stored in the platform and only accessed on demand when running the report.

This feature is not necessarily designed to replace your existing enterprise reporting platform, but it’s a great way to augment Salesforce data and make it available to users who spend a lot of their time in the platform. My employer will be interested in integrating some of our data warehouse information with Salesforce reports.

The most popular reporting databases we’re already getting requests for include:

• Oracle [Exadata]

• Microsoft SQL Server

• Greenplum

• Amazon Redshift

• IBM DB2

• Hadoop Hive (including Cloudera, Hortonworks, IBM BigInsights, and MapR)

Resources

Related tutorials from Salesforce Devs:

Salesforce Developers guide to hybrid connectivity

Trailhead for Salesforce Connect

Related webinars from Progress:

Webinar: Configuring Salesforce External Object reports [Guest: Salesforce]

Webinar: Integrate Oracle Data Sources with Salesforce Connect [Guest: Appirio]

Getting Started

OData services for Salesforce Connect

Reporting Considerations

• When you run a report, your org performs a request callout for each external object in the report.

• When you run a report that’s in the joined format, your org performs separate request callouts for each block.

• If the URL length of a report callout approaches or exceeds 2 KB, your org splits the request into multiple HTTP calls, with each URL being less than 2 KB. However, with the cross-org adapter for Salesforce Connect, report callouts that exceed 2 KB fail and are not split into multiple calls.

• When a report includes an external object, the report fetches up to 2,000 records for the primary object. This limit applies regardless of whether the primary object is a standard, custom, or external object.

• If the report has no child objects, the total number of rows is 2,000 or fewer.

• If the report has a child object, the total number of rows can be greater or less than 2,000, depending on how many child records are fetched.

• As is true for all callouts for external objects, report callouts are limited by the Salesforce Connect adapters in use.

• For large external data sets, report callouts typically access only a subset of the external data.

• If the report results in few or no rows, try customizing the report to obtain more relevant external object rows.

• If the report includes summary fields and formulas, those aggregate values likely reflect only a subset of your data. To improve the accuracy of the aggregate values, try customizing the report to obtain more relevant data.

• The external objects’ associated external data sources must have the High Data Volume option deselected. This requirement doesn’t apply to the cross-org adapter for Salesforce Connect. • Cross filters don’t support external lookup relationships. An external lookup relationship links a child standard, custom, or external object to a parent external object.

• These report features aren’t available for external objects.

• Buckets and bucket fields

• Historical trend reporting

• Reports can include or reference external objects via lookup, external lookup, and indirect lookup relationship fields. If those relationship fields aren’t Salesforce IDs or the name fields of parent objects, the report returns only empty values for those relationship fields

The Author

Sumit Sarkar

Sumit Sarkar is the Chief Data Evangelist at Progress with expertise integrating Salesforce with external systems.

Comments:

    Francis Pindar
    June 30, 2017 3:11 pm
    Also an important limitation is to understand that Salesforce assigns Salesforce IDs to the external object records but these are only temporary assignments and could change. So use the foreign key id's on the records and not Salesforce IDs for the external object records.
    Francis Pindar
    June 30, 2017 3:14 pm
    I did a talk on External Objects at Dreamforce last year. Everyone thinks of External Objects as talking to data sources OUTSIDE of Salesforce but never about looking inside. Eg want to create a report on all deleted opportunity records in the Recycle Bin? or all manual sharing rules on a particular object? with External Objects you can: https://www.youtube.com/watch?v=2cTfkhKsvIs
    Ben McCarthy
    July 01, 2017 10:53 am
    Great insight, thanks for that Francis.
    david
    July 05, 2017 3:56 pm
    Great feature, unfortunately not many companies can afford the $4000+ a month
    kevin
    April 04, 2018 5:51 am
    Hello There, You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide. I am new to payroll module. Could you please tell me How to see the net amount to be paid to each employee in a particular period. This report will run after the monthly prepayment and payment process run. I want the report in below format employee name, salary amount, remitters name, remitters account ----------------- ---------------- ---------------- --------------------- or employee name, salary amount ----------------- ---------------- this much information will be good for me.I look forward to see your next updates. Gracias
    Frank Mamone
    August 02, 2019 6:34 pm
    Very cool...I'll need to report on a Parts database. I guess if it's more than 2000 records, I'm out of luck? Don't really understand the bit about child records which seems to have no limit.
    Mike
    January 31, 2020 10:24 am
    Totally agree but it depends. Everything related to Salesforce is very expensive. And this feature can potentially save you a lot of money if you don't want to pay Salesforce for storing historical data. Our org accumulated about 300GB of data so far and we decided to replicate the historical data (about 200GB) to a database for storing and further analysis. Then we used Salesforce Connect to partially load data we needed as external objects. Both scenarios we implemented with the single service - Skyvia (https://skyvia.com/connect/salesforce-odata).

Leave a Reply