There are many use cases where ETL is superior to Lightning Connect, and many cases where Lightning Connect is superior to ETL.
Recently, you could say that I’m living the dream of standards-based data connectivity. I’ve been consulting on a number of Salesforce1 Lightning Connect External Object integration projects with OData. At the same time, I have been consulting on some Extract, Transform, Load (ETL) projects using ODBC/JDBC targets and sources for Salesforce1 with tools like Microsoft SSIS, Informatica PowerCenter, IBM DataStage, Pentaho Data Integrator and SAP BODS.
While working on these projects, I keep getting asked which is better: ETL or Lightning Connect. I get the feeling that the stage is being set for a heavyweight data integration battle to the death! In fact, the two aren’t in conflict, and are often complementary. Lightning Connect remains new to the Salesforce1 platform (Winter ’15), so the confusion is understandable.
Today, I want to share my experiences with both and set the record straight.
I’ve run across several cases where ETL strategies are being used when Lightning Connect would be a better fit. This is because Lightning Connect did not exist until now, and those strategies will be reevaluated. However, both Lightning Connect and ETL are data integration strategies for specific use cases. While there are cases where project requirements overlap, each has a specific purpose in data integration.
The major difference is that ETL physically moves data in and out of the platform while Lightning Connect does not. To figure out which is the best strategy, ask yourself “Do we need to persist the data in the platform, or not?” Consider accounts, contacts or other master data from external systems that need to be loaded into Salesforce in batches. On the other hand, it may not make sense to load orders and order line items from the last 10 years into Salesforce.
ETL actually complements Lightning Connect behind the firewall because it builds data warehouses, marts, lakes and operational data stores which are ultimately consumed as external objects for 360-degree views of different Salesforce objects.
Salesforce will be the first to tell you this is untrue. As stated in Knowledge Article Number 204599:
Lightning Connect is new way for Data Integration–it is commonly known in the industry as Data Federation or Data Virtualization (similar to Siebel’s EBC, Oracle’s DBLink, PostgresSQL’s Foreign Data, SQL Server’s External Data Source).
While Lightning Connect is new to the Salesforce1 Platform, it’s not new to the industry. None of the data federation strategies mentioned above from Oracle or Microsoft have replaced the need for ETL, nor is that the intent.
Progress® DataDirect® has more than 20 years of experience with the mentioned data federation strategies using open data standards. It’s no coincidence that we’re the first and only generally available player providing enterprise OData connectivity with Lightning Connect, nor is it a coincidence that we have over 11,000 users already. To be completely transparent, I even wrote a “behind the scenes” article about my Lightning Connect integration experience with DataDirect Cloud on the Salesforce Developers blog.
It’s true that native reporting is not supported with external objects today. I’ve heard from the Salesforce community that it is planned for later this year (I feel like I should say safe harbor). Regardless, the answer to rumor #1 still applies.
OData is an OASIS-approved industry standard for building RESTful APIs for which Lightning Connect is a consumer. While OData stands for “open data protocol,” it does not mean the data is publicly available. Authentication is configured from Salesforce External Data Source configuration, and DataDirect Cloud supports secure authentication while encrypting all data in transit over HTTPS.
It’s true that DataDirect Cloud provides an OData service for data sources behind the firewall. However, did you know that it also connects to Salesforce1 in the other direction, providing direct SQL access to the platform? DataDirect Cloud ODBC clients can be used as both ETL sources and targets.
While Lightning Connect will inevitably replace several projects where ETL integration was the only option at the time, hopefully it’s clear there is no dramatic battle between the two. There are many use cases where ETL is superior to Lightning Connect, and many cases where Lightning Connect is superior to ETL. The freedom to choose the best solution is what I’m evangelizing.
What are your thoughts? Let me know in the comments, tweet me at @SAsinSumit or contact me directly at: www.linkedin.com/in/meetsumit. And if you’re interested in giving DataDirect Cloud a try, you can pick up a trial on our website.
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Copyright © 2019 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.