Set up your Olist database

For the Olist assignment you will use a number of software tools to create, store, edit and analyse data. On this page we will explain how to install, connect and use these software tools in 9 steps (including videos in the links):

  1. Install Tableau and connect to Excel or CSV files
  2. Install Power BI and connect to Excel or CSV files
  3. Connect MySQL Workbench to your databases
  4. Reset personal password
  5. Connect Tableau to the databases
  6. Connect Power BI to the databases
  7. Recreate the database of w3schools
  8. Insert data into the database tables via Excel or CSV files
  9. Connect the Angular application to the databases (Work in Progress)

1. Install Tableau Desktop and connect to Excel or CSV files

Install Tableau desktop. This is free for students via this link. (You don’t need Tableau prep)

Connect to Excel or text (SCV) file:

2. Install Power BI and connect to Excel or CSV files

Option 1 (Windows users): Install the free version of Power BI Desktop directly from here, or

Open the web version of Power BI website (https://app.powerbi.com/home) using your UGent credentials.
However, to get the desktop version, click on the download button in the top right corner of the screen:

Option 2 (Mac users): Run the Power BI Desktop application via Athena (www.athena.ugent.be).

After running Power BI, you can connect to an Excel workbook or CSV file by clicking on the Select Data button.

3. Connect MySQL Workbench to your databases

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

Second, install MySQL workbench using this link, click on the download button and follow the instructions:

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:

Fill in a connection name of your choice and following credentials:

Hostname = ‘ugmarket.ugent.be’

port = ‘13306’.

Username is equal to your username on UFORA.

Select ‘test connection’ to check if the connection works. Your password (before you change it in step 4) is equal to your student number. If it doesn’t work (and your VPN connection is working!), send a mail to thomas.derave@ugent.be. If it is connected, press ‘ok’.

After, double click the connection you just created to open:

When you press ‘SCHEMAS’ on the left of the screen, you can see the databases you have access to:

4. Reset personal password

Your can (and should!) reset your personal password by opening a new SQL script:

Typing the following:

And pressing the lightning bolt above to run the script:

5. Connect Tableau to the databases

Select mySQL as connection data:

If In case you need a driver (or more than one), just download the ones you need by pressing the download button. After installing the driver(s), close and reopen tableau and try again. 

Name of the server is ‘ugmarket.ugent.be’, port is 13306, username and password are the same as for MySQL Workbench:

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

6. Connect Power BI to the databases

Connecting to the MySQL database

In Power BI click on get data and choose the more bottom option. 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 also 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 click on the Database tab (left hand side) and enter your database credentials (send by mail). 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 Power BI.

7. Recreate the database of w3schools

The w3schools database is used an excellent SQL tutorial. W3schools is one of the best sites to learn programming languages. Besides the SQL tutorial you can also learn HTML, PhP, CSS, JavaScript, Python, Java and many more. The physical database model W3schools is the following:

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

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

Open the script (via any text editor or IDE) and in the second line of the w3schools_database_creation.sql file, replace ‘groupTest’ into the name of a database you can edit (e.g. yourUserName; group0):

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 equal to the physical database model above, use the reverse engineering option.

Load data into the database via SQL file

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, thy to solve it yourself by googling the error)

To create a view of the data in a table, just right click on a table name and press ‘Select Rows – Limit 1000’.

8. Insert data into the database tables via Excel or CSV files

if you have an excel file, you first need to convert the Excel sheet to CSV. After, import the records via MySQL Workbench import button.

9. CRUD

Info will follow