Building a Data Integration Proof of Concept Using Lightning Connect

by Sumit Sarkar, March 2015


Lightning Connect, which was introduced in Winter ’15, offers new data-integration capability, and provides access to data from external sources with point-and-click simplicity. Now you can incorporate data from systems such as SAP and Microsoft SharePoint in real-time as External Objects, dramatically reducing integration time to unlock and modernize back-office systems. This article chronicles the first-hand experiences of a citizen developer (me) connecting my organization’s Sales Cloud with on-premise corporate business systems to create related lists of invoices grouped by account, and accessed in real-time.  Prior to Lightning Connect, this seemingly simple task was well beyond the scope of Salesforce developers and admins.


Building My First Lightning Connect POC

In the spirit of the Salesforce1 Platform, my project will be completed using point-and-click configuration from end to end.  My external data source will be connected using DataDirect Cloud as suggested by our Salesforce Account Team. My integration experiences apply to any corporate business systems or databases. From talking to folks at my local Salesforce User and Developer Groups, the most popular data sources include Oracle EBS, JDEdwards, Microsoft SQL Server, SAP, Siebel, Peoplesoft, QAD, Banner, to name a few.


Day 1: Request Connection Information for POC

Time: 15 minutes

Level of Difficulty: Easy

Contacted Progress Software IT and Sales Operations with my intent to do a proof of concept (POC) for Lightning Connect to integrate invoices from our system of record behind the corporate firewall with our Sales Cloud as an External Data Source.  For Phase 1, I was advised to get the invoices from a development data warehouse environment, rather than our ERP system. The steps for Phase 2 connecting directly with the ERP will be similar.

IT was not familiar with Lightning Connect, but Sales Operations learned about it from Dreamforce ’14.  Everyone was very interested in the final write up.

Day 2: Data Model Relationship Planning

Time: 15 minutes

Level of Difficulty: Easy

My credentials to the data warehouse were created and I set up a quick call with our Sales Operations analyst who provided the entities in the data warehouse that contain invoice data, which happen to be “Invoice.”

Field name Filter criteria
Invoice_SoldTo = account.Customer_Number__c

 


Day 3: Get Started with Developer and Trial Accounts

Time: 60 minutes

Level of Difficulty: Easy

I contacted our Salesforce Account team for Progress Software to request an evaluation of Lightning Connect, and they requested our production organization ID.  In the meantime, I created a developer account in Salesforce that comes with Lightning Connect (limited to 1 external data source) at https://developer.salesforce.com/signup.

