Begin with the reader’s decision

A dashboard for a sales manager and a dashboard for finance may use the same source but answer different questions. Write the intended decision before choosing charts: identify regions needing follow-up, reconcile booked sales, or monitor overdue invoices. Define refresh expectations and which date controls each measure.

Separate facts from descriptive context

Facts record measurable activity at a declared grain, such as one row per order line. Dimensions describe customers, products or dates. In a simple star model, a product dimension has a unique product key and relates to many sales rows. This makes the meaning of filters easier to reason about. Microsoft’s guidance explains these roles and the importance of consistent fact-table grain.

A small example with a large consequence

Order 10 contains two products: $60 and $40. Order 11 contains one product worth $50. Sales total $150, there are three order lines, and there are two orders. A chart that labels row count as “orders” is wrong even though the revenue is correct. The average order value is $150 / 2 = $75, not $150 / 3 = $50.

Document the metric as “sales amount divided by distinct orders in the current reporting population.” Then decide how refunds, cancelled lines and date filters change that population. The formula must follow the business definition rather than a convenient default aggregation.

A portfolio deliverable worth reviewing

1. A short requirements note with audience, decision and measure definitions.
2. A model diagram showing fact grain, keys and relationships.
3. A report with a focused overview and a route to detailed records.
4. A validation sheet reconciling totals and several filtered slices.
5. A handover note covering refresh failures and known limitations.

Questions to practise

Why does your order count change after adding a product filter? How do you distinguish order date from shipment date? What happens when a customer key is missing? Which measures are additive, and which must be recalculated instead of summed? Use a tiny dataset to show each answer rather than relying on screenshots alone.

Suggested learning route

Work through the star-schema lesson below, then build a sales report from a small public or synthetic dataset. Validate the unfiltered result, a single customer, a single date and a missing-key case. Save these checks alongside the project so a reviewer can reproduce the conclusions.

Documentation and further reading

• Microsoft star-schema guidance