top of page
  • Writer's pictureSiddhant Kumar

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.


  [FullDateAlternateKey] AS Date 
  ,[EnglishDayNameOfWeek] AS Day
  ,[WeekNumberOfYear] AS WeekNr, 
  [EnglishMonthName] AS Month
  ,LEFT(EnglishMonthName,3) AS MonthShort
  ,[MonthNumberOfYear] AS MonthNo
  ,[CalendarQuarter] AS Quarter
  ,[CalendarYear] AS Year
  CalendarYear >= 2020


SELECT [CustomerKey]
	  ,CONCAT([FirstName],' ',[LastName]) AS FullName
      ,CASE WHEN [Gender] = 'M' THEN 'Male' ELSE 'Female' END AS Gender
	  ,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


SELECT p.[ProductKey]
      ,p.[ProductAlternateKey] AS ProductItemCode
      ,[EnglishProductName] AS ProductName
	  ,ps.EnglishProductSubcategoryName AS SubCategory
	  ,pc.EnglishProductCategoryName AS ProductCategory
      ,[Color] AS ProductColor
      ,[Size] AS ProductSize
      ,[ModelName] AS ProductModelName
      ,[EnglishDescription] AS ProductDescription
      ,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


SELECT [ProductKey]
  FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]
	LEFT(OrderDateKey,4) > = YEAR(GETDATE())-2    -- To get only past 2 years data from current year
  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

22 views0 comments

Recent Posts

See All
bottom of page