Next I created a trial account in DataDirect Cloud (http://www.datadirectcloud.com) to produce OData from our on-premise data warehouse for Lightning Connect External Objects. You can see the main DataDirect Cloud home screen in Figure 1.


Lighting1.png

Figure 1: The main screen for DataDirect Cloud


In our case, the data warehouse is built on Progress OpenEdge and I specified the connection information provided by IT and named the data source “ProgressEDW” as shown in Figure 2.  The “Connector ID” value is generated from the on-premise connector downloaded from DataDirect Cloud, which establishes a secure connection behind our firewall.  The steps will be similar for other applications or databases such as SQL Server or Oracle.


Lighting2.png

Figure 2: The log-in screen for Progress DataDirect Cloud


From here I clicked the OData tab to select the entities I wanted to expose to Salesforce from the data warehouse (tables or views), as shown in Figure 3.


Lighting3.png

Figure 3: Selecting the entities to expose to Salesforce


From here, I log into my Salesforce Developer account (see this tutorial for details) to configure the external data source using the following steps:

  • Click Setup > Develop > External Data Sources.
  • Enter Label and Name and select Type of Lightning Connect: OData 2.0.
  • Enter URL as https://service.datadirectcloud.com/api/odata/ProgressEDW (ProgressEDW is the name of the DataDirect Cloud data source configured above.)
  • Specify Identity Type as Named Principal and Authentication Protocol as Password Authentication, and enter the DataDirect Cloud credentials.


Lighting4.png

Figure 4: Creating new external data sources


  • Click Save.
  • Click the new External Data Source and click Validate and Sync.
  • Select the entities and click Sync to create external objects.

 


Lighting5.png

Figure 5: External objects from the data warehouse


 

Now I have external objects for Invoices and InvLines from the data warehouse, as shown in Figure 5.  It’s so cool to see the external object for “Invoices” and the mapped columns from the database to custom fields.  It really does seem like “magic” as shown in Figure 6.

 


Lighting6.png

Figure 6: External objects with mapped columns from the database to custom fields


 

That was easier than I would have expected!


Day 4: Building Related Lists in Developer Sandbox

Time: 60 minutes

Level of Difficulty: Intermediate

I ran into an issue creating a related list with external objects by following this tutorial from tquila.  So I posted my question to the Salesforce community and received an answer that same day from Carolina Ruiz Medina, Principal Developer at Financial Force. https://developer.salesforce.com/forums/ForumsMain?id=906F0000000AyT7IAK

I was a little surprised at the speedy reply since Lightning Connect is relatively new.


Day 5: Migrating the POC From Development to Sandbox

Time: 60 minutes

Level of Difficulty: Easy

Then I received a note from James Green at Salesforce that Lightning Connect has been successfully enabled on our org.  I requested Progress Software Sales Operations create a new sandbox since the feature cannot be directly enabled for existing sandboxes.

My new sandbox was then ready to go, thanks to SalesOps:

https://cs15.salesforce.com

I double-checked creation of an external data source and verified the type “Lightning Connect: OData 2.0” is available, which means it worked; and I’m no longer limited to just one as in my developer account!

I repeated the previous steps to create the external objects in my new sandbox with real data (instead of my friends at Burlington Textiles Corp of America).  Now to create a related list of invoices by account object,  I located the Customer_Number__c field recommended by SalesOps.


Lighting7.png

Figure 7: Creating a related list of invoices by account object


Next I clicked the “Invoices” external object shown in Figure 8 and created an indirect relationship between Accounts.Customer_Number__c and Invoices.SoldTo__c.  I then created a related list from the Account page.


Lighting8.png

Figure 8: Creating a related list from the Account page.


This is so cool -- and the invoice data remains physically stored behind our firewall.


Day 6: Show and Tell

Time: 30 minutes

Level of Difficulty: Easier than expected

The business unit invited me for a prime slot to demo the POC at the Quarterly Business Review. Even though it’s pretty awesome already, I added the Salesforce1 app for Chrome to simulate mobile access to data in our on-premise system of record, as shown in Figure 9.


Lighting9.png

Figure 9: Simulation of mobile access to our on-premise SOR using the Salesforce1app


The sales executives were completely blown away since they’re struggling with integration of this very same data using Tableau Reporting, as well as figure out how to host the reports and pass appropriate authentication/filters down using the Canvas API (not a job for sales executives, no offense).

 


Summary of My Experiences with Lightning Connect integration

This has been a great experience for me as a citizen developer to collaborate with other business units.  And it’s really exciting that data integration can be self-service and point-and-click in the spirit of the Salesforce platform.  This means the Salesforce devs and IT professionals can take their innovation to the next level without having to spend grueling sprints on data integration.

The features of Lightning Connect got the attention of key leaders in the company, making me look like an expert on the latest technologies (even though I don’t really know anything).  My presentation was featured in a Quarterly Business Review and have demos lined up to IT and SalesOps leadership, and even our CTO recognizes my name now.

Starting with Lightning Connect, I now view the Lightning Components in a new light, such as the new process builder introduced in Spring ’15.  Now, through the use of external objects, the potential is unlimited with all this access to data.

One of my favorite achievements from this experience is my ability to share my story with the Salesforce Developer community.

Resources

About the Author

Sumit Sarkar is a systems engineer at Progress Software new to developing on the Salesforce platform.  He has presented four Dreamforce sessions on data connectivity across industry data standards such as ODBC, JDBC, and OData. Contact Sumit Sarkar:

www.linkedin.com/in/meetsumit