Setup Metabase and connect to the database

What exactly is Metabase? In a nutshell, it's a free and open source business intelligence tool. Some might describe it as a reporting and dashboard tool. Metabase enables you to easily ask questions of your data and view it in a variety of logical formats.

I came across this amazing tool during my recent Data Analysts technical challenge. I had heard of it but had never used it. Every day is a learning cup, and learning never stops.

Instructions;

• I was given the task of importing a spreadsheet into a database and connecting it to Metabase.

• Visualize the data by route and time of departure.

• Using whatever tools you have at your disposal, forecast which routes and departures are the most profitable, making recommendations on which to expand and which to cancel.

Before adding a new App, one must first create an account with Heroku then new App → Resources → search for ClearDB. I had to use cleardb to connect Heroku to my MySQL workbench using a csv file.

ClearDB MySQL should be expanded in the settings section expose configuration parameters This will enable us to connect to the local database and view the following information;

mysql://b176c32991cf37:14187ffe@us-cdbr-east-**

USER NAME = **

PASSWORD = *

HOST = us-cdbr-east-*

DATABASE NAME = heroku_*

You run heroku config to get the CLEARDB DATABASE URL, which should look like this:

CLEARDB_DATABASE_URL => mysql://[username]:[password]@[host]/[database name]?reconnect=true So you basically just look at your own url and get everything you need from there. That is how I configured mysql workbench.

'mysql://b0600ea495asds:9cd2b111@us-cdbr-hirone-west- 06.cleardb.net/heroku_4a1dc3673c4114d?reconnect=true' This will then be your database credentials. (Extracted from the URL above)

USER NAME = b0600ea495asds

PASSWORD = 9cd2b111

HOST = us-cdbr-hirone-west- 06.cleardb.net

DATABASE NAME = heroku_4a1dc3673c4114d

The screenshot shows that the connection between the MySQL workbench and the remote database was successful.

I received an error during the csv file import process, as shown in the screenshot, indicating that it was unicode (utf - 8) with BOM, despite the fact that MySQL supports unicode (utf - 8) so I had to convert it to unicode (utf - 8) as shown in the series of screenshots, then import it again.

I was able to successfully import both my local and remote databases, as shown in the screenshots.

Visualize the data by route as well as departure time for the first question. The number of routes taken in a given period of time.

Routes with multiple counts and representative times

Exploring Metabase using a sample database

Various visualisation features

Query to SQL Conversion

Visualization