Introduction

After learning more about data, I wanted to test my SQL skill and my Data Visualization skill, and I think one of the best way to test it by querying Data from Database and use it to create a Dashboard because Dashboard usually have many Visualization, therefore I create this Sales Analysis Project.

Sales Analysis is a process of using data to understand how the business performs in terms of sales. With a dashboard as the end result of this project, we will be able to gather valuable insights from it, and analyze AdventureWorks Sales Performance.

The Data

AdventureWorks Database is a sample database that provided by Microsoft. AdventureWorks database supports standard online transaction processing scenarios for a fictitious bicycle manufacturer - Adventure Works Cycles. More on AdventureWorks Database..

Software Used

Objective

The objective is given by a fictitious Sales Manager (Rahmat) through e-mail, below is the e-mail.

From that E-mail we can conclude that our goal is to Create Interactive Dashboard:

1. That shows the numbers of sales, profits, orders, and customers
2. That shows the top performing products and customers
3. That shows the numbers of sales against budget
4. That shows map of customer with their sales number

And an additional things I'm going to do is gather some insights from the dashboard.

Chapter 1 - SQL

SQL stands for Structured Query Language. It's a programming language used for managing and manipulating data in relational databases. Think of a database as a well-organized storage system for information, SQL is the tool that helps you find, sort, and organize those information.

Query the data needed from the database.

Before we start loading the data to Power BI, the first thing we need to do is query or request data from the database system which in this project called AdventureWorksDW2019 Database. The AdventureWorksDW2019 Database contains tables from multiple aspects of the company like customer, employee, sales, etc.
This is where SQL skills come in handy, we can request a data that we only need without including other unused tables and we can also transform some of the data to match the spesific format that we want.

The Table that we going to use is:

• DimCustomer
• DimDate
• DimProduct
• FactInternetSales

Dim is for Dimension Table, and Fact is for Fact Table. The main difference is Fact Tables contains Measurement, metrics, or facts about business process while Dimension table is a companion to Fact Table that contains descriptive attributes.

There are 4 query files that contains SQL code to query the data needed, here are the codes.

DimCustomer SQL Code

DimDate SQL Code

DimProduct SQL Code

FactInternetSales SQL Code

Chapter 2 - Power BI

After we finished querying all data that we need, we're going to extract the result into .csv file. After that, load the extracted result + Sales Budget spreadsheet that has been given via E-mail into Microsoft Power BI to build the Dashboard.

Relationship Data Model

This is the Relationship Data Model after we import all the data and connect each table with key, from here we are able to see the difference between dimension table and fact table easier, as the fact table connects to multiple dimension table.

Creating Key Measure for Calculating Sales and Budget Differences

In Power BI, a measure is a calculated value or metric that you can define and use to perform calculations or aggregations on your data, such as sums, averages, counts, percentages, or ratios.

We're going to create measure that can show the Sales - Budget number and Sales/Budget differences Percentage.
There are 4 measures that we're going to create to achieve that:

1. Total Sales = SUM(Fact_Internet_Sales[Sales_Amount])
2. Total Budget = SUM('Sales Budget'[Budget])
3. Sales - Budget = [Total Sales] - [Total Budget]
4. Sales and Budget Differences = ([Total Sales]-[Total Budget])/[Total Budget]

Slicer for month and year

The first thing to do is to create a slicer that can filter all the values in the dashboard to selected month and year.

1. Sort the Month_Short column by Month_Number to prevent the system sorting by alphabet.
2. Click the slicer icon to build a slicer, and insert Month_short to the field.
3. After formatting the slicer, copy & paste it, and replace Month_Short with Year in the field.

Line Chart

Next, we're going to create a Line Chart that can show Sales vs Budget Overtime.

1. Click Line Chart Icon and put Month_Short in X Axis and Sales_Amount and Budget in Y Axis.
2. Change the color of Sales to Blue and Budget to orange to make a clear distinction between them.

Top 10 Products and Customers

Next, we're going to create a Bar Chart that can show The Top 10 Customers and Products by Sales.

1. Click the Bar Chart Icon and put Full_Name in Y Axis and Sales_Amount in X Axis.
2. Open the Filter Menu and filter the name by Top 10 of the Sales_Amount.
3. Last thing to do is to add Color formatting. The highest value will be bolder, and lowest will be lighter.
4. Copy paste the bar chart and replace the Full_Name in Y Axis to Product Name from DimProduct Table and also add Sub_Category to Tooltips so we can see the sub category. So we don't need to recreate the bar chart again for the products.

The Number Cards

Next is the number card. These card will be the main thing that people see because the numbers in the card will change based on the selected month, year and even products/customers when they click it.

1. For the first number we're going to need to import a new visual, the name is Dynamic KPI. After that we're going to click the icon and put the measures we create in the beginning, Sales_Amount in Indicator, Sales - Budget in Comparison 1, and Sales and Budget Difference Percentage in Comparison 2.
2. For the other 3 numbers we just need a normal card and put Profit into fields, do the same for Total Customer (Customer_Key) and Total Order (Sales_Order_Number)

Map of Customers by sales

Last step is creating Map of customers by their sales. The higher the sales the bolder the color.

1. Click the filled Map icon and put Customer_State in Location field and Sales_Amount in Tooltips.
2. Color Formatting the map by Sum of Sales_Amount, and give bolder color for highest amount and lighter color for lowest.

Chapter 3 - Result

Power BI requires Pro/Premium License for me to be able to embed the Dashboard to public, therefore I just share the GIF of the Dashboard. You can view the Dashboard in detail by Downloading Power BI through Microsoft Store App and then download the Dashboard file (.pbix) under the GIF below or in github repo to view my Dashboard.

The Dashboard

Insight and Recommendation

These are the insights that I obtained from the dashboard and also I provide some recommendation based on the insight.

1. Line Chart & Number Card

2. Line Chart

3. Bar Chart

4. Filled Map & Number Card

Conclusion

Looking data from dashboard can really made a difference from just a normal report/visualization. The flexibility in dashboard and the amount of information you can fit into it is what makes dashboard amazing, it is dynamic not static. Dashboard is a really good tools for monitoring data, people can use it for monitoring performance, product quantity, sales made, etc. etc.

AdventureWorks Cycle has gained a massive amount of Sales increase in the year of 2022, with this sales increase, that means the profit is getting bigger, brand awareness is growing, customer is increasing and they will spend more.
These all are a really good positive, but there are always a room for improvement such as expanding the business, optimizing Inventory, retain the customers, and analyzing different aspects of the business. A business should never be satisfied, always achieve to grow and expand.