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 |