DATA ANALYSIS PROJECT — OLYMPIC GAMES ANALYSIS

Business Problem

The analysis required for this project is given below. It has been ensured that the right file has been selected, processed, and analyzed which will help business users to make a decision.

“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 Collection and Table Structure

The Data collected was uploaded into SQL query for transformation where a few criteria were added to age to understand age groups, Gender for gender bifurcation. Also, the non-registered medal list was converted from NA to Non-Registered.

SELECT [ID]
,[Name] AS ' Competitor Name' -- Column Renamed--
, CASE WHEN Sex = 'M' then 'Male' Else 'Female' End as Sex -- Better name for filters and visualisations--
,[Age]
,case when [Age] < 18 then 'Under 18'
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'
--   ,CHARINDEX (' ',Games) - 1 AS 'Example 1'
--  ,CHARINDEX(' ', Reverse(Games))-1 as 'Example 2'
, Left(Games, Charindex(' ',Games) -1) as 'Year' -- Split columns to get year wrt Season --
--  ,[Games]
,--[City]-- not required as we need only country--
[Sport]
,[Event]
,CASE WHEN Medal = 'NA' THEN 'Not Registered' ELSE Medal END AS Medal -- NA WILL BE REPLACED AS NOT REGISTERED--
FROM [olympic_games].[dbo].[athletes_event_results]
WHERE RIGHT(Games,CHARINDEX(' ',REVERSE(Games))-1) = 'Summer'

DATA MODEL

The below view shows the dimension and fact has been combined

CALCULATION IN POWER BI

NO OF COMPETITIOR = DISTINCTCOUNT('OLYMPIC GAME DATA'[ID]) 
NO OF MEDALS = COUNTROWS('OLYMPIC GAME DATA') 
NO Of Medals (Registered) =  CALCULATE(     [NO OF MEDALS],     FILTER(         'OLYMPIC GAME DATA',         'OLYMPIC GAME DATA'[Medal] = "Bronze"             || 'OLYMPIC GAME DATA' [Medal] = "Gold"             || 'OLYMPIC GAME DATA'[Medal] = "Silver"     ) )

Finally, the visualization, which has filters that give users easy options to navigate through the history.

Thanks for reading :)