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]