SSAS - Many to many bridge table with queryable dimension attribute

2
votes

I'm working on a scenario where I have a bridge fact table joining two dimensions, but the bridge table also has data that I would like to be able to use as an attribute in my query. I believe essentially what needs to happen is for the bridge table to act as both a fact table (for the many-to-many join) and a dimension (to allow the attribute to be used in the query). The situation is set up like this:

Cube Setup

Using the fields in the many to many join, the results are what I would expect when looking at the total transaction amount by expertise. When I add in the field from the bridge table, however, it does not appear to be linked in any way to the other fields and results in all possible combinations:

Results

My guess is that there should be some kind of relationship established between the bridge table and the fact table, but I'm not sure how or what that relationship should be. Any help would be appreciated. Thanks!

1
many-to-manyssasolap-cube
I think I found the answer. It appears that I needed to set the relationship between my bridge and the fact table as a 'many to many' relationship using the bridge as the link. Making the connection that way allows me to query based on the bridge table field and the results are as expected. – skaaks
Exactly, you needed to set the relationship in the bottom right square - which is gray in your first picture. Could you write that as an answer, and acknowledge it as the correct one so that the question is shown as having an answer? – FrankPl

1 Answers

2
votes

As noted above, I needed to set the relationship between my bridge and the fact table as a 'many to many' relationship using the bridge as the link. Making the connection that way allows me to query based on the bridge table field and the results are as expected.