BI course 02 — Preparing reliable measures and a report specification

A chart can look correct while counting the wrong thing. This chapter teaches how to define grain, select evidence and produce a small report specification that another analyst can reproduce. The examples are classroom calculations, not executed BI transformations.

In this chapter

• Choose one row per meaningful unit
• State time boundaries and population explicitly
• Separate operational and measurement problems
• Design a report around the decision
• Check your specification

Choose one row per meaningful unit

For the practice pack, the unit is one item. A review-event log may contain several events for the same item, so counting events would inflate the population. Keep the source log unchanged and create an analytical register with one row per eligible item.

Use the following complete fictional input. All events are in the same stated practice timezone and are available by the 10:00 cutoff.

Item	Due date	Latest review by 10:00	Evidence	Ready under the rule?
A	Friday	Accepted at 09:00	Review note A1	Yes
B	Friday	Changes needed at 09:30	Review note B2	No
C	Monday	No review	None	No

B also had Accepted at 08:00, but the later Changes needed event supersedes that classification for this snapshot. Do not select the earlier favorable event. An exact duplicate of A's 09:00 event is still one observation, not another item or another acceptance.

The ready rule is: the latest qualifying review by the cutoff is Accepted and has the required evidence. Missing review is not accepted. An Accepted label without the required evidence would also remain unverified under this rule.

State time boundaries and population explicitly

The baseline population is A, B and C. Its ready count is 1, eligible count 3 and rate 1/3, approximately 33.3 percent. If the question is Friday readiness, the population becomes A and B: 1/2 or 50 percent. The underlying work did not improve merely because the denominator changed.

Due date, creation date, event time and ingestion time answer different questions. A due-date filter selects a deadline cohort. A review-event cutoff determines what was known at a checkpoint. A late-arriving event can change a reconstructed historical answer even when its event time is old.

Write the timezone and whether boundaries are inclusive before comparing snapshots. A generic Last 30 days label does not establish which field, timezone or endpoint convention a populated BI report uses.

Separate operational and measurement problems

B needs correction: that is an operational gap. A missing evidence reference on an Accepted review would be an evidence-quality gap. Duplicate rows would be a transformation problem. A report that fails to load is an availability problem. The remedies differ.

Do not edit or delete a source record merely to make a report agree. Preserve the observed result and compare included identities, latest-event selection and evidence rules. Ask the reporting owner to correct the analytical transformation when that is where the mismatch lies.

A useful reconciliation records included IDs, excluded IDs with reasons, the current classification and the exact source behind each classification. Equal totals are not enough: a report showing A and C as the two Friday items would still be wrong even if it returned 50 percent.

Design a report around the decision

For the baseline, use a headline 1 of 3 ready, a small table listing A/B/C and the reason each is or is not ready, and a next-action summary: correct B and obtain C's review when needed. The table is more informative than a large green percentage alone.

If a chart is useful, use a zero baseline for counts, clear units, direct labels and text that does not depend on color. State the cutoff and population near the result. A 100-percent bar for one item should not look like more completed work than a 50-percent bar for ten without showing denominators.

A future native build specification should name the source dataset, field mappings, one-row-per-item transformation, latest-event rule, evidence requirement, filters, output labels and reconciliation tests. Do not invent Workroomly SQL syntax, Model behavior, widget types or refresh guarantees from button names. The current unavailable dataset prevents those implementation claims, not the design work itself.

Check your specification

A proposed report counts A's duplicate review twice, uses B's old Accepted event and omits C. It shows three ready rows. The correct response identifies all three errors: duplicate grain, wrong latest event and missing population member. Restore the agreed register analytically; do not change the source history. The baseline remains 1/3 until new valid work or an explicitly revised scope changes it.