Data Modeling
Use a coverage factless fact table when the business question depends on what should have happened, not only what did happen. Model expected participation in one fact table, model actual events in another, then reconcile them at a shared grain. This prevents a common reporting error: treating missing event rows as if they were the same thing as zero expected activity.
The coverage problem: events alone cannot show what was missed
Event tables are good at answering questions like what happened, when it happened, and who was involved. They are not enough when the real question is whether something happened that was expected to happen.
For example, an attendance event table can show that a student attended a session. It cannot, by itself, show which students were supposed to attend but did not. A login table can show who logged in. It cannot, by itself, show which assigned users failed to log in. A delivery table can show completed deliveries. It cannot, by itself, show which planned stops were skipped unless the planned stops are modeled separately.
That separate population is the coverage set. In dimensional modeling, a common way to represent it is a factless fact table for coverage: one row for each expected relationship or expected opportunity, with foreign keys to the relevant dimensions and usually no additive business measure other than row count.
Use two fact tables: one for expected coverage, one for actual events
The clean pattern is to keep expected participation and actual occurrence as separate facts.
- Coverage fact: one row for each expected participant, assignment, eligibility, scheduled slot, or required action.
- Event fact: one row for each actual occurrence, such as an attendance record, completed task, purchase, scan, or submission.
This separation matters because the two tables often have different grains. The coverage table may contain one row per student per class session where attendance was expected. The event table may contain one row per attendance scan, and a student might scan twice, scan late, or be corrected manually. If those tables are joined directly at the row level, duplicate rows and inflated counts are likely.
The practical rule is simple: declare the grain of each fact table separately, then compare them only after reducing both to a shared reconciliation grain.
If missing rows have business meaning, model the expected rows explicitly. Do not expect an event table to explain events that never happened.
A hypothetical schema for expected attendance
Consider a training program that wants to report attendance coverage. The business question is not only how many attendance events did we record? It is of the people expected to attend, how many actually attended?
A dimensional model could use shared dimensions such as date, participant, course, session, location, and instructor. The expected population goes into fact_expected_attendance. The actual events go into fact_attendance_event.
The important design choice is that fact_expected_attendance contains expected attendance rows even when no event occurred. That gives the model a place to count absences, missed requirements, or nonparticipation. Without this table, missed attendance has to be inferred indirectly, often with fragile joins or spreadsheet logic.
| Table | Grain | Typical keys | Role |
|---|---|---|---|
| fact_expected_attendance | One row per participant per scheduled course session expected | date_key, participant_key, course_key, session_key, location_key | Defines the denominator for coverage and attendance rate |
| fact_attendance_event | One row per captured attendance event | event_date_key, participant_key, course_key, session_key, event_type_key | Records actual attendance signals that may need deduplication |
| dim_participant | One row per participant version or participant, depending on history design | participant_key | Describes the person expected to attend or recorded in an event |
| dim_session | One row per scheduled session | session_key, course_key, scheduled_date_key | Aligns expectation and occurrence to the same business session |
| dim_date | One row per calendar date | date_key | Supports reporting by scheduled date or event date |
Declare the grain before writing measures
Coverage tables become unreliable when the grain is vague. Before building the table, write the grain as a sentence that a non-modeler can inspect.
For this example:
- fact_expected_attendance grain: one row per participant per scheduled course session where the participant was expected to attend.
- fact_attendance_event grain: one row per captured attendance event for a participant and course session.
- reconciliation grain: one row per participant per scheduled course session.
Notice that the event fact may have multiple rows at the reconciliation grain. A badge scan, mobile check-in, and manual correction could all refer to the same participant-session pair. That does not mean the participant attended three expected sessions. It means the event fact needs to be reduced to an attended indicator at the reconciliation grain before it is compared with coverage.
Reconciled measures that separate expected from actual
Once the two fact tables are modeled separately, the measures become clearer.
- Expected attendance count: count rows in fact_expected_attendance.
- Attended count: count coverage rows that have at least one qualifying attendance event at the reconciliation grain.
- Missed attendance count: expected attendance count minus attended count.
- Attendance rate: attended count divided by expected attendance count.
The phrase qualifying attendance event should be defined by the business. For example, a late arrival might count as attended for one dashboard and not count for a compliance report. The dimensional design gives you a stable place to apply that rule, but it does not remove the need to define the rule.
The safest reconciliation approach is multipass: aggregate or deduplicate each fact table to the shared grain first, then combine the results. Do not join detailed fact_expected_attendance rows directly to detailed fact_attendance_event rows when the event table can contain multiple records per expected row.
Avoid detailed fact-to-fact joins for coverage reporting. First reduce each fact to the shared reconciliation grain, then compare the results.
| Metric | Uses coverage fact? | Uses event fact? | Calculation idea | What it answers |
|---|---|---|---|---|
| Expected attendance count | Yes | No | Count rows in fact_expected_attendance | How many participant-session obligations existed? |
| Raw attendance events | No | Yes | Count rows in fact_attendance_event | How many event records were captured? |
| Attended count | Yes | Yes | Count expected rows with at least one qualifying event at participant-session grain | How many expected participant-sessions were fulfilled? |
| Missed attendance count | Yes | Yes | Expected attendance count minus attended count | How many expected participant-sessions were not fulfilled? |
| Attendance rate | Yes | Yes | Attended count divided by expected attendance count | What share of expected participation occurred? |
Coverage answers a different question than activity
A common modeling mistake is to use an activity table to answer a coverage question. Activity counts can rise while coverage gets worse. For example, a small group of highly active users may create many login events while a larger assigned population never logs in.
Coverage analysis starts from the denominator. Who was eligible? Who was assigned? Which sessions were scheduled? Which stores were supposed to submit a report? Which accounts were in the campaign audience? The actual events are then compared against that expected population.
If the denominator is not explicitly modeled, the dashboard will usually drift toward activity reporting. That may be useful, but it will not answer participation, compliance, adoption, or missed-opportunity questions reliably.
When a coverage factless fact table is the right model
This pattern is useful when absence matters. If the business cares only about events that occurred, a normal transaction fact may be enough. If the business cares about expected events that did not occur, model the expected set.
- Use it for attendance, required training, account assignments, campaign eligibility, sales territory coverage, scheduled visits, planned deliveries, subscription entitlements, access rights, and required approvals.
- Use it when the denominator is known before or independent of the event.
- Use it when reporting must distinguish not expected from expected but missing.
- Use it when event data is sparse and missing rows have business meaning.
Do not force this pattern where the expected population is not defined. If there is no reliable business rule for who should be included, the coverage table will create a false sense of precision.
Common failure modes
Factless fact tables for coverage are simple in shape, but they can fail in subtle ways.
- Undefined denominator: the team cannot explain why a row is expected, so the coverage count is disputed.
- Changing expectations overwritten: a participant was expected last week but removed today, and the model loses the historical expectation.
- Direct fact-to-fact joins: detailed coverage rows are joined to detailed event rows, duplicating counts when multiple events exist.
- Wrong reconciliation grain: expected rows are per session, but actual rows are reconciled per day or per course, hiding misses.
- Event quality treated as coverage quality: a missing event could mean absence, late ingestion, source system failure, or an unqualified event.
- No rule for duplicates: multiple event records are counted as multiple attendances instead of one attended expected row.
Most of these problems are not solved by a BI tool setting. They are modeling and definition problems. The durable fix is to make the expected population explicit and reconcile at the declared grain.
A good coverage model can answer three questions separately: who was expected, who actually participated, and which expected rows have no qualifying event.
| Symptom | Likely modeling issue | Better question |
|---|---|---|
| Attendance rate exceeds 100 percent | Multiple event rows are being counted against one expected row | Have events been deduplicated to the reconciliation grain? |
| Absences cannot be reported | Only actual events are modeled | Where is the expected population stored? |
| Counts change when assignments are updated | Historical expectations are overwritten | Should the coverage fact preserve expectation as of the scheduled time? |
| Events appear for people who were not expected | Actual events and coverage population do not fully overlap | Should these be excluded, flagged, or reported as unplanned participation? |
| Different dashboards show different denominators | Each report rebuilds expectation logic separately | Can the expected coverage fact become the shared denominator? |
Implementation checklist for practitioners
Before building the table, use this checklist to reduce ambiguity.
- Name the coverage question. For example: of assigned participants, who attended the required session?
- Declare the expected grain. Write one sentence that states exactly what one coverage row means.
- Identify the source of expectation. This might be enrollment, assignment, schedule, contract, eligibility, or campaign membership.
- Decide whether expectations are historical. If assignments change, define whether reports should reflect expectation at the time or current assignment.
- Declare the event grain. Identify whether actual events can repeat at the reconciliation grain.
- Define qualifying events. Decide which statuses, timestamps, corrections, or event types count as actual participation.
- Aggregate before comparison. Reduce both facts to the reconciliation grain before calculating rates or misses.
- Test edge cases. Include expected-without-event, event-without-expected, duplicate-event, changed-assignment, and late-arriving-event cases.
Operator rules for durable coverage models
A coverage model stays trustworthy when it follows a few operating rules.
- Keep expectation and occurrence separate. Do not hide the expected population inside event logic.
- Count expected rows from the coverage fact. The event table should not define the denominator.
- Make nonparticipation observable. A missed event should be visible as an expected row without a qualifying event.
- Reconcile at one declared grain. If a metric uses participant-session as its grain, do not mix it with participant-day logic without a documented reason.
- Preserve business meaning over convenience. A simpler join that changes the question is not a simpler model; it is a different answer.
These rules are especially important when semantic layers or BI models hide joins from dashboard authors. The model should make the correct path easier than the incorrect one.
Key takeaways
- A factless fact table for coverage models the expected population, not the event itself.
- Keep expected participation and actual events in separate fact tables when they have different grains or business meanings.
- Coverage measures should start from the expected denominator, then reconcile qualifying events at a shared grain.
- Do not join detailed fact tables directly when event rows can repeat; aggregate or deduplicate first.
- The model is only as trustworthy as the declared grain, expectation rule, and qualifying-event rule.
Next step
Pick one metric that currently reports participation, compliance, attendance, or adoption. Write the expected grain, event grain, and reconciliation grain as three separate sentences. If the denominator is currently coming from the event table, sketch a coverage fact table that would make missed participation visible.
- Read Additive, Semi-Additive, and Nonadditive Measures: A practical way to state which dimensions permit summation before a metric reaches a dashboard.
- Read Accumulating Snapshot Fact Tables: Track Milestones in a Defined Lifecycle: A practical guide to modeling processes such as orders, claims, tickets, and applications as one row that updates as milestones are completed.
- Read Data Modeling Before Dashboards: Build Metrics People Can Trust: Use business definitions, entities, events, and trusted marts before investing in dashboard polish.