Week 2 - Software Setup

PreviousWeek 1 - Introduction (Self-Reading)NextWeek 3 - Extract, transform, and load (ETL)

Last updated 2 months ago

Week 2 - Software Setup

1. Learning Objectives

This lab's purpose is to set up a data warehousing development environment (Layer 1 in the OLAP Architecture, as shown below) on your local machine. This includes installing PostgreSQL, and visualization tools such as Power BI (for Windows users) or Tableau (for Mac users). By setting up the environment on your own machine, you will have full control over all the software tools. We strongly recommend completing this setup on your own device.

What we will need for this:

git: we only need you to have git installed and can git clone
docker and docker compose

If you are not familiar with Docker, we know that there is no Unit in UWA officially teaching Docker, so we wrap up a tutorial .

To install and learn about git, check this .

2. OLAP Architecture

3. Installing and Running Database

We have several options to get a PostgreSQL database. You only need to choose ONE of these three options.

Option 1: Install databases locally
- Download and set up the database environment on your own device independently
Option 2: Running database with Docker locally
- This is the easiest way, as we have done the complex setup work for you. However, you will need to understand how it works.
- Note: Errors may occur if you have previously installed PostgreSQL locally.
Option 3: Uni Shared Server
- Uni IT has deployed a shared server for all students enrolled in this Data Warehousing unit. You can use your assigned account name and password to log in to the shared server. However, this is the slowest solution. We only recommend this option for users who cannot use their own devices.

No matter which option you choose, you should be able to connect to the PostgreSQL database via either a GUI tool or a command-line tool and perform operations on the database.

In the PostgreSQL ecosystem, there is a tool called pgAdmin 4, developed by the official PostgreSQL team. It provides capabilities for running SQL queries, monitoring database status, viewing data, setting up users, and more.

But do not mess it up here. All the GUIs or command-line tools are methods for connecting to the database engine; they are not database engines and can not replace them.

3.1 Option 1: Local Solution

3.1.1 PostgreSQL Installation

You can install and run PostgreSQL directly on your machine. You can download PostgreSQL using the links below.

During the installation process, you'll be prompted to set a password for the default 'postgres' superuser account. Make sure to remember this password.

Complete the installation by following the prompts, and uncheck the 'Launch Stack Builder' box before clicking Finish.

3.1.2 Create a Server

To manage your PostgreSQL server, launch pgAdmin 4 by searching for it on your machines.

When logging in for the first time, you'll need to configure a new server connection. Click the 'Add New Server' button.

Under the 'General' tab, enter your preferred Server name.

Under the 'Connection' tab:
Set 'Host name/address' to 'localhost'
Enter the password you created during installation
Verify the port number (default is 5432, but you may change it during the installation process)
Click 'Save' to complete the configuration"

Once connected, you can view your server in the Object Explorer panel. The server will appear under 'Servers' with the name you provided (e.g., 'DW_2025').

3.2. Option 2: Database within Docker

To use the Docker solution, first, clone a GitHub repository.

git clone https://github.com/PascalSun/DW_2025
# This is the command you will use to download the repo if you already have a good setup

Subsequently, installing and running Docker on your machine.

3.2.1. Installing and Running Docker Desktop (Windows/Mac/Linux)

The process of installing Docker Desktop is straightforward. You use the installer for your particular operating system.

3.2.2. Testing Docker

Use the command below in the terminal to run the hello-world file in Docker:

docker run hello-world

After running the above command, you should see a message below, which means your docker has been successfully installed on your device.

Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
e6590344b1a5: Pull complete
Digest: sha256:e0b569a5163a5e6be84e210a2587e7d447e08f87a0e90798363fa44a0464a1e8
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
    (amd64)
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker ID:
 https://hub.docker.com/

For more examples and ideas, visit:
 https://docs.docker.com/get-started/

This command below will bring up a PostgreSQL database.

docker-compose up -d

If the previous command doesn't work, please try the command below:

docker compose up -d

If any errors regarding SQL Server are encountered, please ignore them. We won't use SQL Server this semester anymore. As long as PostgreSQL works well, that will be sufficient.

3.2.3 Running pgadmin4

All the variables can be found in the docker-compose.yml file within the repo, you can try to find out how they match together.

User name: admin@admin.com
Password: root

After you login to the pgAdmin, then you will need to add a new server if it is your first time to log in.

Click on 'Add New Server', and enter your preferred Server name under General.

Switch to the Connection tab, and enter the Host name/address. The username and password can be found in docker-compose.yml. also

username: postgres
password: postgres
host name/address: pgdb
port: 5432

Finally, click on 'Save'.

If you have done all the steps above successfully, you will see the screenshot in the Object Explorer panel below.

After this, clean your docker images, then you can run it with docker image prune (or docker system prune if you want to clean all unused Docker objects).

3.3 Option 3: Shared Server Solution

This solution is highly not recommended. Use this solution only if you have failed the Docker solution and the local solution.
The shared server solution only works with Unifi. If you implement this solution on a network other than Unifi, you must first set up the UWA VPN - UniConnect on your machine.

3.3.1 Shared Server with Local PostgreSQL Client

Under the 'Connection' tab:
Set 'Host name/address' to 'ems-win-058.uniwa.uwa.edu.au'
Enter the Username you received in the email from Luke.
Enter the Password you received in the email from Luke.
Verify the port number: 5432
Click 'Save' to complete the configuration"

Once connected, you can view your server in the Object Explorer panel. The server will appear under 'Servers' with the name you provided.

3.3.2 Shared Server with Python Libraries

You can use any code editor, such as VSCode or Jupyter Notebook while working with psycopg2 .

Use the command below to install psycopg2 .

pip install psycopg2

Set up connection parameters.

import psycopg2

conn_params = {
    "host": "ems-win-058.uniwa.uwa.edu.au",
    "database": "your_database_name", #the database name is <studentID_dbx>, where x is a number from 1 to 10
    "user": "your_username",
    "password": "your_password",
    "port": "5432"  
}

Connect to the database. These two lines establish a connection to the PostgreSQL database using the provided connection parameters and create a cursor object that allows you to execute SQL commands and fetch results.

conn = psycopg2.connect(**conn_params)

cur = conn.cursor()

Connection test. If you connect the database successfully, you can see the PostgreSQL version.

cur.execute("SELECT version()")
version = cur.fetchone()
print("PostgreSQL version:", version)

4. Installing Visualisation Tools

You have 2 options to install and run visualization tools.

Windows users: either Power BI or Tableau
Mac users: Tableau only

Below, you will find detailed instructions for downloading and installing these applications on your respective operating systems.

4.1 Power BI

Download now: Clicking this button will redirect you to Microsoft Store.
Advanced download options: Download the .exe installer and follow the setup steps.

Alternatively, you can open the Microsoft Store on your machine, then search for "Power BI". Click "Power BI Desktop" to open the installation page.

4.2 Tableau

4.2.1 Download and Install Tableau Desktop

Activate with product key:

Note: Downloading the wrong version of Tableau Desktop will result in activation failure. Please download version 2024.3.x.

Product Key

TC4N-76D9-F100-EF57-0302

If you already have a copy of Tableau Desktop installed. Update the license key in the application Help menu → Manage Product Keys or activate with a product key.

Important: You can only use Tableau with the product key for non-commercial academic research.

5. Exploration

Please feel free to explore the features of PostgreSQL and Power BI/Tableau.

PreviousWeek 1 - Introduction (Self-Reading)NextWeek 3 - Extract, transform, and load (ETL)

Last updated 2 months ago