5 Minute Snack: Preparing imported data for visualization, data cleansing

The last snack showed how easy it is to read textual data into a database with Delphi and FireDAC.

However, most of the times we cannot be happy just yet. Creating a good user-experience is our major concern at all times and we want to offer our users easy to read information.

Our energy data shows the generated energy  for every day. The structure of the data is as follows:

It is wonderful to have all the data at hand. But we want to display the 5 best months that have been recorded.

With SQL this could be very easy. And with Delphi it is indeed. I was really enthusiastic about the Local SQL feature that FireFAC offers. My fascination was only calmed a bit by Matthias Eissing, Embarcadero Germany, who told me that even Delphi 1 offered similar comfort using the BDE already. Yet another reason, why the BDE must have been that popular and can still be found in the industry…

Still, his statement will still not keep me from describing how we can do it with Local SQL and FireDAC these days with the current versions of Delphi — without the BDE. As a funny aside: Young engineers in my company have no clue what the “B” might stand for if you tell them it is the “database engine” of the comapny that developed Delphi back then. Borland is no longer present in the heads of the next generation it seems.

We are all in agreement that we could store the imported data in a different table and use a query to retrieve the data any way we would like. However, this would require the setup of a database connection of some sort – maybe even a server. And also it would require us create a persistence logic and model for our databases in some way. If you chose the quick and dirty approach, you write it in a local SQLite database. Still, it requires some sort of write access on the underlying operating system.

Thus, we formulate our premise: We want to offer the visualization from the data as it was imported.

Coming back to the query. Query languages are very comfortable, but I make our requirements even harder. I want to end up with a dataset that I can pass on to other areas of the application. This makes it necessary to end up with decent field names and field types, maybe even a proper index definition.

Currently, the data resides inside a TFDMemTable.

• In order to use Local SQL we need to drop a TFDConnection and a TFDLocalSQL component on the form.
• The driver name property of the connection needs to be set to “SQLite”. That already gives us a clue what kind of SQL will be at our disposal for local SQL operations.
• Set the Connection property of the LocalSQL component to the connection we just dropped.
• In order to apply Local SQL to our imported data, we may use a query indead. Drop a TFDQuery component on the form and set its connection property to the connection from the first step.
• Going back to our table with the imported data, give it a name like “Table” and assign its LocalSQL property to the local SQL component we dropped on the from. The table is now linked to the local sql component and can be addressed in local SQL queries by its name “Table”. Be aware of the double quotes, I had to find out that without the double quotes it does not work.
• Set the SQL query of the query as follows:

  SELECT *,
    CONCAT
     ( CONCAT( 
               YEAR(DATUM),"-"
              )
         ,MONTH(DATUM)
     ) AS Category
    FROM "Table"

This set up will create a table with one more field that allows us to group the energy values. We generate an atribute with the name “Category” that is generated from the year and the month of the date.  As an example it will generate the “Category” 2016-08 for any record that was stored in August, 2016.

I might add at this point that the next step could be included in this query, but I like to do things step-by-step and you will see that this way you might have a chance to understand what you did going back at this code years later.

• We now drop a second query on the from and set its connection property accordingly.
• In order to be able to use the first query in local sql assign the local sql component to the LocalSQL property of the query and give it a unique name like “Query” (not a good name, okay).
• Then add the following SQL statement to the second query, which you should name “QueryTotal”:

  SELECT Category,
      SUM(Energie) AS Total 
      FROM "Query" 
      GROUP BY category 
      ORDER BY total DESC
      LIMIT 5

QueryTotal will sum up all the energy values for every month, order them with the highest value on top and will limit the data to the best 5.

Opening up all the connections and executing the batch during design time will already yield the result we desired. I hooked up the tables to grids and even hooked up the final query to a chart component from TMS Software. Please believe me that I did no fancy styling.

This took less than five minutes!

When running the app you have to take care of opening and closing the connection, queries and local sql component at the correct time, but that will be no problem whatsoever:

procedure TfrmMain.Button1Click(Sender: TObject);
begin
  QueryTotal.Close;
  Query.Close;
  LocalSQL.Active := False;
  Batch.Execute;
  LocalSQL.Active := True;
  Query.Open;
  QueryTotal.Open;
end;

I left in the Button1 on purpose so that it would underline my claim that it only took 5 minutes.

Local SQL makes Delphi a very powerful tool when importing data, cleaning it up and preparing it for visualization. Imagine that you can do all this in a method of a web service to deliver the 5 best values for user input that is uploaded using a csv file (or any other custom file)…  The key is that the backend is non-existent for us as a programmer in this case. We do not store the information anywhere. Delphi and its underlying frameworks handle everything for us. We do not need to store values temporarily. We do not need to set up a database connection. We get the data in a file, tranform it and can return the tuple of values with the data required. Heck, we could even return a PNG or JPEG of the visualization. The chart component from TMS Software is also very powerful that it offers to return image files of its charts.

Hopefully this example shows yet again that Delphi has a lot of features to offer that are somewhat well-hidden sometimes.