Data Analyst Project - Sales Management

---

Business Requirement

Data Cleansing and Transformation(SQL)

To create necessary data model for doing analysis and fulfilling the business needs.The following tables were extracted using SQL.

One data source(Sales Budget) were provided in excel format and were connected in the data model in a later step of the process.

Dim_Calendar

SELECT 
  [DateKey], 
  [FullDateAlternateKey] AS Date 
  --,[DayNumberOfWeek] 
  ,[EnglishDayNameOfWeek] AS Day
  --,[SpanishDayNameOfWeek]
  --,[FrenchDayNameOfWeek]
  --,[DayNumberOfMonth]
  --,[DayNumberOfYear]
  ,[WeekNumberOfYear] AS WeekNr, 
  [EnglishMonthName] AS Month
  ,LEFT(EnglishMonthName,3) AS MonthShort
  --,[SpanishMonthName]
  --,[FrenchMonthName]
  ,[MonthNumberOfYear] AS MonthNo
  ,[CalendarQuarter] AS Quarter
  ,[CalendarYear] AS Year
  --,[CalendarSemester]
  --,[FiscalQuarter]
  --,[FiscalYear]
  --,[FiscalSemester]
FROM 
  [dbo].[DimDate] 
WHERE 
  CalendarYear >= 2020

Dim_Customer

SELECT [CustomerKey]
      --,[GeographyKey]
      --,[CustomerAlternateKey]
      --,[Title]
      ,[FirstName]
      --,[MiddleName]
      ,[LastName]
	  ,CONCAT([FirstName],' ',[LastName]) AS FullName
      --,[NameStyle]
      --,[BirthDate]
      --,[MaritalStatus]
      --,[Suffix]
      ,CASE WHEN [Gender] = 'M' THEN 'Male' ELSE 'Female' END AS Gender
      --,[EmailAddress]
      --,[YearlyIncome]
      --,[TotalChildren]
      --,[NumberChildrenAtHome]
      --,[EnglishEducation]
      --,[SpanishEducation]
      --,[FrenchEducation]
      --,[EnglishOccupation]
      --,[SpanishOccupation]
      --,[FrenchOccupation]
      --,[HouseOwnerFlag]
      --,[NumberCarsOwned]
      --,[AddressLine1]
      --,[AddressLine2]
      --,[Phone]
      ,[DateFirstPurchase]
      --,[CommuteDistance]
	  ,g.City AS [Customer City]  -- Joined Geography and Customer table to get the city of customers
	  ,g.EnglishCountryRegionName AS Country
  FROM [AdventureWorksDW2019].[dbo].[DimCustomer] AS c
  LEFT JOIN dbo.DimGeography AS g
  ON c.GeographyKey = g.GeographyKey
  ORDER BY CustomerKey ASC

Dim_Product

SELECT p.[ProductKey]
      ,p.[ProductAlternateKey] AS ProductItemCode
      --,[ProductSubcategoryKey]
      --,[WeightUnitMeasureCode]
      --,[SizeUnitMeasureCode]
      ,[EnglishProductName] AS ProductName
	  ,ps.EnglishProductSubcategoryName AS SubCategory
	  ,pc.EnglishProductCategoryName AS ProductCategory
      --,[SpanishProductName]
      --,[FrenchProductName]
      --,[StandardCost]
      --,[FinishedGoodsFlag]
      ,[Color] AS ProductColor
      --,[SafetyStockLevel]
      --,[ReorderPoint]
      --,[ListPrice]
      ,[Size] AS ProductSize
      --,[SizeRange]
      --,[Weight]
      --,[DaysToManufacture]
      ,[ProductLine]
      --,[DealerPrice]
      --,[Class]
      --,[Style]
      ,[ModelName] AS ProductModelName
      --,[LargePhoto]
      ,[EnglishDescription] AS ProductDescription
      --,[FrenchDescription]
      --,[ChineseDescription]
      --,[ArabicDescription]
      --,[HebrewDescription]
      --,[ThaiDescription]
      --,[GermanDescription]
      --,[JapaneseDescription]
      --,[TurkishDescription]
      --,[StartDate]
      --,[EndDate]
      ,ISNULL([Status],'Outdated') AS ProductStatus
  FROM [AdventureWorksDW2019].[dbo].[DimProduct] AS p
  LEFT JOIN dbo.DimProductSubcategory AS ps
  ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
  LEFT JOIN dbo.DimProductCategory AS pc
  ON ps.ProductCategoryKey = pc.ProductCategoryKey
  ORDER BY p.ProductKey ASC

Fact_Sales

SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      --,[PromotionKey]
      --,[CurrencyKey]
      --,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      --,[SalesOrderLineNumber]
      --,[RevisionNumber]
      --,[OrderQuantity]
      --,[UnitPrice]
      --,[ExtendedAmount]
      --,[UnitPriceDiscountPct]
      --,[DiscountAmount]
      --,[ProductStandardCost]
      --,[TotalProductCost]
      ,[SalesAmount]
      --,[TaxAmt]
      --,[Freight]
      --,[CarrierTrackingNumber]
      --,[CustomerPONumber]
      --,[OrderDate]
      --,[DueDate]
      --,[ShipDate]
  FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]
  WHERE
	LEFT(OrderDateKey,4) > = YEAR(GETDATE())-2    -- To get only past 2 years data from current year
  ORDER BY
  OrderDateKey ASC

Data Model

We have now the required data to load into the Power BI desktop.After the data is loaded in to the power BI desktop, relationships between the tables were established.

The below data model shows the relationship between the Dimension and Fact tables.

Sales Management Dashboard

The below dashboard shows all the details requested in the business requirement

• Sales vs Budget KPI visual.

• Sales by Top 10 customers

• Sales by Top 10 products

• Sales and budget by month

• Sales by product category

• Sales by customer city