Analysis and visualization of Olympic Games for news readers (SQL | Power BI)
For this project, I used Microsoft SQL Server Management Studio and Microsoft Power BI.
Background
The summer Olympic Games, also known as the Games of the Olympiad, and often referred to as the Summer Olympics, is a major international multi-sport event normally, is a major international multi-sport event normally held once every four years. The inaugural Games took place in 1896 in Athens, Greece, and the most recent edition was held in 2021 in Tokyo, Japan. The international Olympic Committee (IOC) is responsible for organizing the Games and for overseeing the host city’s preparations. The tradition of awarding medals began in 1904; in each Olympic event, gold medals are awarded for first place, silver medals for second place, and bronze medals for third place. The Winter Olympic Games were created out of the success of the Summer Olympic Games, which are regarded as the largest and most prestigious multi-sport international event in the world.
It was found that providing news readers with a predefined data insight might not sufficiently satisfy the interest of the reader and raw data will be overwhelming. Since this is a multi-sport game, a well-designed, interactive visualization will sufficiently cover the needs or interests of the news reader.
In order to effectively respond to the questions, I followed the steps of the data analysis process which include: ask (the business task), prepare, process, analyze, share, and act.
Business Task
As a data analyst working at a news company, you are asked to visualize data that will help readers understand how countries have performed historically in the summer Olympic Games.
You also know that there is an interest in details about the competitors, so if you find anything interesting then don’t hesitate to bring that in also.
The main task is still to show historical performance for different countries, with the possibility to select your own country.
Prepare
This stage is about data collection and preparation for cleaning. The database was downloaded in the .bak format. The database was then loaded into SQL Server Management Studio and contains 12 fields (columns) namely: ID, Name, Sex, Age, Height, Weight, NOC, Games, City, Sport, Event, and Medal as previewed below. The data contains 27116 records between Summer 1896 and Summer 2016.
Process
During this process, I processed the data from dirty to clean. I began by changing Name column to Competitor_Name and “F” and “M” in the Sex column to Female and Male respectively using the following SQL statement:
I then introduced Age_Group using SQL Case statement below
I also renamed NOC as Nation_Code for better understanding. It was noticed that year was combined with the season which will make the analysis and visualization quite tedious. So, I decided to split it using these lines of code.
Finally, where Medals are “NA”, they were renamed as “Not Registered” and a WHERE clause was added to fetch summer data.
Analyze
In this process, necessary calculations required to achieve the desired visual result were made. Since the data have been cleaned in SQL Server, I imported the data on Power BI by loading the database along with the full SQL Statement.
In order to have an effective visualization, I create two measures to dynamically calculate the number of competitors and number of registered medals (which only includes Gold, Silver and Bronze but excludes Not Registered). To achieve this, I wrote the following DAX Expressions:
Share
Using these calculations on the data, interactive visuals were created. Below is an image of the interactive report created as response to the business task.
Click here to access the interactive report on Power BI.
Act
This final stage of the project is for the readers to make use of this interactive visualization. In order to effectively solve the business task and allow the users to have some interesting level of visualization, I have added four slices which will allow news readers to filter based on year, country, competitor name, and sport type. These will help readers to have a good understanding of how countries have performed over the years in the Summer Olympic Games.
Conclusion
The interactive report has been designed with utmost priority on fulfilling the business tasks and convenience of readers. Readers can now filter to gain insight on competitors as well as their country’s performance.
Thank you for reading. Please be free to add comments and reviews.
You can connect with me on LinkedIn and Twitter. Thank you for your time.