S
Sumit Sarkar
Guest
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.
1. Lightning Connect replaces ETL
Reality
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.
2. Lightning Connect is risky because it is first in real-time external data strategy
Reality
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.
3. Salesforce is Adding Support for Native Reporting on External Objects to Replace ETL
Reality
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.
4. OData is Not as Secure as ETL Data Standards ODBC and JDBC
Reality
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.
5. Progress DataDirect Cloud is Only Useful for Lightning Connect
Reality
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.
Developers Have the Freedom to Choose
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.
Talk to Sumit Sarkar
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.
Author information
Principal Systems Engineer, Progress DataDirect at Progress Software
Data Access Evangelist, Speaker and World's leading consultant on direct SQL access to Cloud Data.
Sumit Sarkar has been working in the data connectivity field for 10 years. His 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 IUC34, IUC35, Progress Revolution, Oracle User's Group, Collaborate 12, Dreamforce 2012, Cloud Computing Expo 2012, PRGS13 Exchange, Dreamforce 2013, Oracle OpenWorld 14, PRGS14, DF14
Presented related webinars in collaboration with IOUG, DZone, Progress DataDirect and Progress Communities.
| Twitter | Google+ | LinkedIn |
Continue reading...