Power BI — Classic Car models Visualisation

SQL

SQL (Basic and intermediate level)

First of all, classic car model’s dataset contains 8 different tables. I needed to combine some tables together to understand the data so that I organised the data using SQL by using joins. I also used the group by with some aggregation functions with MySQL to get some basic statistical information from the data set. I have some ETL experience where I applied them to create some queries which helped me to find initial answers before I look at it on the PowerBI dashboard.

The EER diagram below shows the relationships between the tables.

1) — Which product line sells the most?

SELECT SUM(quantityOrdered), products.productCode, products.productName,orderdetails.priceEach,products.productLine
FROM classicmodels.products
JOIN classicmodels.orderdetails
ON classicmodels.products. productCode = classicmodels.orderdetails. productCode
Group by products.productLine;

2) — Which customer spends the most?

SELECT SUM(quantityOrdered*priceEach) as sales,customers.customerNumber,customers.customerName,customers.country, orderdetails.productCode,orderdetails.quantityOrdered,orderdetails.priceEach
FROM classicmodels.orders
JOIN classicmodels.customers ON classicmodels.orders.customerNumber = classicmodels.customers.customerNumber
JOIN classicmodels.orderdetails ON classicmodels.orders.orderNumber= classicmodels.orderdetails.orderNumber
GROUP BY customerNumber;

3) Finally, let's join all the tables together

SELECT products.productCode, products.productName,orderdetails.priceEach, orderdetails.quantityOrdered,
products.productLine, orderdetails.orderNumber, orders.orderdate,orders.customerNumber,
customers.customerName,customers.city,customers.country, products.buyPrice,products.MSRP
FROM classicmodels.products
JOIN classicmodels.orderdetails
ON classicmodels.products. productCode = classicmodels.orderdetails. productCode
JOIN classicmodels.orders ON classicmodels.orderdetails.orderNumber=classicmodels.orders.orderNumber
JOIN classicmodels.customers ON classicmodels.orders.customerNumber= classicmodels.customers.customerNumber;

PowerBI

Data, facts, and Insights

Visualisation would enable us to understand the data better and become a storyteller when we speak about insights with data. We should tell a story which is easy to understand. This begins with your audience, products, market or competitors, etc. Your data could also be complex and have many inputs to visualise while creating your dashboard. That is why I think we should keep it simple.

After joining the tables in SQL, I then exported the data into a CSV file which I then imported into PowerBI, after I adjusted the data type settings

I will give some examples below of visualisations I created using PowerBI for the classic models dataset discussed above, after ensuring the data type of each column is correct.

Number of Orders by time

For example: To investigate which year has the most sales for classic cars, I decided to pick the line chart shown in the graph below. The peak time of the year was on November for orders.

We can see the facts, but we also need to read this data. Power BI has a nice tool which is called smart narrative. This narrative is generated by the data. Once we click the right mouse button on the line graph, we will find the summarize option. All data can be converted as a text, as in the paragraph below:

MAP

I always find the Map Visualisation very enjoyable. You can see the whole world and that is very useful to see the big picture. You can gather all the data into the map. The graph below illustrates which countries ordered the most products among those years by product line. Once I hover over the rectangle with my mouse, these details can appear.

KPI

Key Performance Indicators (KPI) play a crucial role to meet the company’s goals, evaluation of the company’s objectives, and how the company can positively impact. There are many KPI metrics to create a dashboard or report in terms of being measurable, actionable, and specific. I decided to focus on the actionable insights and I found some meaningful and positive results in my project. I wonder whether the classic car models could make profit or revenue by product line and compare one against another in 2003 and 2004.

For instance: The bar chart emphasized the target revenue where I found the classic cars product line was the highest revenue against trains, at the same time the vintage car product line reached the target revenue. The target revenue was calculated hypothetically to be 2 million. There also are two KPI boxes displaying the comparison of profits by years, and another one is the comparison of the revenue between two product lines in terms of total revenue (motorcycles versus classic cars product lines).

Power BI Drill Through

With drill through in Power BI reports, this capability can help us drill to another report which I found was a very useful feature for storytelling. For example: this feature can be used to see the wider information across the organisation as well as granularity by revenue, country, product line, year, etc. You can create a meaningful sales report, as illustrated below:

Decomposition Tree

This visual can be decomposed using one metric of the data to analyse the other multiple dimensions. As far as I was concerned, this visual enabled us to see the customer’s journey better. This is a great way to see customer’s details, product’s breakdown in a detailed way. Microsoft states that “the decomposition tree visual in Power BI lets you visualize data across multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order. It is also an artificial intelligence (AI) visualization, so you can ask it to find the next dimension to drill down into based on certain criteria. This makes it a valuable tool for ad hoc exploration and conducting root cause analysis”.

This visual aids can breakdown the data for deeper analysis, as shown below. I have broken down the profit to see the multidimensions such as country, customer name, and product line:

The last section: Insights of Classic car models

After creating the dashboard, it is time to share with stakeholders, audiences, and your clients. Once the dashboard publishes your insights and data, that will help make better decisions and analysis of the positive and negative impacts. You can see some insights of the classic car models below:

• The USA has the highest revenue and sales in these years 2003,2004 and 2005 as followed by Spain, France, Australia, and New Zealand.
• Whilst at the Product line, classic cars, and vintage cars models has increased among these top 5 countries, ship and trains have declined during that period. Classic cars were the most popular and most attractive models for the audience in these countries.
• Australia has ordered a total of amount 1945 classic cars between 2003 and 2005. This number was the second highest order number among all product lines.
• The month of November was an exception to otherwise relatively consistent orders from 2003 to 2005. This is because of Thanksgiving or early Christmas shopping affecting the sales positively.