Data Modeling

When one amount belongs to several recipients, preserve totals by separating the original transaction fact from the allocation grain. Store the unallocated amount once at the transaction grain, store recipient weights in a separate allocation table, and expose reconciled allocated measures whose weights sum to 100 percent for each allocation set.

Why allocated measures break totals

An amount that is safe to sum in one table can become unsafe after it is joined to a table with multiple recipients. A sales order for 1,000 dollars is one row in an order fact. If two sales reps receive credit for that order, a direct join from the order to both reps creates two rows. Summing the original order amount after that join returns 2,000 dollars, even though the order total is still 1,000 dollars.

This is not only a dashboard problem. It is a grain problem. The original fact is at one grain, such as one order line, one invoice, one contract, or one shipment. The allocation is at a different grain, such as one order line per credited rep, one invoice per cost center, or one contract per campaign. Those two grains need to be represented separately.

The reliable pattern is to keep two truths distinct:

  • Original amount: the amount as recorded by the source business event, stored once at the declared transaction grain.
  • Allocated amount: the portion of the original amount assigned to a recipient according to an explicit allocation rule.

Both can be correct. They answer different questions. Problems start when the model lets users sum the original amount while slicing by allocation recipients.

Declare the transaction grain and the allocation grain

Before designing tables, write down the grains in plain language. This is the most important step because it tells you which measures are additive in which context.

For example:

  • Transaction fact grain: one row per order line.
  • Allocation fact grain: one row per order line per allocation recipient for a specific allocation type.

The transaction fact can safely store measures such as order quantity, gross revenue, discount amount, and net revenue. The allocation fact should store allocation weight and allocated versions of the measures that need to be analyzed by recipient.

The allocation recipient might be a sales rep, region, channel partner, marketing campaign, cost center, product manager, or any other dimension member that shares credit or responsibility. The pattern is the same: the model needs an explicit row for each share of the amount.

If the same transaction is allocated across more than one dimension, be precise about whether those allocation dimensions are independent or joint. Sales credit by rep and cost ownership by cost center may be two independent allocation processes. Sales credit by rep by region may require a joint allocation grain if users need to analyze both dimensions together without ambiguity.

Practical rule

If a dimension can have multiple members for one transaction, do not attach the original measure to that dimension and call it additive. Create an allocated measure at the recipient grain.

A dimensional schema for allocated measures

A practical design uses a base fact table plus an allocation fact table. The exact names are not important, but the responsibilities are.

Base transaction fact: stores the business event once at its natural grain. It has foreign keys to ordinary dimensions and stores the original additive measures.

Allocation fact: stores one row per transaction grain key, allocation type, and recipient. It contains the allocation numerator, allocation denominator, allocation percent, and allocated measures. It joins to the recipient dimensions needed for the allocation analysis.

Allocation type dimension: describes the allocation rule, such as sales credit split, support ownership split, campaign attribution split, or cost center split. This is useful when the same base amount can be allocated in different valid ways.

Recipient dimensions: describe the entities that receive the allocation, such as employee, team, region, cost center, or campaign. If the recipient can be one of several entity types, avoid hiding that complexity in a generic text column unless reporting requirements are very simple. A clear allocation type plus clear recipient keys is easier to test and explain.

A simple schema might look like this:

  • fact_order_line: order_line_key, order_key, date_key, customer_key, product_key, net_revenue_amount.
  • fact_order_line_allocation: order_line_key, allocation_type_key, sales_rep_key, allocation_weight, allocated_net_revenue_amount.
  • dim_allocation_type: allocation_type_key, allocation_type_name, allocation_method_name.
  • dim_sales_rep: sales_rep_key, sales_rep_name, team_name, territory_name.

This design protects the original order total while allowing rep, team, or territory reporting to use allocated revenue.

Worked example: split revenue across sales reps

Consider three order lines. Order line A has 1,000 dollars of net revenue and is split equally between two reps. Order line B has 600 dollars and belongs fully to one rep. Order line C has 400 dollars and is split 75 percent to one rep and 25 percent to another.

The base fact stores 2,000 dollars total: 1,000 plus 600 plus 400. The allocation fact stores five recipient rows, but the allocated revenue still sums to 2,000 dollars because each order line's allocation weights sum to 1.0.

The reconciled logic is simple:

allocated_net_revenue_amount = net_revenue_amount × allocation_weight

