Avoid defining many-to-many relationships when either the intermediate measure group or any of the intermediate dimensions have 1 million or more rows
This rule analyzes whether the estimated size of either of the following is greater than 1 million rows or members:
The estimated size of the intermediary measure groups.
The estimated size of the attribute key of the regular dimension that links an intermediary measure group to the main measure group.
Best Practices Recommendations
The process of resolving queries on dimensions that have many-to-many relationships involves more overhead than resolving queries on dimensions that have direct relationships. To avoid possible query performance issues, limit your use of many-to-many relationships to smaller intermediate measure groups and dimensions.
For More Information
For more information about defining many-to-many relationships, see Defining a Many-to-Many Relationship and Many-to-Many Relationship Properties in SQL Server Books Online.
For more information about performance issues related to many-to-many relationships, see the section, "Many-to-many relationships," in the SQL Server 2005 Analysis Services Performance Guide.