About a year ago, I was asked to create a POC for an address validation automation in SFDC. This job required an automated process to send mortgage address data from SFDC to a 3rd party web service, and parse a response back into SFDC. An aggressive 30 day deadline was included, and there was no appetite for on-premise hardware.
The 3rd party spec followed MISMO standards (Mortgage Industry standards for electronic interchanges), which though extremely well put together, are not really standard. For example, the specs required parsing a complex DTD (Document Type Definition) for XML messaging [eXtended Markup Language – think envelopes for web data]. There was no WSDL service [Web Service Definition Language – a standard and useful web service nowadays], which would have eased the coding.
This was not to be a run-off-the-mill project.
The Salesforce API collection [Application Programming Interface -think USB, Video and Audio ports in your computer] provides customers with the integration capabilities to connect to nearly any data, and in many ways. However, SFDC customers are responsible for building such customizations on top of the provided APIs, in this case, using custom Javascript or Apex (coded) , or a cloud-based, third-party ETL app or tool [Extract, Transform, Load -declarative].
I compared cost estimates between coding in SFDC, the required messaging from the ground up, or using any of the advertised declarative ETL systems. As there is sparse SFDC documentation on how to code this particular requirement by following a DTD; I initially thought using one of the several available ETL tools would be cheaper and quicker. I must also add that the customer’s CIO had twice previously attempted to get this customization coded, and was not satisfied with the outcome. He was not too keen on a 3rd coding attempt, but had never heard of SFDC ETLs.
As the popular saying goes; “The definition of insanity is doing the same thing over and over and expecting a different outcome” – Attributed to Benjamin Franklin, Albert Einstein, and several others.
Though well versed in MSSQL and Oracle transformations, this was my first experience with SFDC ETL tools. ETL sounded like a very good proposition at first. Integrations were advertised to be as easy as dragging and dropping readily available flow components into a design environment, and 80% declarative. The cost was a bit scary, as most of these tools were billed as yearly subscription contracts, including just a couple integrations into the first billing tier. You can expect to pay about $1000 /month (and more, depending on the product) for your first tier contract.
-Note: Up to 50% discounts are offered to Non-Profit organizations.
The customers were informed and they stated the proposed benefits surpassed the costs estimate. So business case was validated, project chartered, and costs scoped. – or so I thought…
While the selected product is excellent, and has received rave reviews from the SFDC community, the documentation for doing anything other than standard WSDL connections was non-existent. The procedures for the transformation of the ETL were described hastily without any details.
It took more than 3 days to get a company representative to answer my request for information.
Once I had the ETL provider’s attention, service was excellent. They admitted that proper documentation was in progress and assigned developers to explain the procedures of the transformations (letter of intent non-withstanding). It was not until then that I learned that the ETL provider does not process any response messaging. This was not documented anywhere.
So the customer must provide a web server to run their agent software, which basically is a web listener, if you will.
Considering our on-premise related hardware constraints, I selected cloud web server from a well-known provider to install the ETL response listener, and configured the minimum requirement resources the ETL provider listed.
And that’s where it hit me.
– Too many parts…
– 4 different monthly bills… (SFDC, Data Provider, ETL Provider & Well Known Provider)!
This was deviating from the initial scope. It was time to bite the proverbial bullet, knuckle down, and code directly into SFDC. Leveraging the SFDC DOM class (Document Object Model- an XML parsing tool) was not nearly as difficult as expected – it ended up costing about a quarter of the estimated yearly ETL. Also, the customer would only receive 2 monthly bills. A successful POC was Apex built within a week. Needless to say, the customer’s CIO was only too happy to accept the proposed changes.
I used to be absolutely biased in favor of declarative methods whenever such were possible. Though Apex and VF coding can certainly become troublesome to maintain, such methods can sometimes be much more convenient than declarative tools for the customer, and should never be dismissed without careful consideration.
