Milestone Project 1: Enterprise E-Commerce Star Schema Transformation

Target Role: BI Engineer / Dimensional Data Modeler
Estimated Time: 180 min (3.0 Hours)
Deliverable Format: Power BI Project (.pbip Git repository with TMDL) or .pbix

1. Business Context & Problem Statement

You are hired as a Business Intelligence Analyst for "NexusDirect," a multi-channel e-commerce retailer. The IT department exports an un-normalized operational flat CSV (nexus_orders_raw.csv, 150,000 rows, 28 columns) combining order line items, customer details, product attributes, shipping methods, and warehouse locations.

The current dashboard built directly on this flat table takes 18 seconds to refresh, consumes excessive memory, and freezes whenever executives slice by date or product category.

2. Technical Deliverable Specifications

1. Power Query Normalization: Deconstruct the flat file into a clean Star Schema:
   • Fact_Sales: Transaction grain = 1 row per order line item (SalesAmount, Quantity, DiscountAmount, TaxAmount, UnitCost).
   • Dim_Customer: CustomerKey (Surrogate Key), CustomerName, Email, Segment, City, State, Country.
   • Dim_Product: ProductKey (Surrogate Key), SKU, ProductName, Category, Subcategory, Color, ListPrice.
   • Dim_Shipping: ShippingMethodKey, Carrier, ServiceLevel.
   • Dim_Date: Dedicated continuous calendar table covering all transaction dates.
2. Relationship Architecture:
   • Enforce 1-to-Many (1:*) cardinality pointing from Dimension to Fact.
   • Cross-filter direction set to Single. Zero bi-directional filtering permitted.
   • Role-Playing Dates: Connect Dim_Date[DateKey] to OrderDateKey (Active) and ShipDateKey (Inactive).
3. Inactive Relationship Verification: Verify your inactive ship date relationship using this standard verification measure:

   Orders Shipped Verification = 
   CALCULATE(
       COUNTROWS(Fact_Sales),
       USERELATIONSHIP(Fact_Sales[ShipDateKey], Dim_Date[DateKey])
   )
   

4. Architecture Documentation (DATA_MODEL_ARCHITECTURE.md):
   • Declare the atomic grain of Fact_Sales.
   • Explain your surrogate key generation logic in Power Query.
   • Detail the VertiPaq dictionary compression benefits of your schema over the flat table.

3. Grading Rubric (100 Points Total)

• Schema Topology (30 pts): Clean Star Schema (1 Fact, $\ge 4$ Dimensions, 1-to-Many single-directional).
• Role-Playing Date Architecture (25 pts): Correct active/inactive date relationships and surrogate keys.
• Power Query ETL & Typing (25 pts): Strict data typing, renamed steps, clean transformation pipeline.
• Architecture Documentation (20 pts): Clear analytical defense of grain, surrogate keys, and memory optimization.