Power BI Project | How to Analyze Formula 1 Team Journeys in Power BI and SQL Server

Join me as I gather, analyze, and visualize F1 teams' vast journeys across the 2023 calendar year!

From Bahrain through Europe, the Americas, Australia, Asia, and the Middle East, we analyze the distances covered in SQL Server and build a Flow Map in Power BI.

We're doing all the data collection ourselves here as well. No fancy tricks, APIs, Python code. Just me, Excel, Bing, and the F1 Website.

Note: this is a VERY long format video, so please use the chapters! They're located in the scrollbar or down below and get you exactly where you need to go!

Credits:
Trancy Le's Power BI Distance formula: https://medium.com/@trancyle/calculate-the-distance-between-locations-with-longitude-latitude-in-power-bi-d35a18056760

How to help Maui Wildfire victims: https://www.hawaiinewsnow.com/2023/08/09/want-help-those-devastated-by-maui-wildfires-heres-how/

#formula1 #sqlserver #sqlserver #powerbi #bi #businessintelligence #data #dataanalytics

Chapters:
00:00:00 - Start

00:01:47 - Introduction

00:02:10 - Data Intro - What are we going to track?

00:03:17 - LunaCam (HuskyCam intro)

00:03:56 - Checklist - Steps we'll take to complete the project

00:06:07 - Maui Wildfires - How you can help.

00:07:48 - Reviewing Distance formula

00:09:35 - Gathering Data from Formula 1 Website in Excel

00:20:10 - Gathering Latitude and Longitude for each track with Bing Maps

00:46:50 - Creating an "Event" table which uses TrackID and catalogues the dates at each track

00:56:34 - Saving data to CSV files for importing

00:58:15 - Importing Data via Powershell and DBATools

01:07:45 - Investigating slowness on the server :(

01:09:55 - Selecting the data in SQL Server and reviewing

01:12:49 - Checking the data quality using JOINs

01:18:28 - Creating SQL views to consume the data in Power BI

01:26:25 - Power BI time! Importing our data into the report (with notes about Import vs. Direct Query)

01:33:17 - Checking our data out in the Power BI report and starting our visuals

01:38:30 - Updating datatypes in SQL Server

01:53:45 - Adding the Flow Map via the Visuals Store in Power BI

01:55:00 - Adding a new Flow map to the Power BI Report

01:55:40 - Combining Columns for Latitude and Longitude into one using Power BI

01:57:45 - Checking our track locations on a standard map in Power BI.

01:59:05 - Adding slicers filters by Track Country and Track Name, and starting our visual changes.

02:02:25 - Using Excel to build "Journey" data by building dataset about trips from track to track

02:20:30 - Importing CSV data via SSMS Import Wizard

02:25:30 - Querying Imported data and joining tables together in T-SQL

02:30:28 - Querying From/To coordinates via SQL and preparing for Power BI

02:43:50 - Using new From/To Coordinates views in Power BI.

02:44:22 - Modifying datasets in Power BI to use new sources.

02:54:10 - Building our Flow map using Origin and Destinations.

02:58:30 - Calculating Distance using Power BI Measures

03:08:25 - Adding Distance to Power BI Map

03:12:25 - Converting km to miles via Power BI formula (if needed)

03:26:50 - Adding Distance to Power BI Flow map and changing colors based on distance thresholds.

03:28:45 - Creating the Distance KM formula in T-SQL

03:38:05 - Creating the distance "color" column in T-SQL

03:42:50 - Adding Color to Power BI Map

03:47:20 - Adding filters to map page

04:05:30 - Final Visual Preparations

04:12:40 - Recap and outro00:01:47 - Start/Intro

Видео Power BI Project | How to Analyze Formula 1 Team Journeys in Power BI and SQL Server канала SQLDevDBA