Business Requests and User Stories
The challenge for this data analyst project is outlined below. This has been used continuously to ensure that the right data has been selected, transformed and used in the data visualization which is meant to be passed on to the business users.
“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.”
Data Transformation Using Sql
The necessary data was first put into a SQL database and afterwards transformed using the transformations that you can see below.
Transformed Table
--Transformed table
select
ID,
[Name] as [Competitor Name], --Renamed Column
case when sex = 'M' then 'Male' else 'Female' end as Sex, --Better name for filters and visualization
Age,
case when Age < 18 then 'Under Age'
when Age between 18 and 25 then '18-25'
when Age between 25 and 30 then '25-30'
when Age > 30 then 'Over 30' end as [Age Grouping],
Height,
[Weight],
NOC as [Nation Code],
--Explained Abbreviation
--CHARINDEX(' ',Games)-1 as [Example 1],
--CHARINDEX(' ',reverse(Games))-1 as [Example 2],
LEFT(Games, CHARINDEX(' ', Games)-1) as [Year],
--Split Column to isolate Year based on space
--right(Games,CHARINDEX(' ',reverse(Games))-1) as [Season], --Split Column to isolate season based on space
--City, commented out as it is not necessary for the analysis
--Games,
Sport,
[Event],
case when Medal = 'NA' then 'Not Registered' else Medal end as [Medal] --Replaced NA with not registered
from
athletes_event_results
where
right(Games, CHARINDEX(' ', reverse(Games))-1) = 'Summer' --Where Clause to isolate summer season
Importing Data to Power Bi Using Sql Queries
Importing Table to Power Bi with the help of Sql Queries
Data Model
Data table and the different fields in it.
Dax(Data analysis expressions):
Here i have created a seperate table for measures. I have made several measures in this project.
As this is a view where dimensions and facts have been combined, the data model that is created in Power BI is one table. The query from previous step was loaded in directly.
Olympic Games Analysis Dashboard
The finished dashboard consist of visualizations and filters that gives an easy option for the end users to navigate the summer games through history. Some possibilities are to filter by period using year, nation code to focus on one country or look into either a competitor or specific sports over time.
