Data warehousing / Metric-definition worksheet
Data warehousing for an SME: start with one business decision
Make the first useful report traceable to its source.
Download worksheetEditable CSV · Open in your spreadsheet app
Download article in MarkdownThe first data-warehouse project should answer one recurring business question with numbers people can reconcile. For a small or midsize business, that might be which orders are late, which products tie up stock, or how sales and refunds compare across channels. Start with the decision and define the data needed to support it.
Distinguish reporting from running the operation
A warehouse management system runs physical stock and fulfillment. A data warehouse brings information from business systems together for analysis. It usually receives copies or transformations of operational records; those source systems continue to own the transactions.
That distinction matters when people see a difference between a dashboard and a live order screen. The dashboard may have a refresh delay or a different definition. Document both before deciding that a number is wrong.
Define what one row represents
Choose the level of detail for each dataset: one order, one order line, one shipment, or one stock movement. Data teams call this the grain. Mixing levels without care can count the same money or quantity more than once.
In an illustrative example, an order has two item lines and three payment records. Joining both lists only by order identifier can create six combinations. Summing amounts from that combined result may multiply values. Ask the implementation team to show how the model preserves the meaning of each measure.
Microsoft's dimensional-modeling guidance describes fact tables for measurements and dimension tables for descriptive context. These are useful concepts for planning a reporting model; they do not require an SME to select a particular vendor.
Agree a metric contract
| Metric decision | Definition to record | Check before publishing |
|---|---|---|
| Business question | The decision the report supports | A named person can explain how they will use it |
| Record detail | Order, line, shipment, or movement | A sample reconciles at that level |
| Date | Order date, payment date, or dispatch date | Time zone and period boundaries are consistent |
| Amount | Gross, net, tax, refund, or contribution | Finance agrees the components and exclusions |
| Freshness | How recent the data must be | Last successful refresh is visible |
| Ownership | Source owner and reporting owner | Someone accepts unresolved differences |
| Access | Who can view details and export them | Restricted records are tested with different roles |
Build one useful reporting area first
Choose a manageable combination of sources and a clear review cadence. A first project could connect orders and shipments to show dispatch delays. It needs stable identifiers, status mapping, refresh logic, and reconciliation. It may not need customer segmentation, forecasting, and a complete finance model at the same time.
Keep a copy of the source values needed to investigate differences, subject to the agreed retention and access rules. Record transformation logic so someone can trace a report row back to the original record. Make failed or incomplete loads visible; an old dashboard that looks current is a poor operating signal.
Plan how updates and deletions are handled. An order imported yesterday may be canceled today. A pipeline that only appends new records can leave the reporting layer inconsistent unless it has a defined method for later changes.
Accept the numbers with their owners
Pick a closed period and reconcile counts and totals against the source systems using the agreed definitions. Investigate differences before dismissing them as timing. Some differences will be legitimate, but they should be explained and repeatable.
Test access with the actual roles that will use the report. A manager may need summary figures without seeing every customer record. Include exports and shared links in that review.
Measure freshness, unexplained differences, time spent preparing reports, and whether people use the output for the intended decision. Those measures provide a better first acceptance discussion than the number of dashboards built.
The migration guide covers broader data checks, while the modernization roadmap helps decide whether reporting is the right first investment.
Your next step
Discuss your modernization
Share the task, your current systems, and the decision you need to make. We’ll discuss a useful first scope.
Discuss your modernization