Capstone Project: End-to-End Analytics with Superstore Dataset

It is time to assemble everything you have learned into a unified Intermediate Capstone Project. In this project, you will download the raw superstore_sales_analytics.csv dataset, perform enterprise ETL transformations in Power Query, engineer a clean Star Schema data model, construct modular DAX calculations, and build an interactive multi-page dashboard.

1. Acquiring the Practice Dataset

The dataset is located in the platform's public repository:

• Download Path: /downloads/datasets/superstore_sales_analytics.csv
• Rows: 250 multi-region commercial orders.
• Fields: OrderID, OrderDate, ShipDate, ShipMode, CustomerID, CustomerName, Segment, City, Region, Category, SubCategory, Sales, Quantity, Discount, Profit.

2. Phase 1: Power Query ETL & Star Schema Decomposition

A single flat CSV table violates our Star Schema rules. In Power Query, we will normalize the flat table into 1 Fact Table and 3 Dimension Tables:

Power Query Transformation Steps:

1. 1
   Load superstore_sales_analytics.csv.
2. 2
   Duplicate the query 4 times. Rename queries to:

• Fact_SalesOrders
• Dim_Customer
• Dim_Product
• Dim_Geography

1. 1
   In Dim_Customer:

• Select columns: CustomerID, CustomerName, Segment.
• Remove other columns.
• Right-click CustomerID $\to$ Remove Duplicates (ensures a clean 1-side Primary Key!).

1. 1
   In Dim_Product:

• Select columns: SubCategory, Category.
• Remove other columns $\to$ Remove Duplicates.

1. 1
   In Dim_Geography:

• Select columns: City, Region.
• Remove other columns $\to$ Remove Duplicates.

1. 1
   In Fact_SalesOrders:

• Keep foreign keys and numeric metrics: OrderID, OrderDate, ShipDate, ShipMode, CustomerID, City, SubCategory, Sales, Quantity, Discount, Profit.
• Remove descriptive string columns (CustomerName, Segment, Category, Region).

1. 1
   Click Close & Apply.