For order line A, each rep receives 500 dollars. For order line B, the only rep receives 600 dollars. For order line C, the first rep receives 300 dollars and the second receives 100 dollars. The allocation table has more rows than the transaction table, but the allocated measure remains reconciled to the original total.

The important reporting distinction is this: if a chart is grouped by sales rep, use allocated net revenue. If a chart is grouped only by order date, customer, or product and does not involve allocation recipients, use the original net revenue from the base fact.

Order line Original net revenue Recipient Allocation weight Allocated net revenue
A 1,000 Rep 1 0.50 500
A 1,000 Rep 2 0.50 500
B 600 Rep 1 1.00 600
C 400 Rep 2 0.75 300
C 400 Rep 3 0.25 100
Total 2,000 2,000

When allocation crosses multiple dimensions

The phrase multiple dimensions can mean two different things. The first is that one allocation recipient has attributes in several dimensions. For example, a sales rep rolls up to a team, territory, and manager. In that case, allocate to the rep and let normal dimension attributes handle team and territory reporting.

The second meaning is harder: the amount is shared across combinations of dimensions. For example, an invoice might be allocated across both cost center and product line, and users may need to report cost center by product line. If the allocation rule is defined for the combination, the allocation fact grain must include that combination.

Do not assume that two independent allocation splits can be multiplied together unless the business rule actually says so. If an invoice is split 50 percent across two cost centers and a product amount is split 60 percent and 40 percent across two product lines, multiplying those splits creates four rows. That may be correct for some cost accounting rules, but it may also invent precision that the business never approved.

A useful question is: Does the recipient combination exist in the business rule, or are we creating it for reporting convenience? If the combination is real, model it. If not, keep the allocation processes separate and document which measures can be combined safely.

Warning

Cross-joining separate allocation rules can create numbers that reconcile mathematically but do not represent a real business rule.

Query and BI semantics that prevent double counting

The model should make the correct measure easy to choose and the unsafe measure hard to misuse. The safest approach is to expose separate measures with names that describe their grain.

  • Net revenue: sum of the original amount from the base transaction fact.
  • Allocated net revenue: sum of the allocated amount from the allocation fact.
  • Allocation weight: used for validation and explanation, not usually as a business KPI by itself.

A BI semantic layer should not let a user group original net revenue by sales rep through the allocation relationship. If that relationship exists physically, the semantic model still needs measure definitions that route recipient analysis to allocated measures.

For example, a report by customer can use original net revenue. A report by sales rep must use allocated net revenue. A report by sales rep and customer can use allocated net revenue if the allocation fact includes both the order line key and the sales rep key, and customer is inherited from the order line through a safe relationship or pre-joined allocation mart.

Some teams create a flattened allocation mart for common reporting use cases. That can be practical, but it should not obscure the rule. The flattened table still needs one row per allocated recipient share and should store allocated measures rather than duplicated original measures.

Reporting question Safe measure Why
Total revenue by month Original net revenue Month belongs to the base transaction grain, so the original amount is additive.
Revenue credited to each sales rep Allocated net revenue The amount is being sliced by allocation recipient, so each recipient needs only their share.
Revenue by customer and sales rep Allocated net revenue Customer comes from the base transaction, but sales rep comes from the allocation grain.
Order count by sales rep Allocated order count or credited order count A plain order count may double count shared orders unless the counting rule is explicit.
Revenue by two independent allocation dimensions Depends on the documented rule Independent allocations should not be combined unless a joint allocation rule exists.

Validation tests for allocated measures

Allocated measures need reconciliation tests because small modeling mistakes are easy to miss in dashboards. The minimum test is that allocated totals match original totals for the population being allocated.

Useful tests include:

  • Weight completeness: for each transaction key and allocation type, allocation weights sum to 1.0, unless the business explicitly allows partial allocation.
  • No negative or impossible weights: weights are within the allowed range for the allocation rule.
  • Allocated amount reconciliation: the sum of allocated amount equals the original amount at the transaction key and allocation type.
  • Recipient key validity: every allocation row points to a valid recipient dimension member.
  • Allocation type clarity: each allocation row belongs to a documented allocation type, not an overloaded generic split.
  • Rounding control: rounding differences are handled at a defined grain, not left to vary by dashboard.

