Setup Metabase and connect to the database
Tags: datascience, beginners, database, dataanalysts
Published: 2022-09-15 16:04:02

**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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cbge8bb1rcjqyxliyrq4.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k5icp3eeage8y1nfgkhg.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8au09c6fkej1q5pj9wi8.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nq9p995xpmniovhfdrfb.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tld2mtxdulomu7ytag83.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3l1kzomjgjfwsfjt6nzc.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z2uutu9kysda053596u2.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ufy20jsrrxzksv1sgj5u.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/18wrgw8s3jug8xjugl8e.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qzw7a6avk19no7059klq.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/98jrar6lrv7zl1am3s0s.png)

Routes with multiple counts and representative times


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/muqf63w3aj152rth3pov.png)

Exploring Metabase using a sample database

Various visualisation features
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kllxkulan1lckuln61s5.png)

Query to SQL Conversion
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m5div6w1b67j7n8oihf0.png)

Visualization
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ylqe9kkq1a14scjfprtm.png)