Business Demand Overview & User Stories

#	As a (role)	I want (request / demand)	So that I (user value)	Acceptance Criteria
1	Sales Manager	To get a dashboard overview of internet sales	Can follow better which customers and products sells the best	A Power BI dashboard which updates data once a day
2	Sales Representative	A detailed overview of Internet Sales per Customers	Can follow up my customers that buys the most and who we can sell ore to	A Power BI dashboard which allows me to filter data for each customer
3	Sales Representative	A detailed overview of Internet Sales per Products	Can follow up my Products that sells the most	A Power BI dashboard which allows me to filter data for each Product
4	Sales Manager	A dashboard overview of internet sales	Follow sales over time against budget	A Power Bi dashboard with graphs and KPIs comparing against budget.

Data Cleansing & Transformation (SQL)

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

One data source (sales budgets) were provided in Excel format and were connected in the data model in a later step of the process.

Below are the SQL statements for cleansing and transforming necessary data.

DIM_Calender:

--Cleansed DIM_DateTable---
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 
  [AdventureWorksDW2022].[dbo].[DimDate]
WHERE 
  CalendarYear >= 2019

---

DIM_Customers:

---Cleansed DIM_Customers Table---
SELECT 
  c.CustomerKey AS CustomerKey, 
  ---,[GeographyKey]
  ---,[CustomerAlternateKey]
  ---,[Title]
  c.firstname AS [First Name], 
  ---,[MiddleName]
  c.lastname AS [Last Name], 
  c.firstname + ' ' + lastname AS [Full Name], 
  ---,[NameStyle]
  ---,[BirthDate]
  ---,[MaritalStatus]
  ---,[Suffix]
  CASE c.gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender, 
  ---,[EmailAddress]
  ---,[YearlyIncome]
  ---,[TotalChildren]
  ---,[NumberChildrenAtHome]
  ---,[EnglishEducation]
  ---,[SpanishEducation]
  ---,[FrenchEducation]
  ---,[EnglishOccupation]
  ---,[SpanishOccupation]
  ---,[FrenchOccupation]
  ---,[HouseOwnerFlag]
  ---,[NumberCarsOwned]
  ---,[AddressLine1]
  ---,[AddressLine2]
  ---,[Phone] 
  c.datefirstpurchase AS [DateFirstPurchase], 
  ---,[CommuteDistance]
  g.city AS [Customer City] --- Joined in Customer City from Geography Table
FROM 
  [AdventureWorksDW2022].[dbo].[DimCustomer] AS c 
  LEFT JOIN dbo.dimgeography AS g ON g.[GeographyKey] = c.geographykey 
ORDER BY 
  Customerkey ASC --- Ordered List by Customerkey

---

DIM_Product:

---Cleansed DIM_Products Table---
SELECT 
  p.[ProductKey], 
  p.[ProductAlternateKey] AS ProductItemCode 
  ---,[ProductSubcategoryKey]
  ---,[WeightUnitMeasureCode]
  ---,[SizeUnitMeasureCode]
  , 
  p.[EnglishProductName] AS [Sub Category], 
  ps.EnglishProductSubcategoryName AS [Sub Category], ---- Joined in from Sub Category Table
  pc.EnglishProductcategoryName AS [Product Category], --- Joined in from Category Table
  ---,[SpanishProductName]
  ---,[FrenchProductName]
  ---,[StandardCost]
  ---,[FinishedGoodsFlag]
  p.[Color] AS [Product Color],
  ---,[SafetyStockLevel]
  ---,[ReorderPoint]
  ---,[ListPrice]
  p.[Size] As [Product Size], 
  ---,[SizeRange]
  ---,[Weight]
  ---,[DaysToManufacture]
  p.[ProductLine]AS [Product Line], 
  ---,[DealerPrice]
  ---,[Class]
  ---,[Style]
  p.[ModelName] AS [Product Model Name], 
  ---,[LargePhoto]
  p.[EnglishDescription] AS [Product Description], 
  ---,[FrenchDescription]
  ---,[ChineseDescription]
  ---,[ArabicDescription]
  ---,[HebrewDescription]
  ---,[ThaiDescription]
  ---,[GermanDescription]
  ---,[JapaneseDescription]
  ---,[TurkishDescription]
  ---,[StartDate]
  ---,[EndDate],
      p.Status AS Example,
  ISNULL (p.status, 'Outdated') AS [Product Status] 
FROM 
  [dbo].[DimProduct] AS p 
  LEFT JOIN dbo.DimProductSubcategory AS ps ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey 
  LEFT JOIN dbo.DimProductcategory AS pc on ps.ProductCategoryKey = pc.ProductcategoryKey 
order by 
  p.ProductKey asc

---

Fact_Budget:

---

Fact_Internet Sales:

---

Data Model:

Below is a screenshot of the data model after cleansed and prepared tables were read into Power BI.

This data model also shows how Fact_Budget has been connected to Fact_InternetSales and other necessary DIM tables.

---

Sales Management Dashboard:

The completed sales management dashboard features a comprehensive overview page, providing a high-level snapshot of key metrics. Additionally, it includes two detailed pages: one dedicated to combining essential tables and another for visualizing sales data over time, by customer, and by product. Simply click on the dashboard to explore these detailed insights.