top of page

SALES ANALYTICS USING SSMS, SQL, AND POWER BI

image.png
image.png
Entity relationship.png
image.png

Click on the image to view the interactive dashboard

BUSINESS REQUEST & USER STORIES

image.png

The goal of this project is to develop a comprehensive executive sales report designed for sales managers and sales representatives. This report will provide a detailed daily overview of sales performance, broken down by product and customer. The key objectives are to:

​

  1. Enhance Visibility: Offer clear insights into daily sales metrics for better tracking and assessment.
     

  2. Drive Performance: Support sales managers and representatives in meeting and exceeding their targets.
     

  3. Facilitate Strategy: Enable informed decision-making and strategic planning based on up-to-date sales data.
     

This report can be an essential tool for the sales team to monitor progress, identify opportunities, and refine their sales strategies effectively.

DATA CLEANSING & TRANSFORMATION IN SQL

  • The data for this project was extracted from the AdventureWorks Sales database provided by Microsoft using SQL Server Management Studio (SSMS).
     

  • The dataset contains various dimension and fact tables. Additionally, a budget data source was connected as an Excel file to the data model later in the process. For this project, I selected the following tables:
     

    • Dimension Tables: Date, Products, and Customers

    • Fact Tables: Internet Sales, Budget
       

  • Below are the SQL queries used to clean and transform the data for further analysis.

DIM_Calendar

image.png

DIM_Products

image.png

DIM_Customers

image.png

FACT_Internet Sales

image.png

As mentioned earlier the FACT_Budget table was added as an Excel file which is shown in the Data ​Model section below.

DATA MODEL

Entity relationship.png

  • The data model in Power BI consists of FACT and Dimension tables connected as follows:
     

    • The DIM_Calendar table is connected to the FACT_Internet Sales table using the DateKey (one-to-many relationship).
       

    • The DIM_Calendar table is also connected to the FACT_Budget table using the DateKey (one-to-many relationship).
       

    • The DIM_Customer table is connected to the FACT_Internet Sales table using the CustomerKey (one-to-many relationship).
       

    • The DIM_Product table is connected to the FACT_Internet Sales table using the ProductKey (one-to-many relationship).
       

  • These connections ensure that the data is accurately linked and can be analyzed effectively in Power BI.

SALES ANALYTICS BI DASHBOARD

There are three main dashboards that can be used to keep track of different aspects of the sales data: Sales Overview, Customer Details, and Product Details. Let me walk you through each one.

Sales Overview

Sales overview.png

Click on the image to view the interactive dashboard

In my Sales Overview dashboard, the stakeholders can keep a close eye on the overall sales performance. Here’s what it includes:
 

  • Sales vs Budget: This shows the key performance indicators, such as total sales and how they compare to the budget.
     

  • Filters: They can filter the data by Customer City, Sub Category, Product Category, Product Name, and Year to get a more detailed view.
     

  • Sales by Product Category: This chart shows how sales are distributed across different product categories like Bikes, Accessories, and Clothing.
     

  • Sales and Budget Amount by Month:  This line chart can be used to compare monthly sales against the budget, which would help the sales manager & sales representative to spot the trends and variations throughout the year.
     

  • Sales by Top 10 Customers: This bar chart ranks the top 10 customers based on their sales figures.
     

  • Sales by Top 10 Products: Here, they can see which products are the top sellers.
     

  • Sales by Customer City: This map visual would help the stakeholders understand sales distribution across different cities.

Customer Overview

My Customer Details dashboard focuses on detailed analytics related to the customers, providing insights into their behaviors and contributions to sales. It includes:
 

  • Filters: Similar to the Sales Overview, the stakeholders can filter data by Customer City, Sub Category, Product Category, Product Name, and Year.
     

  • Sales and Budget Amount by Month: This line chart helps them compare monthly sales against the budget.
     

  • Sales by Customer City: This visual representation shows how sales are distributed across various cities.
     

  • Customer Sales Data: This section provides detailed information on individual customer sales, including their monthly sales figures.

Product Overview

In the Product Details dashboard, I've dug deeper into product performance to understand sales trends at the product level. Here’s what it includes:
 

  • Filters: Similar to the other dashboards, data can be filtered by Customer City, Sub Category, Product Category, Product Name, and Year.
     

  • Sales and Budget Amount by Month: This line chart lets the stakeholders compare monthly sales against budgeted amounts for different products.
     

  • Sales by Top 10 Products: This bar chart highlights the top-selling products with their sales figures.
     

  • Product Sales Data: Here, the stakeholders can get detailed sales information for individual products, including their monthly sales figures.

© 2024 by Aditya Prakash.

bottom of page