Firstly, my genuine apologies are due to many past clients! It seems that all of those ERP integrations I have implemented over the years, nightly batch jobs, middle-ware syncronisations and roll-yer-own data-syncs… were all sub-optimal solutions! I’m sorry!
Salesforce aligned with OData (Open Data Protocol) about a year ago with its introduction of External Data Sources and External Objects, before this, building large scale ERP data synchronizations was the norm. More than that… it was an advanced integration, a task that I always welcomed, it often required the selection of some form of enterprise middle-ware platform and much detailed data-flow design. The final solutions delivered incredible alignment between Salesforce and back-office systems that allowed businesses to finally begin to align sales and operations and close the data divide that was present in most organizations between front and back office platforms. But it was no-where near real time, the data was old as soon as it was updated, in today’s faster moving world the demands are greater!
At westbrook I have been working recently with some key clients that had previously implemented sub-optimal processes that bring a little bit too much data into the salesforce environment than is considered ideal. These back-office integrations have been replaced with standards based OData, external data access solutions.
What is OData and Salesforce Lightning Connect?
OData is the open protocol that describes the schema of a data source or database, it can be implemented over many different database types and is implemented with a restful approach. From the perspective of our use, it allows access to multiple external data sources through a reliable standards based approach. It represents an interface to our external data, somewhere behind the OData service is an actual data source or database.
Lightning Connect is the term that describes the overall setup that includes External Data Sources and External Objects.
External Data Sources are the configuration element in Salesforce that defines the connection to an OData service, here we define any connection attributes and the endpoint of the OData interface onto the data.
External Objects are the salesforce custom objects that are created automatically from the OData schema document. You can add additional custom fields to these if needed. These external objects have the naming convention Object_Name__x, and although they do not store any data in salesforce they look and feel like other custom objects that do.
A large industrial wishes to display sales order history from ERP in Salesforce related to its active accounts, this provides valuable information for the sales and account management teams.
Backend datastore, for this we will use an traditional relational database, a cloud based PostgreSQL hosted on Amazon RDS.
OData interface, salesforce is not the OData service provider and its not baked into most databases, so we need an interface, this can be something you host on your infrastructure. I am aware of an open source PHP OData connector, and I’d imagine others are available. For cloud simplicity here I am using the DataDirect Cloud service from Progress Direct, its a paid service but takes away the cost of build, host & maintain of your own connector. They offer a 30 day free trial.
Data, within the SQL database we have around 1 million transaction and also the full product reference of around 1,000 products, the transactions all contain a customer account number identifier that we will use to map to the salesforce account record.
Pre-requisit, you already have access to a backend SQL data source, if you are just prototyping spin up a Amazon RDS PostgreSQL database, note the MySQL community edition is not supported for OData through Progress, and I had trouble setting up a MSSQL instance (but that might be me… or MS!).
Setup DataDirect, Signup at progress.com/datadirect-cloud to get started. Enter the typical connection information to create your data source. Then on the OData tap we use the built in tool to generate the schema json. Save and we are provided with the OData serivce access URI, similar to this: https://service.datadirectcloud.com/api/odata/RdsPostgrSql, this service is authenticated with DataDirect Cloud credentials.
Setup Salesforce External Data Source, Setup->Develop->External Data Sources, we create a new data source, specifying a type of Lightning Connect: OData 2.0, a service URL that is the DataDirect access URI we got earlier, and also specify the Authentication details as, Named Principle and Password authentication. Leaving all others as defaults (the Format provided by DataDirect is AtomPub).
Generate and sync the External Objects, pressing Validate and Sync will present you with note the “sync” part here refers to synchronizing the data schema and not the actual data, as of course no data is stored in Salesforce.
Setup user access, just like a custom object provide object and field level access to the users you want through profiles or permission sets. Additionally create a custom tab so you can quickly access the object and use list views. For my Transactions external object, there are around 1.2 million transactions, and not a single row counts against your salesforce data storage quota!
In my example above I have changed the Product_Code field to be an “External Lookup” field, which is like a regular look-up field but to another external object, this means we can drill to the product detail from the transaction level and see a transaction related list on the Product page.
Also I have changed the Customer_ID field to be an “Indirect Lookup” field, this is a really exciting new type (which I am waiting to be included as a standard field type on regular objects), it performs a dynamic lookup to another object but using an external Id field rather than the sfdc standard Id field. Kind of like an old-fashioned primary-key-foriegn-key relationship in a relational database! This is important because most back-office systems are not Salesforce aware and so the data will not have embedded salesforce Ids. The dynamic part of this field is that it will lookup to the related object if it finds a matching external Id, but if there is no match the data will still be available, this wouldn’t be possible with a sfdc Id relationship. It also gives a reverse lookup capability, where for example all transaction data may already be available through the integration, but the parent account is not in Salesforce. When eventually the account manager creates the account record and enters the Customer ID, all historic transactions for that account will instantly be related. Nice!
Video setup and demo
Some additional limits and considerations need to be understood around this feature of course…