Power BI Data Modeling Project: From 23 Messy Tables to a Clean Star Schema

📌 Overview

This project takes a real-world, unorganized dataset of 23 messy tables and rebuilds it into a clean, optimized star schema in Power BI. The goal was to move beyond basic reporting and apply proper dimensional modeling practices — including multiple fact table types, junk dimensions, row-level security, and time-based measures.

🎥 Project inspired by: Power BI Data Modeling Portfolio Project (Nightmare Data Model)

---

🗂️ Data Model

Fact Tables

Fact Table	Type	Description
fact_sales	Transaction fact	Captures individual sales events at the lowest grain
fact_inventory	Periodic snapshot fact	Tracks stock levels at fixed points in time
fact_campaign	Transaction fact	Logs marketing campaign activity
fact_factless_promotion_coverage	Factless fact	Records which promotions applied to which products/stores/dates (no measures — relationships only)
fact_accumulating_order_fulfillment	Accumulating snapshot fact	Tracks an order's journey across milestones (order → picked → shipped → delivered)
fact_sales_targets	Periodic snapshot fact	Stores planned/target values per period for actual-vs-target analysis

Dimension Tables

Dimension Table	Type	Description
dim_customer	Standard dimension	Customer attributes for filtering and segmentation
dim_product	Standard dimension	Product hierarchy and attributes
dim_geo	Standard dimension	Geographic hierarchy (region/country/city)
dim_campaign	Standard dimension	Marketing campaign attributes
dim_order_junk	Junk dimension	Groups low-cardinality order flags/indicators into one compact table

---

🔒 Security

• Implemented Row-Level Security (RLS) roles so users only see data relevant to their region (via dim_geo)
• Used DAX filter expressions to dynamically restrict data per logged-in user
• Validated roles using Power BI's "View As" feature before publishing

---

📊 Measures

total_sales = SUM(fact_sales[LineTotal])

total_orders = DISTINCTCOUNT(fact_sales[OrderID])

base_total_customers = COUNT(dim_customer[customer_id])

total_active_customers = DISTINCTCOUNT(fact_sales[customer_id])

avg_order_to_pay = AVERAGE(fact_accumulating_order_fulfillment[order_to_pay])

Measure	What It Shows
total_sales	Overall revenue performance
total_orders	Order volume trends
base_total_customers	Total size of the customer base
total_active_customers	Customers who actually placed an order
avg_order_to_pay	Average time between order placement and payment (cash flow indicator)

---

💡 Key Takeaways

• Multiple fact tables at different grains can coexist cleanly in one star schema
• Factless fact tables are useful for coverage/eligibility questions, not just numeric measures
• Junk dimensions keep low-cardinality flags organized without cluttering the model
• Accumulating snapshot facts simplify process-duration measures like order_to_pay
• Row-Level Security should be designed into the model from the start, not added as an afterthought

---

🛠️ Tools Used

• Power BI Desktop
• Power Query
• DAX
• Star Schema / Dimensional Modeling principles (Kimball methodology)

---

📎 Reference

• Project video: https://www.youtube.com/watch?v=0A2k62YEbfI