Capstone project for Dashboards and Business Intelligence
Rebuild the weekly trading dashboard
Mangrove's leadership team looks at three different sales numbers every Monday: finance's net sales, store operations' till sales and the app team's order value. Build one reporting model, one set of metric definitions and one dashboard that ends the argument. Generate a small sample dataset (at least 20 stores, 200 articles and 8 weeks of sales lines, daily stock snapshots and app orders with their status times), load it into any SQL database or warehouse (PostgreSQL, DuckDB or a free cloud tier all work), and build staging, intermediate and mart models with tests in the style of dbt. Write a metric dictionary for net sales, till sales, app order value, out-of-stock loss and on-time delivery, with an owner for each, and show how the three Monday numbers reconcile. Design the dashboard for the Monday trading meeting in any BI tool (Metabase, Looker Studio, Power BI Desktop or similar), show when the data was last refreshed, and describe the checks and alerts that would catch a late or broken load.
What you hand in
- A repository with the SQL models in layers, their tests and a README with the star schema diagram and the grain of every fact table
- A metric dictionary with definitions, formulas, grain, filters, owners and a reconciliation of the three Monday sales numbers
- The dashboard (a link or screenshots) with a one-page guide for its users: the decisions it serves, how to read it and when the data is fresh
- A short operations note: the schedule, the freshness and volume checks, who gets alerted and what happens when a number is wrong
Check your work
Tick each item when your project meets it. Be strict: this is the rubric the reviewer uses.
- Every fact table has its grain stated in one sentence, and the schema uses conformed dimensions with keys that join cleanly (tests prove it)
- Models are layered (staging, intermediate, marts), each layer has a clear job, and tests cover keys, accepted values and relationships
- Each metric has one definition, a formula that respects additivity (ratios as ratios of sums, stock not summed across days) and a named owner
- The three Monday sales numbers are reconciled line by line, so anyone can see why they differ
- Every chart on the dashboard answers a named decision, with comparisons and targets, and the layout passes a five-second test with a real user
- Data freshness is visible on the dashboard, and checks with an owner catch late or incomplete loads before Monday's meeting
- The guide and operations note say who can see what, which content is certified and how old dashboards are retired
0 of 7 checked
Model answer
The model answer opens once your capstone passes the review, so you can compare your work with an outline of a strong submission.
Hand in your capstone
Your links are checked automatically, then the work is scored against the rubric above, item by item, with comments. A pass counts toward the course certificate and shows on your profile.
Loading…