How to Build a Coffee Shop Sales Dashboard with MySQL and Power BI | Part 1: SQL

TL;DR

Build the coffee shop sales dashboard by preparing and importing the raw data into MySQL, cleaning it, changing incorrect data types, and running SQL queries against the business requirements before comparing the results with Power BI. The MySQL portion follows six steps, while the dashboard includes three header KPIs, sparklines, heat maps, and interactive filters. Read on for the complete workflow and dashboard features.

Transcript

hey guys welcome back to my channel so in today's video we are going to see a data analyst portfolio project and in this we are going to make use of powerbi plus MySQL softwares so this will be a complete project and in this video we are going to see step by step complete Solutions and in front of your screen at at this end you can see the project ... Read More

Key Insights

• The project pairs MySQL and Power BI on a single coffee shop sales dataset, with SQL queries answering the business requirements first and the Power BI dashboard built to match those query results afterwards, so the numbers can be cross-checked between the two tools.
• The dashboard header carries three KPIs, total sales, total orders and total quantity sold, and each is accompanied by smaller sub KPIs that indicate month-on-month increase or decrease relative to the month chosen in a single-select month slicer.
• A calendar heat map renders the month as a calendar grid where darker colors mark higher sales days, and hovering over any day surfaces total sales, total quantity sold, total orders, and the revenue difference versus last month for that day.
• The stacked bar chart showing sales by store location across three locations is a customized build rather than a default Power BI visual, carrying extra information plus sub KPIs comparing current month store performance against last month.
• The daily sales trend chart includes an average line, and bars are colored differently depending on whether they fall above or below that average, making it immediately visible which days performed better than the period average.
• A second heat map plots days against hours of the day, so hovering on a cell such as Wednesday at hour nine returns total sales, quantity and orders for that day-hour combination along with revenue versus last month.
• Interactive action filters make every element clickable as a cross-filter: clicking weekday, weekend, a store location, a product category such as coffee, or an individual product such as espresso refilters the entire dashboard to that selection.
• The MySQL workflow proceeds in six ordered steps, data walkthrough, raw file preparation, database creation, file import, cleaning including data type changes, then business-requirement queries, and the import step alone is expected to take 15 to 20 minutes.

Questions & Answers

Q: How do you build a coffee shop sales dashboard with MySQL and Power BI?

Start by walking through and preparing the raw coffee shop sales file, creating a MySQL database, importing the file, and cleaning the imported data, including changing incorrect data types. Then run SQL queries for the business requirements and compare their results with the Power BI dashboard. The complete project is divided into two parts, with MySQL covered first and Power BI following in the second part.

Q: What are the six steps in the MySQL part of the project?

The six steps are data walkthrough, raw-file preparation, database creation, file import, data cleaning, and running SQL queries for the business requirements. Cleaning includes correcting data types after the file has been imported. The query results provide outputs that can be compared with the finished dashboard.

Q: How long does importing the coffee shop sales file into MySQL take?

The import is described as taking roughly 15 to 20 minutes. It is the fourth step, following raw-file preparation and database creation and preceding data cleaning.

Q: Can the MySQL queries be adapted for MS SQL Server, PostgreSQL, or Oracle?

Yes. Around 85 percent of the queries remain the same across MS SQL Server, PostgreSQL, Oracle, and MySQL, while about 15 percent require changes. The main differences involve date functions, which must be replaced with the corresponding functions for the chosen database.

Q: Which KPIs appear at the top of the Power BI sales dashboard?

The dashboard has three header KPIs: total sales, total orders, and total quantity sold. Each includes a sub KPI showing the month-on-month increase or decrease for the month chosen with the single-select slicer. Daily sparklines show higher and lower spikes, while the displayed data range updates as data is added to the database.

Q: How does the calendar heat map show coffee shop sales performance?

The calendar heat map displays the selected month as a calendar, using darker colors for days with higher sales and lighter colors for other days. Hovering over a date reveals total sales, total quantity sold, total orders, and revenue versus the previous month. Selecting another month updates the calendar.

Q: What does the daily sales trend chart reveal?

The chart shows daily sales for the month selected in the slicer and includes an average line. Bars above and below the average use different colors, making it easier to identify which days are performing better relative to the average.

Q: How do interactive filters change the Power BI dashboard?

Clicking weekday, weekend, a store location, a product category such as coffee, or a product such as espresso refilters the entire dashboard. Changing the selected month also updates every metric. These interactions let the dashboard show performance for a specific time period, location, category, or product.

Summary & Key Takeaways

• The project is a data analyst portfolio build covering a coffee shop sales topic using MySQL and Power BI together, split across two video parts. Raw data is downloadable from a link in the description. SQL queries are fired against the MySQL database to produce results matching business requirements, and those results are then compared against the finished dashboard.

• The final dashboard opens with three header KPIs for total sales, total orders and total quantity sold, each with sub KPIs showing month-on-month increase or decrease driven by a single-select month slicer. Each KPI also carries a sparkline of daily trends showing which days spike higher or lower. A dynamic data range at the top updates automatically as new data enters the database.

• Charts include a calendar heat map where higher sales days get darker colors and hovering reveals sales, quantity, orders and revenue versus last month; a customized weekday versus weekend split built with calculations; a customized stacked bar chart of sales by store location across three locations with month-on-month sub KPIs; and a daily sales trend with an average line coloring bars above and below average differently.

• Additional visuals cover sales by product category, top 10 sales by product, and a second heat map mapping days against hours to show sales performance by hour. Interactive action filters let a click on weekday, weekend, store location, product category such as coffee, or a product such as espresso refilter the entire dashboard, and switching months to June, March, April or February changes every metric.

• The MySQL portion runs through six steps: data walkthrough, preparation of the raw file for import, creating the database, importing the file, cleaning the imported file including data type changes, and firing SQL queries for the business requirements. Importing the file into MySQL is described as a long process taking roughly 15 to 20 minutes.

• The queries transfer to other databases with modest rework. Around 85 percent of the queries stay the same on MS SQL Server, PostgreSQL or Oracle, while about 15 percent, mainly date functions, need alternative functions that can be looked up. The description timestamps mark Project Overview at 02:00, Business Requirements at 14:18, Data Walkthrough at 28:22, and Total Sales Analysis at 56:26.