Many-to-Many Part 3: Prevent Disappearing Data

By Evan Schmidt

This article describes how to prevent disappearing data when using many-to-many relationships in SSAS.

 

If a dimension is related to a fact via a many-to-many relationship then the bridge table must contain a reference to every fact record or data will disappear. (Note: the Grand Total will always show the full total.)

 

This can be dealt with by configuring the unknown member for the dimension or by explicitly creating an undefined or unknown member to show that no relationship exists.

 

This article describes explicitly creating a member called “No Expertise” in the Expertise dimension. Some will argue that it is poor design to add member which does not represent a real area of expertise into the expertise dimension. It is true that this can cause conflicts but in many cases is just makes good business sense. In this situation it’s would be nice to know how much has been sold by sales people with no area of expertise, and so I would argue that “No Expertise” is a tangible dimension member.

 

To make this happen simply add “(No Expertise)” to the dimension and bridge table. The parentheses are a quick way to influence sort order.

Sales Person	Expertise
Bob	Art History
Bob	Modern Art
Bob	Local Art
Nancy	Modern Art
Trent	(No Expertise)

 

 

The report will show the total revenue driven by people who have a given area of expertise:

Expertise	Distinct Qty	Distinct Rev
(No Expertise)	4	10,000
Art History	3	25,000
Local Art	3	25,000
Modern Art	4	75,000
Total	8	85,000

 

 

Many-to-Many Part 2: Multiple Groupings of a Dimension Member in SSAS

By Evan Schmidt

This article describes how to enable a dimension member to be classified multiple times or given multiple tags. This data structure can be built by linking many-to-many relationships together.

 

The scenario, described by the data below, show a dimension where one attribute (e.g. “Bob” a sales person) is described by multiple members of the “Expertise” dimension (e.g. his 3 areas of expertise).

 

For example a sales person might have multiple areas of expertise:

Sales Person	Expertise
Bob	Art History
Bob	Modern Art
Bob	Local Art
Nancy	Modern Art

 

Now “Expertise” becomes a new dimension and the table above is the “Sales Person Expertise Bridge” table.

The dimension usage will look like this:

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

 

The report will show the total revenue driven by people who have a given area of expertise:

Expertise	Distinct Qty	Distinct Rev
Art History	3	25,000
Local Art	3	25,000
Modern Art	4	75,000
Total	8	85,000

 

At run time the server looks at the dimension usage of the "Expertise" dimension in the "Transaction" fact table and finds the dimension usage is sourced from the "Transactions Sales Person Bridge". So then server then looks at the dimension usage for the "Expertise" dimension in the "Transactions Sales Person Bridge" and finds the dimension usage is sourced from the "Sales Person Expertise Bridge". Then the serer finds the Regular Usage between "Expertise" and the "Sales Person Expertise Bridge" and so it can generate the data by joining the fact tables based on the common dimensions.

Notice how Trent’s sale is missing. This is because Trent dos not have an area of expertise.

It might be perfectly acceptable for the report to appear this way. If this missing transaction is not acceptable there are several ways to deal with it. The next article will discuss my preferred method.

 

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.