download

Architecture

ELT Desktop enables you to transform your data inside the database. Well, there are several ETL tools on the market, so you might wonder: what's the difference? The outstanding difference to most of the other tools is the concept: inside your database.

ELT vs. ETL

In both cases the letters "E", "L" and "T" have the same meanings: "extract", "load" and "transform" correspondingly. So what is the difference between ELT and ETL concepts? The most important thing in this context is the order of the operations "load" and "transform". Simply put, the ETL tools first do data transformation on the file system and load it into the database afterwards. To do the transformations these tools require a separate server. On the other hand, ELT tools first load the data into the database and then use the power of the database to do the transformation.

Obviously, ELT Desktop follows the ELT concept. It uses in-database transformations and does not have a transformation engine on its own that would claim lots of CPU power, disk space, RAM, etc. Instead, we use the power horse already available in your company: the database!

In ELT Desktop you simply define visually what you want to do. You create queries in an SQL-like syntax, put several jobs together into a Job Diagram (containing conditions, loops, etc.), generate valid SQL code and send it to the database. This provides you with:

  • full control over the SQL sent to the database
  • no need for a powerful ETL-server, ELT Desktop can run on a notebook
  • metadata information is not proprietary, it's extracted directly from SQL
  • no unnecessary data movement between different servers
  • full utilization of database performance

 

 

To learn more about the features and functionality of ELT Desktop, click here.

Read on for more information concerning the trade-offs of other ETL tools.

Where the ETL concept makes sense...

ETL tools generally come along with their own data transformation engine. To perform the work, they have to extract data from a database (or load a file), do their stuff and load it back to the database when they are done. This approach is reasonable as long as many different databases are involved in your data warehouse landscape.

In this scenario you have to transport data from one place to another anyway, so it's a legitimate way to do the tranformation work in a separate specialized engine.

...and where the ETL concept performs poorly!

But what if you want to transform data that is already in your database? In this case most ETL tools act just the same: export, transform, load (ETL). Let's see what happens:

 

 

Considering that extracting and importing is not a strength of most relational databases, it's obviously a big waste of time to do it this way. In many cases you will spend a lot more time unloading and loading the data  than doing the transformations.

Push-Down optimization

Needless to say, ETL vendors have recognized this problem. That's why they invented something called "Push-Down". Push-Down is a functionality that allows you to take your existing ETL logic (which is running in the ETL engine) and convert it to SQL statements that run on the database. This idea is quite good, but practical experience shows: It does not work reasonable!

  • some functionality, the ETL engine provides, cannot be converted automatically by Push-Down
  • the SQL generated has bad quality and causes even worse performance

Yes, in most cases it is possible to "fix" bad SQL by modifying it or adding SQL manually to meet your needs. But, if you do so, you will loose all the fine functions that made you buy the ETL tool in the beginning (automatic documentation, data lineage, consistent metadata, etc.). Consistency is gone. See what it looks like:

 

 

 

ELT Desktop as solution

ELT Desktop is the best solution for in-database transformations. With ELT Desktop we bring it all together:

  • maximum performance due to usage of in-database transformations
  • impact analysis of SQL code
  • lower costs, because there's no need for dedicated ETL Servers.