Lightning Connect – Visualize your external data in Salesforce easily

By Christmas 2013, I received a fantastic present. FinancialForce.com platform team was starting to take a look at one of the new pilots that Salesforce had. By that time we called it “External Data Source” or “External Objects” however, nowadays you know it as Lightning Connect.

And here I am, writing about this feature that was released during Dreamforce ’14 and after having talked about it on such amazing event, Madrid ’15, London ’15 and finally few weeks ago at Dreamforce ’15, now, it’s time to share my knowledge via a blog entry.

So first of all, what is Lightning Connect? This feature allows you to link an external repository, Google Drive, SharePoint, Amazon and others, to your Salesforce organization, therefore, you can visualize and use the data that you have outside of your org. At this moment maybe you are thinking about an ETL (Extract – Transform – Load) but Salesforce already have features like Apex Data Loader. So keep in mind. This is not an ETL.

How does it work? The platform makes http requests to the External Repository and its information is sent back to the org in Real Time via an OData protocol.

Captura de pantalla 2015-05-23 a las 19.33.18

It could sound too technical or complicated if you are not a developer, even it could make you give up and think that Data Import Wizard will help you to solve your scenario. So let me show you a comparation of what you would need to do if you use these tools.

Data Import Wizard vs Lightning Connect

Data Import Wizard

  1. Create Custom object and / or fields
  2. Save data in a csv file
  3. Launch Import Wizard
    1. Choose the data
      1. What do I need? Create or Update
      2. Select the csv file
    2. Edit mapping
    3. Start Import

If you need to modify a record, you would have to run all sub steps that belongs to point 3.

Lightning Connect

  1. Create the connection
  2. Synchronize metadata that means create automatically the object and its fields
  3. Create a Tab
  4. Refresh the List View

Any time you need to make a modification outside of your organization, you should only need to refresh the page to see the changes in Salesforce.

Checa Hotel – Use Case

Sometimes it is easier to understand things when we see a real use case. So lets go for it.

Checa Hotel saves its reservations in a spreadsheet:

Captura de pantalla 2015-10-03 a las 18.53.03

But by the time they started moving into Salesforce, they continued using Google Drive for a while. In any case they wanted to avoid the double effort that means add reservations to the google spreadsheet and move data into Salesforce every time that a guest make a new reservation.

How can Lightning Connect help?

This Salesforce feature will help us to show in our organization any change in the Google Spreadsheet. How?

Lightning Connect is a feature that via OData protocol help us to link the external repository with your Salesforce organization. Per connection, you can create a single External Data Source that will have 1 or many External Objects. At the end this External Object is the metadata that we are going to use to show the data that is stored in the Google Spreadsheet in our case.

Captura de pantalla 2015-10-03 a las 19.00.55

What do we need to get it? We only need to follow above steps.

1. Create a Connection

1.1. Get the URL

There are different ways to get this url. At the end we need to create the connection we have explained above. And we can do it with an existing OData provider, like JitterBit, or we can create something with code. This is my case, where I have used Heroku in order to create an application in Java that is able to read a google sheet via its API.

As a start point you can check odata4j. Actually, my Java application started with some code that I pasted from this page.

So basically you only need to create your producer and register some data there. Bellow example it will just show numbers from 0 to 20.

//InMemoryProducer is a readonly odata provider that serves up POJOs 
//as entities using bean properties
//call InMemoryProducer.register to declare a new entity-set, 
//providing a entity source function and a propertyname to serve as the key
final InMemoryProducer producer = new InMemoryProducer("InMemoryProducerExample", 1000);
		
//expose an large list of integers as an entity-set called "Integers"
producer.register(Integer.class, 
                  Integer.class, 
                  "Integers", 
                  new Func<Iterable>()
                  {
			public Iterable apply()
			{
                          return Enumerable.range(0, 20);
			}
		  }, 
                  Funcs.method(Integer.class, Integer.class, "intValue"));

So we need to do something similar but with the data that we read from google spreadsheet.

In order to do that, the first thing is the creation of an inner class where we will store the data

public static class Reservation
{
   private Integer externalId;
   private Integer roomNumber;
   private Date startDate;
   private Date endDate;
   private Double price;
   private Boolean paid;
   private String guestName;
   private String specialRequirement;
		
   public Integer getExternalId(){ return externalId; }
   public void setExternalId(Integer value) { externalId = value; }
  
   ... 
   //getter and setter are required to be able to read info in Salesforce
}

The next step is to have a method that reads the data

public static List readGoogleReservations(SpreadsheetService googleService) 
{
   List reservationInfo = new ArrayList();
   URL feedUrl = null;
   feedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
   ...
   return reservationInfo;
} 	

And this method will be called from my producer:

