ETL is a powerful and popular tool for migrating or transforming data that doesn’t change often. There are a lot of ETL tools in the market for data extraction and they will work only for the data sets that don’t often change. In other words, ETL doesn’t work well if the volume of the data is high and if the data changes often dynamically. Fortunately there is an alternative; external data source is one of the newest additions to the force.com integration toolkit. This is a technology that enables new integration paradigm in which you don’t have to move the data and you can establish reference to the data from Salesforce org. This technique allows you to focus on the system interaction rather than physical data movement.
External Data Source and External Objects:
What is an External Object?
An External Object is a custom object–like any other custom object you can use in Visualforce, Apex, SOQL, SOSL, custom tabs, and more. The main difference is that instead of pulling the data from oracle database that lives under any other Salesforce platform, the system will obtain the information you request via a form of http call outs. You construct the request, send it to Salesforce, get the response, check the status code, and then you parse the response. The response might be an XML or JSON format. In order to use the External Object in Salesforce, we need to know how the data are formatted and how queries are constructed.
To understand how the data are formatted, let us look at two types of connectors
- OData (open data): Industry standard connector to represent data thru REST
- Apex connector framework: It allows you to write your own connector to tell Salesforce how you will format the query using the SOQL and SOSL input and how to extract the fields from the response.
What can I do with an External Object?
|Standard and Custom Objects||External Objects|
|REST and SOAP API||X||X (read only)|
|Custom Tabs and Details||X||X (read only)|
|SOQL||X||X (read only, no aggregation, no tracking)|
|Search and SOSL||X||X|
|Reports and Analytics||X|
I would like to spend some time to go over how to use External Data in Salesforce.
- Create an External Data Source: Navigate to Setup | Develop | External Data Sources.
Click the “New External Data Source” button to create new external data source.
Now, you have created a data source. The data source tells us how to talk to an external Webservice. Once you have created a data source, the system will list out the table names. Now, select a table and click Sync button.
We have an external object named “SalesOrder”.
- Create an External Object.
To create an external object, navigate to Setup | Develop | External Objects and create it with required fields.
Here, you will see the SalesOrder external object which is similar to Salesforce custom object. The only difference is __x suffix on object API name. There are two standard fields: Display URL and External ID.
Display URL is kind of an identity with in the external Webservice.
External ID Is the key within the system to identify a record. External objects don’t have the standard record id, but have external id instead.
Now, open the SalesOrder object and you will see the data over there: