E-commerce star schema and DAX dashboard

#Brief

Olist connects small Brazilian sellers to large marketplaces. The dataset has about 100,000 orders from 2016 to 2018 across nine related CSV files: orders, items, payments, reviews, customers, sellers, products, geolocation and category translations. Leadership wants a report on sales, delivery performance and customer satisfaction.

#What to build

1. Declare two grains: order item (sales) and order (delivery and review).

2. Build dimensions: customer, seller, product (with English category names), date. Check each key is unique.

3. Build fct_order_item and fct_order. Compute delivery delay (delivered minus estimated date) on fct_order.

4. In Power BI: relationships, a marked date table, measures for revenue, orders, average order value, on-time delivery rate, average review score, revenue MTD and YTD, and previous-month comparisons.

5. Answer: which categories grow fastest, and does late delivery lower review scores?

#Deliverables

• models/ with your SQL or Power Query, and the .pbix

• model.png with grains written next to each fact table

• reconcile.sql or a check page: row counts and revenue match the source files

#How it is graded

The model is graded before the visuals: a wrong grain or duplicated keys caps the score. Then DAX correctness and the clarity of the answers.

#Stretch

Add row-level security by seller state and test it with "View as".