Goal-Driven Analytics: Using GCP and Power BI to Visualize English Premier League Data

Hello! It’s been a while. On the road to becoming a competent ML Ops Engineer, I figured I should comprehensively document the transformative steps I took to get there.With the deadline for the much-awaited English Premier League 2024–2025 season viciously approaching (I finished this article watching the opening match: Manchester United vs Fulham), I created an elementary, easy to follow, ETL pipeline using English Premier League data and created an interactive dashboard to showcase this feat. Here is how you can go about it as I did for your portfolio:

Press enter or click to view image in full size

Figure 1: Old trafford

Requirements.

1. Google Cloud Platform Account: Google Cloud Platform offers a 90-day, $300 credit-free trial. You can use this link to register.

2. Power BI: The medium for visualizing the data

3. Google Collab: An IDE for experimenting with transformations

4. Mage.ai: Open-source data pipeline tool for transforming and integrating data. ( An alternative to Apache Airflow)

5. NovyPro: Web platform where you can showcase your visualization projects i.e PowerBI, Tableau

Project summary.

Press enter or click to view image in full size

Figure 2: The project architecture

This data engineering project aims to extract English Premier League data, apply various data transformation steps to enrich the dataset quality, and load it onto a cloud platform for data visualization on Power BI. This article will be divided into four steps: Extract, Transform, Load, and Visualize. Let’s get into it.

Extract

I used data from the 2009–2024 seasons which can be found here. I used Google Collab to perform basic transformations for experimentation such as renaming columns and extracting and splitting date time information from the date column. The notebook that I used for experimentation can be viewed here. I prefer using Google Collab for my project experimentation due to its robustness, ease of use, and extensive documentation.

Once I got the data transformation steps nailed down, it was time for the dirty work to begin. First up, we have Google’s Cloud Platform that I used for:

• Storing the raw/unclean dataset on GCP’s Cloud Storage and configuring its access to the public.

• Deploying a virtual engine that acts as our trigger for the pipeline on Mage.ai to begin data transformation.

• Storing the clean/processed data on GCP’s BigQuery.

Figure 3–9: Setting up the data set to be publicly accessible.

I created a GCP project that acts as a container for organizing and managing all GCP resources. Proceed to create your own through this link. Your dashboard should look like this after creation.

Press enter or click to view image in full size

Figure 3: Creating a GCP project

A Storage Bucket is a scalable and durable container for storing your data (e.g., files, backups, and images). It’s part of the Google Cloud Storage (GCS) resource, which is an object storage service. Its key uses are:

• Data Storage: Store any amount of data, from a few bytes to petabytes.

• Data Access: Access data via RESTful API from anywhere, making it ideal for web and mobile applications.

• Data Management: Organize data with folders, manage access permissions, and set lifecycle policies.

Go ahead and type “Cloud Storage” on the search bar in your project dashboard. Create a storage bucket that will store our data. When configuring access, uncheck “Enforce public access prevention on this bucket”. This will make it accessible to the public as is crucial to this project.

Press enter or click to view image in full size

Figure 4: Creating the storage bucket for our data

Press enter or click to view image in full size

Figure 5: Configuring access to the bucket

Once the storage bucket is created, upload the data and proceed to change its access level which is set to ‘not public’. Navigate to the Bucket Level -> Permissions and switch to Fine-Grained. This allows you to manage permissions at a more detailed level, specifically at the individual object level within a bucket.

Press enter or click to view image in full size

Figure 6: Uploading the premier league data

Press enter or click to view image in full size

Figure 7: Access is set to “Not Public”

Press enter or click to view image in full size

Figure 8: Switch to Fine-Grained

Press enter or click to view image in full size

Figure 9: Enabling Public Access

Transform

Google Compute Engine (GCE) is a service that provides scalable, flexible virtual machines (VMs) on Google’s infrastructure. It’s part of the Google Cloud Platform (GCP) and offers users the ability to run applications on virtual hardware. I used it to deploy the ETL pipeline through Mage.ai which is an Open-source data pipeline tool for transforming and integrating data.

Search for a Virtual Machine Instance on the search bar and click on it. Give it a preferred name during configuration.

Figure 10–16: Setting up the virtual machine

Press enter or click to view image in full size

Figure 10: Creating a virtual machine

Press enter or click to view image in full size

Figure 11: Configuring a virtual machine

Before installing mage to the VM, we need to configure the VM to accept traffic from any IP address. This allows us to access the VM from anywhere on the internet once the instance is live and the transformation can be triggered. Navigate to VPC Network under the Networking section. Click on “create firewall rule” and change the following settings.

Name: allow-web-trafficDirection: IngressAction: AllowTarget: All instances in the network (or specific instances)Source IPv4 ranges: 0.0.0.0/0Protocols and Ports: TCP: 0000

Press enter or click to view image in full size

Figure 12: Configuring the network rules

Navigate to your VM dashboard and click on SSH to initialize the virtual machine. You will need to install some Python libraries and install mageai within a virtual environment.

sudo apt-get update -y  -- Updates the package lists for the APT package managersudo apt-get install python3-distutils   -- Installs the python3-distutils package and provides tools for building and installing Python modulessudo apt-get install python3-apt --Provides Python 3 bindings for the APT package management system.sudo apt-get install wget --Enables downloading files from the web via the command linewget https://bootstrap.pypa.io/get-pip.py --Downloads the get-pip.py script from the Python Packaging Authoritysudo apt-get install python3-pip --Allows installation and management of Python packages.sudo apt install python3.11-venv - Provides the venv module for creating Python 3.11 virtual environments.python3 -m venv my_env  -Sets up an isolated Python environment.source my_env/bin/activate - activate the environpip install mage-ai - Install mageAimage start demo_project -Sets up the project structure and configuration for machine learning workflows.

The port number will provided as a result. Append your VM network link to your favorite search engine i.e 34.28.166.110:6780 (VM link: port number). This will take you to the mage.ai dashboard

Press enter or click to view image in full size

Figure 13: Mage AI dashboard

Click on New Pipeline and give it a name. You require a loader, a transformer, and a data exporter. The loader is used to load data into the pipeline from various sources. The transformer is used to perform your various transformations, and the exporter is used to ship your data to various destinations. Since the information about using these blocks is vast and meta, you can follow through here. You can also follow this tutorial, which helped me a lot.

Press enter or click to view image in full size

Figure 14: Mage data blocks

Press enter or click to view image in full size

Figure 15:Data transformation steps into the data transformer block

Press enter or click to view image in full size

Figure 16: Data Exportation

Visualization

Once data is loaded to BigQuery, connect it to your Power BI as your data source. Open up PowerBI -> Get Data, and connect it to BigQuery. You will be prompted to sign in to your Google account. Enter your credentials to authenticate.

Press enter or click to view image in full size

Figure 17: Data connector on Power BI

Finally, be creative with your dashboard. Here is mine.

Press enter or click to view image in full size

Figure 18: Power BI Dashboard

You can interact with this dashboard which was hosted via Novypro.comEnglish Premier League (2009 - 2024) Fixture Summary DashboardCheck out this PowerBI Project by Nyambok Juliusproject.novypro.comThank you.