producer.register(Reservation.class,
 		  "HotelReservation",
	 	  new Func<Iterable>()
	 	  {
	 	     public Iterable apply()
	 	     {
                        try
                        {
	 		   return readGoogleReservations(googleServicefinal);
                        } 
                        catch (Exception e) { return null; }
                     }
	 	   },
                  "ExternalId");

1.2. Create the External Data Source

Once you have your Heroku application and the url that it returns, the rest is just #clicksnotcode.

You only need to go to External Data Source entry on the left side bar menu under Setup:

Captura de pantalla 2015-10-03 a las 20.13.39

And click on New. Then just enter information about the new Connection.

Captura de pantalla 2015-10-03 a las 20.09.18

As you can see on above image, it is really simple. Just provide the Name and Type chose OData 2.0 that is the one we have used in our Heroku application.

Then, populate the Url field with the application one, that looks like

http://<funnyname_provided_by_heroku>.herokuapp.com/<yourProducer>.svc

And that is all. Just save, and your connection will be created.

In the case that you are using a different way to create the connection, you would only need to select a different type from the drop down list. For instance on below image, we can also have the Salesforce option, just a Simple URL, and the Custom one that is called LoopbackDataSourceProvider. This Custom one is related to a Custom Apex Connector, another interesting topic that I will talk about in a future entry.

Captura de pantalla 2015-10-03 a las 20.10.04

2. Synchronize Metadata – Create External Object

The next step is the creation of the Metadata that will contain the information we want to show. Even we can use the External Object entry that we can find on the left side bar menu, and create it manually, my advice is to use the External Data Source and create it automatically. Just clicking on Validate and Sync button, it will read those objects that the connection provides and create them for you.

Captura de pantalla 2015-10-03 a las 20.09.51

And if we open HotelReservation one, we can check that it looks like a custom object.

Captura de pantalla 2015-10-03 a las 20.08.58

3. Create a new Tab

Finally I want to see the data in my organization, so I will just create a Tab for it and update the look and feel of the list view.

Captura de pantalla 2015-10-03 a las 20.10.38

Also, if I want to open any of the records, we will see the data as well.

Captura de pantalla 2015-10-03 a las 20.12.08

And that’s all, we have our Google Spreadsheet links to our Salesforce organization.

4. Refresh the List View

Something that I have mentioned since the very beginning is that one of the advantages of Lightning Connect is that any change outside will be reflected in Real Time in my org. And the best way to show it is with a video.

Hightlights

I would like to finish this entry with some bullets points that could be interesting for you

  • Limitations
    • Lightning Connect has an additional cost. How much? Please contact Salesforce.com for more information.
    • We can only have 100 external objects per org
    • It is Read Only for now, although Winter’16 brings the writable feature. Let see how it works in a future entry 🙂
    • Callouts: At the end we are making calls to google drive, so this limitation is still in place. But if you need to increase it, contact Salesforce because this is a soft limitation.
    • External Objects don’t support Master-Detail relationships but Lightning Connect offers workaround for that. Follow next entries where I will talk about what you can do with an External Object.
    • It doesn’t provide Reports for now.
  • Advantages
    • Once you have the Url, the configuration is #clicksnotcode
    • It provides to end user Real Time refresh so if you are worried about performance, just keep in mind that the first time you open your External Object tab, it will take a bit longer because it is doing the connection. But from that moment, any update will be as fast as your internet connection allows you.
    • Finally, something that can make you think that pay for it, worth it. It doesn’t count against the storage limitation because we are not saving anything in Salesforce. We are just showing what we have outside. It is like a mirror.

Captura de pantalla 2015-10-03 a las 20.51.19

I hope now you can understand a bit better Lightning Connect and all its benefits.

Enjoy and see you in my next entry.

4 thoughts on “Lightning Connect – Visualize your external data in Salesforce easily

  1. Pingback: External Objects – What Lightning Connect offers to us | Agustina odeian

  2. Hi Agustina, great post. Currently we are working with Lightning Connect and have 2 external objects in Salesforce related with an External Lookup Relationship. Now we are try to query our objects with the following SOQL sentence:

    SELECT field1, field2
    FROM externalObject__x
    WHERE rel__r.Field__c = ‘a1LW00000002Myz’

    And we are receiving the following error:

    Bad Request StatusCode=400

    But if we do not include an specific value the query return fields correctly

    SELECT field1, field2
    FROM externalObject__x
    WHERE rel__r.Field__c = null

    Could you help us with this issue?

    Thanks in advance
    Daymel

    Like

    • Hi,

      Sorry for the late response. Actually I didn’t try to make a soql like yours, using the related object in the filter. But something comes to my mind. What is Field__c? It seems it is populated with the Id. Did you try filtering by the ExternalId instead of the Id? I remember that at early states of the product, doing something like “Select Field__c from ExternalObject__c Where Id = :idValue’ also failed. Let me know if you don’t make it work and I will try to reproduce the issue.

      Like

  3. Pingback: Lightning Connect and Apex Connector Framework | Agustina odeian

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s