To meet the demand for an executive sales report, i have crafted a story that defined project goals and ensured adherence to acceptance criteria. These story guide the analysis, aligning outcomes with the initial business request.
Data Celaning & Transformation (SQL)
To create the necessary data for doing analysis and fulfilling the business needs defined in the story, the following tables were extracted using SQL.
DIM_Calendar:
SELECT [orderid],
[customerid],
[employeeid],
[shipperid],
CONVERT (DATE, [orderdate]) AS Date,
CASE
WHEN Datepart(quarter, [orderdate]) = 1 THEN ‘Q1’
WHEN Datepart(quarter, [orderdate]) = 2 THEN ‘Q2’
WHEN Datepart(quarter, [orderdate]) = 3 THEN ‘Q3’
WHEN Datepart(quarter, [orderdate]) = 4 THEN ‘Q4’
END AS QuarterName,
Datename(weekday, [orderdate]) AS [Day Name],
Datepart(week, [orderdate]) AS Weeknr,
LEFT(Datename(month, [orderdate]), 3) AS MonthShort,
Month([orderdate]) AS Monthno,
Datepart(weekday, [orderdate]) AS [Week Day],
Datepart(year, [orderdate]) AS Year
FROM [Northwind].[dbo].[orders]
DIM_Customers:
SELECT [CustomerID],
[customername],
[city],
[country]
FROM [Northwind].[dbo].[customers]
DIM_Orders:
SELECT orders.[orderid],
CONVERT(DATE, [orderdate]) AS Date,
orderdetails.quantity AS Quantity,
–,Orders.[CustomerID]
–,[EmployeeID]
–,[ShipperID]
–,OrderDetails.ProductID
–,Products.CategoryID
categories.categoryname AS [Category Name],
products.productname AS [Product Name],
customers.city AS City
FROM [Northwind].[dbo].[orders] AS Orders
LEFT JOIN [Northwind].[dbo].[orderdetails] AS OrderDetails
ON orders.orderid = orderdetails.orderid
LEFT JOIN [Northwind].[dbo].[products] AS Products
ON orderdetails.productid = products.productid
LEFT JOIN [Northwind].[dbo].[categories] AS Categories
ON products.categoryid = categories.categoryid
LEFT JOIN [Northwind].[dbo].[customers] AS Customers
ON orders.customerid = customers.customerid
FACT_Orders:
SELECT [orderid],
[customerid],
[employeeid],
CONVERT(DATE, [orderdate]) AS Date,
[shipperid]
FROM [Northwind].[dbo].[orders]
Data Model
Here is a snapshot of the data model post-cleaning and preparation, showcasing the tables seamlessly imported into Power BI and with proper relations betweeen each table.
Orders Management Dashboard
The completed order management dashboard serves as a comprehensive overview, featuring one main page dedicated to dashboard functionalities. Additionally, two other pages have been crafted to amalgamate tables, offering essential details and visualizations that depict the flow of orders over time, by customer and product.
