Many-to-Many Part 1: Distinct Sum in SSAS

By Evan Schmidt

This article describes how to use a many-to-many relationship to create a distinct, or de-duplicated, revenue calculation.

 

With this method revenue will never be counted twice in a total or sub-total. This may be required when a single transaction can be described by multiple descriptions of the same type. For example in the data below you will find the sale of a $10k Print was influenced by both Nancy and Bob.  It may not be correct to allocate the revenue 50% to Bob and 50% to Nancy so the solution is to give both full credit while never double counting in totals and sub-totals.

 

The transaction is the measure, or fact, record.  Here is the “Transaction” table which lists four sales:

Transaction ID	Product Type	Date	Qty	Rev
A	Original	6/5/11	1	15,000
B	Print	6/6/11	2	10,000
C	Texture	6/7/11	1	50,000
D	Print	6/7/11	4	10,000

 

Here is the “Transaction Sales Person Bridge” table which records the sales person who influenced each sale:

Transaction ID	Sales Person
A	Bob
B	Bob
B	Nancy
C	Nancy
D	Trent

 

The report user wants to see a report which shows the revenue driven by each Sales Person. Both Bob and Nancy are given full credit for the sale but the sale is not double counted in the grand total.

Sales Person	Distinct Qty	Distinct Rev
Bob	3	25,000
Nancy	3	60,000
Trent	4	10,000
Total	8	85,000

 

 

This can be implemented by using a many-to-many relationship in SSAS. The dimension usage will look like this:

Fact: Dimension:	Trans.	Trans. Sales Person Bridge
Transaction	Regular usage	Regular usage
Sales Person	M-M: Trans. Sales Person Bridge	Regular usage

 

 

Upon query execution the SSAS engine will respond based on the dimension usage. When the Transaction Fact is sliced by the Sales Person dimension the slice should be based on the many-to-many relationship with the Transaction Sales Person Bridge table. First the SSAS engine finds all Transaction IDs in the bridge table with the given member from the Sales person dimension. Then the SSAS engine finds all records in the transaction fact table with those Transaction IDs and returns the revenue.