Rounding deserves special attention. If 100 dollars is split three ways, each recipient might receive 33.33 dollars, leaving a one-cent difference. The model should define whether the remainder goes to the largest share, the first recipient by rule, a separate rounding adjustment row, or remains in higher precision until final presentation.

Checkpoint

For each allocation type, the sum of allocated measures should tie back to the original measure at the agreed reconciliation grain, with any rounding adjustment explained.

Common failure modes

The most common failure is duplicating the original amount across allocation recipients and hoping the dashboard will remember to divide it later. That creates fragile reports because every analyst has to rediscover the allocation rule.

Other common failures include:

  • Missing allocation type: sales credit, revenue attribution, and cost ownership are stored in the same table without identifying which rule produced the row.
  • Implicit equal split: the model assumes equal allocation because there are multiple recipients, even though the business rule may use quota, ownership percentage, time, or manual credit.
  • Independent splits treated as joint splits: two unrelated allocation dimensions are cross-joined, creating artificial detail.
  • Only percentages stored: reports recalculate allocated amounts differently, producing rounding and filter inconsistencies.
  • No reconciliation path: users cannot prove that allocated totals tie back to the original fact total.
  • Changing assignments applied historically: current rep, team, or territory attributes overwrite the recipient context that was valid when the allocation was recorded.

These failures are not fixed by changing chart settings. They are fixed by modeling the grain and allocation rule explicitly.

Failure mode Symptom Repair
Duplicated original amount Recipient totals exceed the official total Create allocation rows with weights and allocated measures.
Missing allocation type Different teams use the same split for different questions Add an allocation type dimension and separate the rules.
No weight validation Some transactions are over- or under-allocated Test that weights sum to the expected total by transaction and allocation type.
Rounding handled in reports Different dashboards disagree by small amounts Define rounding at a controlled grain or store allocated amounts consistently.
Current hierarchy overwrites history Past credit moves when teams change Use effective-dated dimensions or preserve the historical recipient context.

A decision framework for practitioners

Use this sequence when you find an amount that belongs to several recipients:

  1. Name the base event. Decide the transaction grain where the amount exists once.
  2. Name the allocation question. Identify whether the split is for sales credit, cost ownership, marketing attribution, capacity, quota, or another purpose.
  3. Identify the recipient grain. Decide whether the recipient is a person, team, account, region, product, campaign, cost center, or a combination.
  4. Define the weight rule. Store or derive the numerator, denominator, and allocation weight according to a documented rule.
  5. Materialize allocated measures. Store or consistently define the allocated amounts that users should sum.
  6. Test reconciliation. Prove that allocated totals match original totals for each allocation type.
  7. Control semantic access. Make original measures available for original-grain analysis and allocated measures available for recipient analysis.

If you cannot answer one of these steps, do not hide the uncertainty in a many-to-many join. Leave the measure unallocated, label it clearly, and resolve the business rule before publishing recipient-level totals.

Operator rules for preserving totals

Several durable rules keep allocated measures understandable:

  • Do not change the base fact grain to satisfy allocation reporting. Keep the transaction fact stable and add allocation facts as needed.
  • Do not sum original amounts after joining to multiple recipients. Use allocated measures for recipient-level reporting.
  • Make weights explicit. A recipient row without a weight is an incomplete allocation row.
  • Separate allocation types. Different business questions can require different valid allocations of the same base amount.
  • Reconcile at the lowest practical grain. Testing only grand totals can hide transaction-level errors that cancel each other out.
  • Document partial allocation if it is allowed. A 70 percent total weight may be valid for known attribution coverage, but it should never look like a complete allocation.

The goal is not to force every business process into one universal allocation table. The goal is to prevent a shared amount from becoming an inflated amount.

Key takeaways

  • Keep the original amount once at the base transaction grain.
  • Create an allocation fact or bridge at one row per transaction recipient share.
  • Use explicit weights and allocated measures instead of summing duplicated original amounts.
  • Separate different allocation types because the same amount can have several valid business splits.
  • Test that allocated totals reconcile to original totals at the agreed grain.
  • Do not cross-join independent allocation rules unless the business has defined a joint allocation.

Next step

Take one shared measure in your model, such as revenue, cost, commission credit, or campaign attribution. Write its base grain, recipient grain, allocation type, weight rule, and reconciliation test. If any part is unclear, keep the recipient-level measure unpublished until the rule is explicit.

Recommended next reads