MS Power BI Project: Sales Analysis

Hi, I am Alok Pratap Singh. Here is my first MS Power BI Project: Sales Analysis. Here I used MS Power BI for Data visualization, SQL for data cleaning, and converting data into CSV.files from where I can get the data.

See in Github

Video: MS Power BI Project: Sales Analysis

Business Requirment:

The client made a requirement on behalf of a business in which one has some requirements. The sales manager wants an executive sales report. Based on his request, here I create a story that tells the beginning and the delivery of a report. The story ensures that acceptance criteria are maintained throughout the project or not.

# Representatives Business request Values to Add Acceptance Criteria
1 Sales Manager A dashboard overview for sales Can get through customers and products sales better An MS Power BI dashboard
2 Sales Representative
An overview of Sales per Category

Category sold the most
A Power BI dashboard allows to filtering data as per categories
3 Sales Representative A detailed overview of Sales Customers that buy the most A Power BI dashboard that allows filtering data as per customers
4 Sales Manager Top Five Models and Customers on Sales overview Models and Customers against Sales A Power Bi dashboard with Top five Models and customers by using filter

Data Cleaning Through MS SQL:

A data model is needed for an error-free report. Only with better data analysis, it is possible to create the necessary data model. Here cleaning data and transformation process is done using MS SQL.

Once the cleaning process is done, the cleaned data is converted to CSV files for further use in the MS Power BI Report.

Here are the SQL statements that have been used for cleansing and transforming the required data.

Career Choices

 

Calendar Table:


/ Script for SelectTopNRows command from SSMS /

SELECT
--[DateKey]

[FullDateAlternateKey] as Date,



-- ,[DayNumberOfWeek]

[EnglishDayNameOfWeek] as Week,
Left (EnglishDayNameOfWeek, 3) AS WeekShort,

--,[SpanishDayNameOfWeek]
--,[FrenchDayNameOfWeek]
-- ,[DayNumberOfMonth]
--,[DayNumberOfYear]
--,[WeekNumberOfYear]

[EnglishMonthName] as Month,
Left (EnglishMonthName, 3) as MonthShort,

--,[SpanishMonthName]
--,[FrenchMonthName]
--,[MonthNumberOfYear]

[CalendarQuarter],
[CalendarYear]

--,[CalendarSemester]
-- ,[FiscalQuarter]
-- ,[FiscalYear]
-- ,[FiscalSemester]

FROM
[AdventureWorksDW2019].[dbo].[DimDate]

Category:

 

   /Script for SelectTopNRows command from SSMS /

SELECT [ProductCategoryKey]
  --,[ProductCategoryAlternateKey]

  ,[EnglishProductCategoryName] CategoryName

  --,[SpanishProductCategoryName]
  --,[FrenchProductCategoryName]

FROM [AdventureWorksDW2019].[dbo].[DimProductCategory]

 

 

Cutomer:


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
  [CustomerKey], 
  [GeographyKey], 

  --,[CustomerAlternateKey]
  --,[Title]
  
  [FirstName], 
  --,[MiddleName]
   
  [LastName], 
  [FirstName] + ' ' + [LastName] as FullName, 
  --Created Full Name
  --,[NameStyle]
  --,[BirthDate]
  
  [MaritalStatus], 
  -- ,[Suffix]
  
  [Gender], 
  [EmailAddress], 
  [YearlyIncome],
  
  --,[TotalChildren]
  --,[NumberChildrenAtHome]
  --,[EnglishEducation]
  --,[SpanishEducation]
  --,[FrenchEducation]
  --,[EnglishOccupation]
  --,[SpanishOccupation]
  --,[FrenchOccupation]
  -- ,[HouseOwnerFlag]
  -- ,[NumberCarsOwned]
  
  [AddressLine1], 
  --,[AddressLine2]
  -- ,[Phone]
  
  [DateFirstPurchase] 
  --,[CommuteDistance]

FROM 
  [AdventureWorksDW2019].[dbo].[DimCustomer]

 

Product:

 

  / Script for SelectTopNRows command from SSMS /
SELECT [ProductKey]

--,[ProductAlternateKey]
 -- ,[ProductSubcategoryKey]
  --,[WeightUnitMeasureCode]
 -- ,[SizeUnitMeasureCode]

  ,[EnglishProductName] as ProductName

  --,[SpanishProductName]
  --,[FrenchProductName]
 --,[StandardCost]
  --,[FinishedGoodsFlag]

  ,[Color]

  --,[SafetyStockLevel]
  --,[ReorderPoint]
 -- ,[ListPrice]
 -- ,[Size]
  --,[SizeRange]
 -- ,[Weight]
 -- ,[DaysToManufacture]
 -- ,[ProductLine]
 -- ,[DealerPrice]
  --,[Class]
 -- ,[Style]

  ,[ModelName]

 -- ,[LargePhoto]
 -- ,[EnglishDescription]
 -- ,[FrenchDescription]
 -- ,[ChineseDescription]
--  ,[ArabicDescription]
 -- ,[HebrewDescription]
 -- ,[ThaiDescription]
 -- ,[GermanDescription]
 -- ,[JapaneseDescription]
--  ,[TurkishDescription]
  --,[StartDate]
 -- ,[EndDate]

  ,[Status]

FROM [AdventureWorksDW2019].[dbo].[DimProduct]

 

Data Model

Here is a screenshot of the data model that we get after the ETL (Extract Transform Load) process into the MS Power BI.

 

 

Data After ETL:

Here is how Data looks like after the ETL process is completed.

MS Power BI Project: Sales Analysis (Final Report)

The final report consists of three pages: 1. Sales Overview 2: Category Details 3: Customer Details

1: Sales Overview

2: Category Overview:

3: Customer Details:

 

Thanks to Digital Deepak to encourage me to create my blog.

One thought on “MS Power BI Project: Sales Analysis

Leave a Reply

Your email address will not be published. Required fields are marked *