Online Sales Reporting Dashboard

Background:

Adventure Works Cycles aims to enhance its online sales reporting system by transitioning from static reports to interactive visual dashboards, providing deeper analytical insights and supporting strategic decision-making in its global bicycle manufacturing business.

Problem Statement:

The current static reports limit the ability to provide detailed and dynamic insights, hindering effective analysis of sales data, including product performance, customer purchases, and sales trends over time.

Objective:

Transition from static reports to visual dashboards.
Focus on visualizing sales data, including product sales, customer purchases, and trends over time.
Implement filtering capabilities to allow sales data to be viewed by specific salespeople, products, and customers.
Compare sales data against budgeted figures for 2019 and analyze performance over the past two years.

Data Analysis Diploma Project - 2024

Deliverables

Business Request & User Stories

Business Demand Overview & User Stories (Filled Out).pdf

Sales Management Dashboard

The finished sales management dashboard with one page with works as a dashboard and overview, with two other pages focused on combining tables for necessary details and visualizations to show sales over time, per customers and per products.

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 hsa been connected to FACT_InternetSales and other necessary DIM tables.

SQL statements for cleansing and transforming data (SQL Server Management Studio)

DIM_Calendar:

SQL statements for cleansing and transforming data

DIM_Calendar:

-- Cleansed DIM_Date Table --

SELECT

[DateKey],

[FullDateAlternateKey] AS Date,

--[DayNumberOfWeek],

[EnglishDayNameOfWeek] AS Day,

--[SpanishDayNameOfWeek],

--[FrenchDayNameOfWeek],

--[DayNumberOfMonth],

--[DayNumberOfYear],

--[WeekNumberOfYear],

[EnglishMonthName] AS Month,

Left([EnglishMonthName], 3) AS MonthShort, -- Useful for front end date navigation and front end graphs.

--[SpanishMonthName],

--[FrenchMonthName],

[MonthNumberOfYear] AS MonthNo,

[CalendarQuarter] AS Quarter,

[CalendarYear] AS Year --[CalendarSemester],

--[FiscalQuarter],

--[FiscalYear],

--[FiscalSemester]

FROM

[AdventureWorksDW2019].[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],

-- Combined First and Last 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

[AdventureWorksDW2019].[dbo].[DimCustomer] as c

LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey

ORDER BY

CustomerKey ASC -- Ordered List by CustomerKey

DIM_Products:

-- 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],

-- Combined First and Last 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

[AdventureWorksDW2019].[dbo].[DimCustomer] as c

LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey

ORDER BY

CustomerKey ASC -- Ordered List by CustomerKey

FACT_InternetSales:

-- Cleansed FACT_InternetSales Table --

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 -- Ensures we always only bring two years of date from extraction.

ORDER BY

OrderDateKey ASC