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
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 firstname.lastname@example.org. If it is connected, press ‘ok’ and open the connection (right click, open connection). More information can be found here.
Recreate the w3schools database
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:
The following two sections are how to connect Tableau and Microsoft Power BI to the MySQL database:
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):
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.
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.
After this step, the notification below might pop up indicating that additional components should be installed. Click on more information and follow the instructions.
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.