ETL Tools and Analytics: A Match Made in Heaven

Imagine this: you order an item of flat-packed furniture, only to have it turn up deconstructed and flat-packed, and with the instruction manual in, say, Korean. There’s nothing wrong with those instructions — they make perfect sense when you know the language — but you can’t make out a word of it. Without a way to decipher how it fits together, all you can do is optimistically fiddle about with the pieces and see if you can create something that makes sense — even if a few bits are screwed on wrong, and you’re not sure it’s really doing what it’s supposed to.

This, essentially, is what happens when you put data through an ETL process. The data is organized using SQL, packed up and cleaned for transfer, and then deposited at the other end beautifully packaged — but in a language that simply doesn’t work for analytics.

Because of this, some people have thrown up their hands and said, “Ah, ETL and analytics — they just can’t mix!” …which is a shame, because, in fact, ETL and analytics can make an excellent match. You just need to get them speaking the same language.

The fact of the matter is that ETL alone is not enough. Once you’ve extracted, transferred, and loaded your data from one data warehouse to another (or to a data mart, or a delimited flat file, or whatever), you need to be able to use that data to get vital insights about your business, and that means finding some way to link to a powerful visualization tool.

One way of getting around this is to use an integrated tool that combines ETL with a wider BI platform, allowing you to draw data from the original warehouse directly into the system and feed it into a self-service, user-facing dashboard for querying and analysis.

Sisense’s ElastiCubes, for example, can handle 99% of all ETL functions, skipping the need for an external tool completely for many types of business and data project.

While it works beautifully in most cases, there are some scenarios where a separate ETL will still be preferable.

If, for example, you’re a massive global conglomerate with oceans of data to process, you’re using multiple, complex data sources at volume, or you need to document every step of the process for compliance purposes, a built-in ETL tool might not have the capacity you need.

In these situations, you’re likely to get much better performance by investing in external ETL capabilities.