Set up your Database

On this page we will explain how to

  • Connect to your database with MySQL Workbench.
  • Set it up in line with the as-is datamap of SuperDelivery
  • Connect to the database using Tableau and Power BI

MySQL Workbench

First, open a VPN connection to UGent via Cisco AnyConnect. A connection to your database is only possible via a VPN!

Second, install MySQL workbench using this link. MySQL workbench is an integrated development environment for the MySQL database system that aides with the database design and data management using SQL queries.

Open MySQL Workbench and create a new connection by selecting the + sign:

Your username and password was send in a mail to one of the group members. Enter hostname = ‘ugmarket.gent.be’, port = ‘13306’.

Select ‘test connection’ to check if the connection works. If it doesn’t, send a mail to thomas.derave@ugent.be. If it is connected, press ‘ok’ and open the connection (right click, open connection). More information can be found here.

Recreate the w3schools database

W3schools is one of the best sites to learn programming languages. Besides HTML, PhP, CSS, JavaScript, Python, Java and many more it has an excellent SQL tutorial for you to learn SQL queries. These can be entered in the MySQL Workbench to create and modify the tables and fields in your database. And to insert, modify and select the data in your database. The datamap of this w3schools database (equal to the as-is datamap of SuperDelivery is the following:

Figure 1: Datamap W3schools

To recreate the exact same database structure in you MySQL database, download following script:

Change the extension from .txt to .sql (just change the name of the file).

In the second line of the w3schools_database_creation.sql file, replace ‘groupTest’ into the name of your database (e.g. group7):

Open the script with MySQL Workbench by clicking on the folder icon:

And run the script by clicking on the lightning bolt icon.

If you know refresh the database schema, you can see an overview of the tables on the left:

If you want to have a visible representation of your database schema, as for figure 1, use the reverse engineering option.

Load data into the database

To fill the database with data, download following script, change the extension to SQL, open with MySQL Workbench and execute as well:

( If an error occurs, change the settings in your preferences and restart the connection as requested)

To see the data in a table, just right click on a table name and select (the first 1000) rows:

To fill the database with new records, you can (1) use the insert into query, or (2) convert excel sheet to csv and import the records via MySQL Workbench import button.

The following two sections are how to connect Tableau and Microsoft Power BI to the MySQL database:

Power BI and Tableau. Who Leads in 2019? - Iflexion

Tableau

Install Tableau desktop. This is free for students via this link.

After you start the software, connect to a MySQL server.

Name of the server is ‘ugmarket.ugent.be’, port is 13306, username and password are the same for MySQL Workbench (send in mail):

MySQL drivers

If you’re working on a Windows machine, the odds are that you have to install MySQL drivers before you can connect with the MySQL database (this is the case if the “sign in” button is greyed out). To solve this problem you can download the required drivers here. After installing the drivers and restarting Tableau, you should be able to connect with the database.

Now, it is possible to select your database, drag the requested table(s) into the white box and create your first worksheet! 🙂

Microsoft Power BI

For Microsoft users

Login to the web version of Power BI using your UGent credentials. Next, download the desktop version of Power BI by clicking on the download icon on the upper right and install the software on your machine.

Download icon on the op right of the page

After installation, open Power BI (the desktop version) and login with your UGent credentials. Open a VPN connection to UGent via Cisco AnyConnect.

For Mac users

Login to Athena via athena.ugent.be and open Power BI Desktop.

Connecting to the MySQL database

In Power BI you should click on get data, If you can’t see all possible data sources, you should choose the bottom option (more). Thereafter, you should click on MySQL-database.

“Get data” > “more” > “MySQL-database”

After this step, the notification below might pop up indicating that additional components should be installed. Click on more information and follow the instructions.

You can download the required components via this link.

After installing the components you can start PowerBI again and follow the previously mentioned steps. You should be led to the following screen where you enter the server followed by a semicolon and the port of our connection: ugmarket.ugent.be:13306, combined with the database name of your group. Then click OK.

You will be led to the following screen where you should click on the Database tab (left hand side) and enter your database(!) credentials. Furthermore, when selecting on which level the entered settings should apply to, choose the second option (the one where your database name is attached to the server and port).

In the Navigator, select all the tables and load them into PowerBI.

PowerBI Navigator