Creating Analysis Services Cubes - SQL Server Analysis Services 2012 Cube Development - page 93

Relationship
Type Explanation and examples

FactInternetSales table to FactInternetSalesReason us-
ing the Sales Order Number and Sales Order Line Number
columns. Next, relate the FactInternetSalesReason key to the
DimSalesReason table using SalesReasonKey . You can review
the relationship between tables using a sales order reasons dia-
gram within the data source view. On the dimension usage tab, you
must relate the Sales Reason dimension with the Sales Reasons
measure group using a regular relationship. Next, relate the sales
reason dimension to the Internet sales measure group using a
many-to-many relationship, specifying the reasons for sales as the
intermediate measure group.

Although the many-to-many relationship offers great flexibility and is
essential for some modeling scenarios, it should be used only when
necessary and handled with care. Keep in mind that many-to-many
relationships are not materialized; instead, Analysis Services runs a
query against the relational data source to derive the MDX query
results during the execution when using ROLAP. Even if you use
the MOLAP storage, Analysis Services has to perform the in-
memory join operation of the data, which has already been imported
as part of processing. Chapter 4 , Extending and Customizing
Cubes , provides additional information about many-to-many rela-
tionships.