Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 132

Defining relationships using the non-key granularity attribute is an important skill
to learn.
As we saw in Chapter 4 , Measures and Measure Groups , when we define
a dimension relationship using a non-key granularity attribute there
is a warning about possible issues when aggregating data. However,
using this technique with bridge tables for many-to-many relationships
shouldn't have any side effects because the bridge table doesn't contain
visible measures; so, the user will never query this measure group
directly and Analysis Services will only use data at the granularity of
the fact table to resolve the many-to-many relationship.
Finally, we have to define the relationship between the Sales Reason dimension and
the Sales measure group. To do that, we use a Many-to-Many relationship type in the
Define Relationship dialog box as shown in the following screenshot. The intermediate
measure group must be Bridge Sales Reasons , which we have just defined. Note that
we cannot use a measure group as an intermediate one in this dialog box if it does not
have a regular relationship with one of the other dimensions of the primary measure
group where we are defining the many-to-many dimension relationship.