Salesforce External Object Reports using OData

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