Advanced Cube Design - Professional SQL Server Analysis Services 2005 with MDX - page 394

many-to-many dimension member, it aggregates the values in the measure
group based on the set of distinct regular dimension members related to the
current many-to-many dimension member. As a result, data is aggregated to
each level exactly once. For example, consider a sales order that has two dif-
ferent sales reasons A and B. If you request Internet Sales measure values
for that sales order by Sales Reasons — specifically members A, B, and All
— you will see that the measure values are aggregated to the All member ex-
actly once because there is only one distinct sales order related to the All
member. Similarly, there is only one distinct sales order related to A and to B,
so all three members will display the same values in this example.
Many-to-many relationships are common in data warehouses, and now, you
have the ability to model and analyze the data from many-to-many dimen-
sions. You can use a many-to-many relationship to perform currency conver-
sion as you saw previously in this chapter when learning about measure ex-
pressions. You can also perform weighted average calculations using the
many-to-many relationship in combination with a measure expression. Many-
to-many relationships can be modeled for any schema that contains at least
one common dimension between the regular measure group and the interme-
diate measure group. If there are multiple common dimensions between the
measure groups, Analysis Services 2005 aggregates values for each distinct
combination of members in those dimensions related to the current many-to-
many dimension member.
On the Dimension Usage tab, click the cell that intersects the measure group
and the dimension for which you want to define a many-to-many relationship.
In the Adventure Works sample cube, take a look at the many-to-many rela-
tionship between the Sales Reason dimension and the Internet Sales meas-
ure group. You can see the relationship requires the intermediate measure
group Sales Reasons, as shown in Figure 9-10 .