In this lesson, you will learn how to use weighting factors within bridge tables to ensure accurate fact measurement allocation when a transaction relates to multiple dimension members.
The Challenge of Double Counting
When you resolve a many-to-many relationship using a bridge table, you often face a dilemma: if a single transaction is associated with two salespeople, simply joining the bridge table to the fact table will duplicate the transaction amount. If a 100 for each, totaling $200—effectively inflating your revenue.
Introducing Weighting Factors
To solve this, we add a weighting_factor column to our bridge table. This column represents the percentage of the fact measurement that should be attributed to each dimension member. The sum of all weighting_factor values for a specific transaction key must equal 1.0 (or 100%).
Consider a sales_bridge table:
| fact_sales_key | salesperson_key | weighting_factor |
|---|---|---|
| 101 | 50 | 0.5 |
| 101 | 82 | 0.5 |
| 102 | 50 | 1.0 |
In this scenario, transaction 101 was a joint effort between two salespeople, so the $100 revenue is split 50/50. Transaction 102 was handled entirely by salesperson 50.
Applying the Weight in Analytics
When you write your query to calculate total sales by salesperson, you must multiply the fact measurement by the bridge table's weighting factor:
SELECT
d.salesperson_name,
SUM(f.sales_amount * b.weighting_factor) as allocated_sales
FROM fact_sales f
JOIN sales_bridge b ON f.fact_sales_key = b.fact_sales_key
JOIN dim_salesperson d ON b.salesperson_key = d.salesperson_key
GROUP BY d.salesperson_name;
By multiplying f.sales_amount by b.weighting_factor, the 50 for each salesperson, maintaining the integrity of your total company revenue while providing granular visibility into individual performance.
Key Takeaway: Always multiply your fact measurements by the bridge table's weighting factor to prevent measurement inflation and ensure accurate distribution across related dimension members.