Salespeople can be assigned to multiple sales regions, and each region has multiple salespeople. You want to relate Salesperson and Region while keeping the model robust and following Microsoft's recommended pattern. Which approach should you use?
- ACreate a bridging table that holds salesperson and region keys and relate each dimension to the bridge. Correct
- BConfigure a direct many-to-many relationship between Salesperson and Region with single cross-filter direction.
- CDenormalise Region attributes into the Salesperson table so the model only needs one dimension table.
- DPromote Salesperson to a fact table and load Region as a dimension with one-to-many cardinality.
Why A is correct: Correct. The star-schema guidance describes a factless fact table acting as a bridging table as the best practice when relating two dimensions.
Why B is wrong: Direct many-to-many is supported but the guidance recommends a bridging (factless fact) table when relating two dimensions, because it preserves clearer filter propagation and is documented as the best practice.
Why C is wrong: Denormalisation collapses the relationship and breaks the ability to filter independently by region; it does not represent the many-to-many association.
Why D is wrong: Salesperson is a dimension entity, not a measurable event; making it a fact table misclassifies it and does not solve the many-to-many problem.