#Instal Easy Data Transform free how to#
#Instal Easy Data Transform free full#
#Instal Easy Data Transform free code#

The last step is to calculate the correlation between GDP and the number of airports in the country:Įverything is transformed and computed, the result is as follows:

#Instal Easy Data Transform free how to#

First of all, we need to calculate the number of airports in the country (for more information, check how to create metrics in GoodData): Let’s move to the Analyze tab to find out what the correlation between the GDP and the number of airports in the country is. I also removed the Country list dataset as it does not have any value for us right now: Perfect, we have the GDP SQL dataset that we need! Also, to count the airports correctly, I will add an identifior to the Airport transformed SQL dataset:īoth of the SQL datasets now contain country codes, we can simply join them based on these country codes:

#Instal Easy Data Transform free code#

Now, we just need to create a new SQL dataset for the GDP where we join Country list to add the missing iso code (or as I named it - country code), and remove commas from the column value and convert it to numeric type:

#Instal Easy Data Transform free full#

We can start with the Country list table where both the full names and the iso codes are found: You can see that the values contain commas and this is not the correct format for numbers. Sadly, there is one more issue with the column value. On the other hand, the GDP table does not have iso codes but only has the full name of the country. The bad news is that the Airports table does not have the full country names (for example, Albania) but only iso codes of countries (for example, AL). Now, with this little transformation, we can simply render a geo chart to see the all airports in the whole world:Īs this was rather simple, let’s explore something more valuable - what is the correlation between GDP and the number of airports in a country?įirst, we can just list the values of Airports and GDP tables: Let’s edit the Airports transformed dataset:

We need to break this column into longitude and latitude to render a geo chart. The coordinates are not in the format we need for analytics. Unfortunately, there is one more problem we need to solve. You can see that I can do it with quite a simple SQL query and the result is the following: I don’t need to go to the ELT/ETL pipeline, I simply create a new SQL dataset called Airports transformed directly in the analytics, and I can do that with the following SQL code: Let’s say that I am interested just in the medium and large airports - it’s not a problem at all. It seems that the Airports table contains several types of airports such as heliports, or even closed airports. Let’s just check what types of airports we have in the database: The SQL dataset gives us the possibility to write SQL queries that are executed directly in the database. We can convert a dataset to a so-called SQL dataset: What you can see in the image above are datasets. Let’s connect the database to the analytics (if you are not familiar with GoodData, I encourage you to check the documentation). We can address these issues with the help of Last Mile ETL inside the analytics. You can see that the coordinates are in one column called coordinates instead of latitude and longitude, or value in GDP the table is text rather than numerics. It means that instead of one concrete goal to achieve, we will primarily try to explore some value in this data! With defined exploratory analytics, here are three tables in a database ( Airports, Country list, and GDP - Gross Domestic Product): Tables in a database What do I mean by 'exploratory analytics'? Let’s say, we have just three tables with data, and we would like to explore its value or learn some facts based on this data. Let’s deep dive into it! Exploratory Analytics With Last Mile ETL You may now ask how? Well, enough words, it’s time for an example.

This experience is sort of normal in our industry but does it have to be? For this reason, we are introducing Last Mile ETL! With it, you can do everything in one tool which significantly improves the ability to iterate on and the speed of development, customization, and security. Normally, you would need to spend a lot of time context-switching between the ELT/ETL pipeline and analytics, where, in the transformation (T) phase you would need to edit code that transforms data from the application shape to the analytics shape. For example, coordinates should be separated by latitude and longitude, some values are in a different format or type, and some tables can even have a bad design. Imagine you connect a database to analytics and the data is not in the shape you would like to have it.