Six Data ETL Solutions To Automate Your Business

What is ETL?

You may come from another programing language or you might have started a new business and want to gather data to include in your new software system. Or perhaps you might want to backup your data so it can be converted to a different data formats. In fact, any situation where you want to get data from one source and put in another place. ETL come in to action in such cases. ETL stands for Extract, Transform and Load. You can read more about ETL and its process from here.

https://en.wikipedia.org/wiki/Extract,_transform,_load

ETL can be the Monday morning of computing

Quite often ETL can be a painful task which involves lots of manual and repetitive work. A typical situation is one where we have loads of data gathered together in a collection of Excel spread sheets. The task might be to include that data in your database to use in a desktop or web application. Extracting data from an Excel sheet manually is often very time consuming. Then you have to transform data into a format which destination data sources can understand and use to load data into their own datasets.

How can Delphi save you time on ETL?

Delphi has great support for handling ETL tasks. We have several components in Delphi specifically designed to simplify the ETL process. In most cases you just need to drag and drop some components to your form, set some properties and with perhaps a very small amount of code to glue it together the task is done. Lets take a look at some components you can use in your ETL process.

• FireDAC
• TFDBatchMove
• LiveBindings
• Enterprise Connectors
• TRESTClient
• SOAP Client

What is FireDac and how can you use it?

FireDac is a universal data access library which you can use in both VCL and in multi device Firemonkey applications. It has extensive support for both SQL relational database management systems and non SQL data sources.

FireDAC also includes the necessary components to move data from one source to another source. Here you can see it in action.

In this example we use “TFDBatchMove” component to move data from an RDBMS to a Microsoft Excel file. We also show the reverse with the data transferring in the opposite direction back from Excel. Also, you can see that there are only two lines of code to make it all happen!

How do we bind components to other components or data sources using LiveBindings?

LiveBindings are the visual way to bind components <> components and components <> data sources. You can control properties of a component using another component without any code. You can use the Live Binding designer or the Live Binding Wizard to so. It’s just a drag and drop of a connection to bind components and data sets.

A Live Bindings example

Let work through an example to demonstrate LiveBindings.

First, create a new VCL project and add a TFDMemTable and add some data to the memory table. Then add a TDateSource and connect it to the memory table using the data set property. Then, add some labels, a TSpinEdit, TEdit components until we your form resembles the form below. Now add a TDBNavigator and connect it to the data source.

Give some memorable names for all components so we can identify those in LiveBindings Designer. If you leave everything with the default names like TEdit1, TEdit2 it very quickly becomes confusing in the LiveBindings view.

Now, right-click the form and select “Bind Visually” form the menu. Drag and connect the properties of those components to fields in the memory table. That’s it, you’re done!

Now run the application and you can insert, edit, delete data in the memory table using those components without a single line of code.

Some more in-depth video of LiveBindings to facilitate ETL

What are those Delphi Enterprise Connectors?

As we discussed earlier, FireDAC has many components to support a variety of different enterprise connections. Using the CData Enterprise Connectors we can go beyond regular SQL/No SQL databases and can connect to many other types of applications and retrieve data from them just like any regular database. The Enterprise Connectors support 180+ enterprise data source types. You can access that data using your regular components using live bindings or manually in your code. Visit this link to find out which components and enterprise applications FireDAC supports. using the Enterprise Connectors:

https://www.cdata.com/firedac/

More video on ETL using Enterprise Connectors

How to access REST based services using Delphi?

Today many web applications have a REST API. Delphi has a great selection of components to handle REST requests.

TRESTClient – Is the component which execute REST requests. It has properties to set authenticators and Base URL which need to execute the request.

TRESTRequest – This component hold the data about the HTTP request. It set the HTTP method, Header Parameters, Body Parameters and many other optional parameters.

TRESTResponse – This component will hold the response after executing the request. It will include all error codes (if any) and any returned data in the form of JSON.

Here’s a video of RAD Studio’s REST Support

How to make remote procedure calls using SOAP client?

SOAP stands for Simple Object Access Protocol. Delphi supports both SOAP client and server. With SOAP we can access remote data or call remote procedures. SOAP has a file called a WSDL which describes the data structures and methods available in the SOAP. Delphi has a wizard to generate interface definitions and connection information from a WSDL document returned by a SOAP server.

More about ETL and SOAP

For a more detailed explanation of how to create a Delphi SOAP server and use the wizard to generate interface definitions and connection information visit this post by Embarcadero MVP Craig Chapman.

Creating and Consuming Soap Services in Delphi.

Conclusion

ETL is a regular process in many modern businesses. It can be a chore or repetitive task if you don’t have the proper tools available. Thankfully, Delphi has many components and techniques to support ETL with minimum coding.