> For the complete documentation index, see [llms.txt](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://csse-uwa.gitbook.io/data-warehousing-lab-sheets/week-2-software-setup.md).

# 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** [**here**](https://uwa-nlp-tlp.gitbook.io/it-support/docker-101)**.**

**To install and learn about git, check this** [**link**](https://git-scm.com/)**.**

## 2. OLAP Architecture <a href="#id-2.-olap-architecture" id="id-2.-olap-architecture"></a>

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2F2Z342k25v5FlL6hvrGHn%2Fimage.png?alt=media&amp;token=7bdf53d0-5c80-430c-8b9b-e3b14fc74bb2" alt=""><figcaption><p>OLAP Architecture</p></figcaption></figure>

## 3. Installing and Running Database <a href="#id-3.-installing-and-running-database" id="id-3.-installing-and-running-database"></a>

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 (The most recommended) <a href="#id-3.2.-option-2-local-solution" id="id-3.2.-option-2-local-solution"></a>

#### **3.1.1 PostgreSQL Installation**

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

* Mac OS X: Click [here](https://sbp.enterprisedb.com/getfile.jsp?fileid=1259405).
* Windows x86-64: Click [here](https://sbp.enterprisedb.com/getfile.jsp?fileid=1259402).

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2F99Zm2ibcFuFbHLOkjhqB%2Fimage.png?alt=media&amp;token=60061c76-4e1a-43d0-9be6-35de4c6e4d78" alt=""><figcaption><p>Password for postgres</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FVzsEJSeBgHO92DSPTzhp%2Fimage.png?alt=media&amp;token=63a6cab1-5463-4dd4-ae1d-ab2d42fc5633" alt=""><figcaption><p>Completing the PostgreSQL Setup</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FhLgrep9Mf42QGdEAGSKZ%2Fimage.png?alt=media&amp;token=150472f4-e399-45f7-a38a-bee780437699" alt=""><figcaption><p>Add New Server</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FljGc6yDiwacPVwx73mad%2Fimage.png?alt=media&amp;token=5a90f871-e589-4cf9-a768-1011e9847417" alt=""><figcaption><p>Server Name</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2F1o5umQv9FXwGCzJsNNFJ%2Fimage.png?alt=media&amp;token=ebb8993a-ee08-4c2f-afce-0cfe59036839" alt=""><figcaption><p>Connection Details</p></figcaption></figure>

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').

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2F1GiVmnqkVRiIH8UD92pr%2Fimage.png?alt=media&amp;token=fd77358b-56ef-41c5-b778-caf61be94165" alt=""><figcaption><p>Connect the Server Successfully!</p></figcaption></figure>

{% hint style="success" %}
Move to [Section 4](#id-4.-installing-visualisation-tools-2) to install visualisation tools.
{% endhint %}

### 3.2. Option 2: Database within Docker <a href="#id-3.1.-option-1-database-within-docker" id="id-3.1.-option-1-database-within-docker"></a>

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

There are multiple approaches to clone the repository. For example, download the zip file via the link [here](https://github.com/PascalSun/DW_2025) or use the `git` command to clone the repository. But we do recommend you do it via `git clone` .

```bash
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
```

For more details read [README.md](https://github.com/PascalSun/DW_2024/blob/main/README.md).

Subsequently, installing and running Docker on your machine.

**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** [**here**](https://uwa-nlp-tlp.gitbook.io/it-support/dev-setup/docker-101)**.**

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

You can get a more comprehensive overview of what Docker is from [here](https://docs.docker.com/get-started/overview/).

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

* Mac - Intel Chips: Click [here](https://desktop.docker.com/mac/main/amd64/Docker.dmg?utm_source=docker\&utm_medium=webreferral\&utm_campaign=dd-smartbutton\&utm_location=module&_gl=1*1v23n0b*_gcl_au*OTY1MTg2MzMzLjE3Mzk5MzUwNzE.*_ga*MTI2MjcwMTcwNS4xNzM5OTM1MDcy*_ga_XJWPQMJYHQ*MTc0MDUzMjYyOS4zLjEuMTc0MDUzMjYzMS41OC4wLjA.).
* Mac - Apple Silicon: Click [here](https://desktop.docker.com/mac/main/arm64/Docker.dmg?utm_source=docker\&utm_medium=webreferral\&utm_campaign=dd-smartbutton\&utm_location=module&_gl=1*1ainorx*_gcl_au*OTY1MTg2MzMzLjE3Mzk5MzUwNzE.*_ga*MTI2MjcwMTcwNS4xNzM5OTM1MDcy*_ga_XJWPQMJYHQ*MTc0MDUzMjYyOS4zLjEuMTc0MDUzMjYzMS41OC4wLjA.).
* Windows - AMD64: Click [here](https://desktop.docker.com/win/main/amd64/Docker%20Desktop%20Installer.exe?utm_source=docker\&utm_medium=webreferral\&utm_campaign=dd-smartbutton\&utm_location=module&_gl=1*1iqe5r6*_gcl_au*OTY1MTg2MzMzLjE3Mzk5MzUwNzE.*_ga*MTI2MjcwMTcwNS4xNzM5OTM1MDcy*_ga_XJWPQMJYHQ*MTc0MDUzMjYyOS4zLjEuMTc0MDUzMjYzMS41OC4wLjA.).
* Windows - ARM64 (Beta): Click [here](https://desktop.docker.com/win/main/arm64/Docker%20Desktop%20Installer.exe?utm_source=docker\&utm_medium=webreferral\&utm_campaign=dd-smartbutton\&utm_location=module&_gl=1*1iqe5r6*_gcl_au*OTY1MTg2MzMzLjE3Mzk5MzUwNzE.*_ga*MTI2MjcwMTcwNS4xNzM5OTM1MDcy*_ga_XJWPQMJYHQ*MTc0MDUzMjYyOS4zLjEuMTc0MDUzMjYzMS41OC4wLjA.).

#### **3.2.2. Testing Docker**

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

```bash
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.

```bash
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/
```

Once you test Docker successfully, **run your terminal under the cloned repository folder (e.g. DW\_2025)**, and then run the [`docker-compose.yml`](https://github.com/PascalSun/DW_2024/blob/main/docker-compose.yml) file. (please note, the process may take a while on your machine).

This command below will bring up a PostgreSQL database.

```bash
docker-compose up -d
```

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

```bash
docker compose up -d
```

{% hint style="warning" %}
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.
{% endhint %}

Read the [`docker-compose.yml`](https://github.com/PascalSun/DW_2024/blob/main/docker-compose.yml) file and the whole project, understand what we are trying to do first.

#### **3.2.3 Running pgadmin4**

After starting the container, click the [link](http://localhost:5050/login?next=%2Fbrowser%2F) for pgadmin4 to open PostgreSQL with your browser. (You **don't** need to download PgAdmin 4 on your devices). Use the following information to login pgAdmin:

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.&#x20;

* User name: **<admin@admin.com>**&#x20;
* Password: **root**

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FTU9RE5B9AkQcfhiVWWlr%2Fimage.png?alt=media&amp;token=604f2256-5fdf-4269-9520-a83135efc3be" alt=""><figcaption><p>Login Page</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FabBzO1wHXNLqoNWOJzV8%2Fimage.png?alt=media&amp;token=e178f3a7-8a47-4022-bc90-118dc06a743f" alt=""><figcaption><p>Add New Server</p></figcaption></figure>

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FoBFc81NhzbGfP4omhtLo%2Fimage.png?alt=media&amp;token=1ebd9642-f993-448e-b1c5-75c0b364222f" alt=""><figcaption><p>Server Name</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FS0AJ9FUNEucZdoaTJxg3%2Fimage.png?alt=media&amp;token=5f3338fe-f1b7-4843-abbc-9cbf6169eabb" alt=""><figcaption><p>Connection Details</p></figcaption></figure>

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FLxvy0ZhJjAnc40lS9oKr%2Fimage.png?alt=media&amp;token=0a6ea7f2-f0e1-42c8-b455-72bcdee69bc2" alt=""><figcaption><p>Connect the Server Successfully!</p></figcaption></figure>

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 (Not available in 2026) <a href="#id-4.-installing-visualisation-tools" id="id-4.-installing-visualisation-tools"></a>

{% hint style="danger" %}

* **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.**
  * [**Windows**](https://uwa.service-now.com/sp?id=kb_article\&sysparm_article=KB0011676)
  * [**Mac**](https://uwa.service-now.com/sp?id=kb_article_view\&sysparm_article=KB0011670)
    {% endhint %}

#### 3.3.1  Shared Server with Local PostgreSQL Client <a href="#id-4.-installing-visualisation-tools" id="id-4.-installing-visualisation-tools"></a>

Please refer to [**3.2.1 PostgreSQL Installation**](#id-3.2.-option-2-local-solution) download and install PostgreSQL on a device. Launch pgAdmin 4 by searching for it on the device and add a new server.

* 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 P**assword** you received in the email from Luke.
* Verify the **port number: 5432**
* Click '**Save**' to complete the configuration"

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FPI9TLTUx2Wepkn0RmyG3%2Fimage.png?alt=media&amp;token=0ec1c2f2-36d5-4818-927a-3431cf73fd78" alt=""><figcaption><p>Connect the Shared Server</p></figcaption></figure>

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

There are multiple Python libraries that can be used to connect a PostgreSQL server. For example, [`sqlalchemy`](https://docs.sqlalchemy.org/en/20/dialects/index.html) , [`PyGreSQL`](https://www.pygresql.org/) , [`psycopg2`](https://pypi.org/project/psycopg2/), and so on.

You can use any code editor, such as VSCode or Jupyter Notebook while working with `psycopg2` .&#x20;

Use the command below to install `psycopg2` .

```python
pip install psycopg2
```

Set up connection parameters.

```python
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.

```python
conn = psycopg2.connect(**conn_params)

cur = conn.cursor()
```

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

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

## 4. Installing Visualisation Tools <a href="#id-4.-installing-visualisation-tools" id="id-4.-installing-visualisation-tools"></a>

You have 2 options to install and run visualization tools.&#x20;

* **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** <a href="#id-4.1-power-bi-for-windows-users" id="id-4.1-power-bi-for-windows-users"></a>

Visit [here](https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop) and download Power BI Desktop.

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

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FMd4D0Hej2kyjJWD70G44%2Fimage.png?alt=media&amp;token=3e7eab5e-54da-40cb-bb24-91f4557b45fb" alt=""><figcaption><p>Get Power BI Desktop from Microsoft Store</p></figcaption></figure>

### 4.2 Tableau <a href="#id-4.2-tableau-for-mac-users" id="id-4.2-tableau-for-mac-users"></a>

#### **4.2.1 Download and Install Tableau Desktop**

* First [Sign into](https://identity.idp.tableau.com/login) an existing [Tableau.com](http://tableau.com/) account, or [create](https://id.tableau.com/register) a new account using your student email.
* Once signed in, visit the [TFT Activation page](https://www.tableau.com/tft/activation) to download the *<mark style="color:red;">**2024.3.4 or 2024.3.3**</mark>* versions of ***Tableau Desktop*** and ***Tableau Prep Builder***
* Activate with product key:

{% hint style="info" %}
Note: Downloading the wrong version of Tableau Desktop will result in activation failure. Please download version **2024.3.x**.
{% endhint %}

<details>

<summary>Product Key</summary>

**TCXZ-2A66-4ED0-F306-04FE**

</details>

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

{% hint style="danger" %}
**Important: You can only use Tableau with the product key for non-commercial academic research.**
{% endhint %}

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FVhHMbUsHUPeJi6CEQepn%2Fimage.png?alt=media&amp;token=0dd88ff0-ab9d-4b3a-8334-c89ce9b324f4" alt=""><figcaption></figcaption></figure>

<figure><img src="https://374096590-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FE6tM8okJTaOtct7O9mvr%2Fuploads%2FcZA0btkTcCNMKBuR8PMp%2Fimage.png?alt=media&amp;token=f7887b84-b701-41f8-aa98-a5afedc0f9a5" alt=""><figcaption></figcaption></figure>

For more information , you can search [Install Tableau Desktop](https://onlinehelp.tableau.com/current/desktopdeploy/en-us/desktop_deploy_download_and_install.htm) and [Platform Identification Guide](https://help.salesforce.com/s/articleView?id=002234045\&type=1&_ga=2.136472313.812932476.1739968067-656350124.1737549879&_gac=1.57369176.1740018194.CjwKCAiAn9a9BhBtEiwAbKg6flCSfKbToop6LUvtZQ-FnxKiep1EMDD5yGsDN6OjXcfoeDiwpfL_whoC8qIQAvD_BwE) (Mac version).

## 5. Databricks

Databricks Free Edition is available in a serverless-only, quota-limited environment that contains many of the same features included with the full Databricks platform, with some limitations (see [Databricks Free Edition limitations](https://docs.databricks.com/aws/en/getting-started/free-edition-limitations)). With a Free Edition account, you can do the following:

* **Build AI applications and agents**: Prepare data, experiment with foundation models, and deploy, evaluate, and govern AI systems.
* **Collaborate on data science and ML projects**: Use shared notebooks to work with others in real time and publish results for others to review. Ideal for group projects, tutorials, and collaborative experimentation.
* **Explore and analyze real datasets**: Use the SQL editor or notebooks to build hands-on experience with querying and visualizing real data.
* **Create interactive dashboards**: Using Genie, interact with and visualize your data using natural-language prompts.
* **Design and test data pipelines**: Ingest, transform, and orchestrate data across different sources using Lakeflow.
* **Access the Databricks Assistant**: Use the Databricks Assistant to get in-product coding help. The Assistant can suggest, explain, and fix code directly within notebooks, queries, and files.

Sign up for the free Databricks Edition [here](https://login.databricks.com/signup?provider=DB_FREE_TIER\&dbx_source=www\&itm_data=dbx-web\&l=en-EN\&tuuid=98d945dd-21de-4a0f-a862-f9b81c75c654\&intent=SIGN_UP\&rl_aid=139139e5-008f-48fe-b582-f9125b0da75c\&sisu_state=eyJsZWdhbFRleHRTZWVuIjp7Ii9zaWdudXAiOnsidG9zIjp0cnVlLCJwcml2YWN5Ijp0cnVlLCJjb3Jwb3JhdGVFbWFpbFNoYXJpbmciOnRydWV9fX0%3D).

## 6. Exploration

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