다음을 통해 공유

SSAS Troubleshooting: Incorrect Amounts for Linked Measures in Cubes containing Many-to-Many Relationships

  • 아티클

Applies to: 

  • SQL Server 2012 and
  • SQL Server 2014
  • Analysis Services multidimensional models

A linked measure evaluated in the context of a many-to-many relationship can produce incorrect aggregations, either NULL or double-counted values when you add a new many-to-many that is unknown in the original cube.

When using linked measure groups in a many-to-many relationship, the many-to-many can be defined in either the original cube, or in the cube that has the linked measure group, but not both.  When multiple,  independent many-to-many relationships exist in either the source measure group and the linked measure group, it can lead to ambiguity when determining what data to retrieve and aggregate, sometimes resulting in unexpected query results for dimension members.

Background

Recall that a linked measure is created in one cube, and then referenced in a different cube. For the purposes of this post, we’ll refer to these cubes as ‘source’ and ‘destination’ cubes. The problem described in this post occurs when both of the following conditions exist:

  • In the source cube where the measure is originally defined, the measure is evaluated against two or more dimensions that have a many-to-many relationship with each other.
  • In the destination cube where the measure operates as a linked measure, you create a new many-to-many relationship, specifying additional dimensions and intermediate measure groups that are unknown in the source cube.

After you create the new dimensions and relationships to the linked measure, it’s possible that you will see erroneous values for that measure. This issue only occurs in models having many-to-many relationships, when the intermediate measure group is missing association data for dimension members.

Example

The following example provides context for understanding this problem.

Consider a restaurant conglomerate that has many restaurant chains, located in many cities. A corporate Sales cube stores data about daily sales, locations, chains, products, and so forth. City and Chains are a many-to-many relationship. A city can have restaurants in more than one chain, and each chain can have restaurants in multiple cities. Also within our corporate Sales cube is a Daily Sales measure group. In our analysis, we want to track Daily Sales by City, by Chain, or both.

Issue

Now suppose that we want to repurpose parts of this model in other cubes, using linked measures to represent Daily Sales in subsequent cubes. For example, the Advertising group wants a separate cube for measuring the effectiveness of promotions,  leveraging an existing measure group like Daily Sales.

As long as the Advertising cube uses City and Chain as designed in the base cube, Daily Sales will be correct because City and Chain have a predefined many-to-many relationship that automatically adjusts Daily Sales accordingly, showing sales by City or Chain without inflating (double-counting) the results. When the linked measure is used as originally designed, the values associated with a linked measure should be correct regardless of which dimension is used with it.

Incorrect behaviors begin to occur when you add new dimensions to a linked measure. Suppose you create a new many-to-many relationship in the Advertising cube, such as Campaign and Promotion, also related to Daily Sales. A campaign might have multiple promotions, and a promotion might belong to multiple campaigns.

The presence of this new many-to-many relationship, which exists only in the Advertising cube and not in the Sales cube, will result in inflated or null values in our analysis. This behavior occurs because Daily Sales is not related to Advertising Campaign in the source cube, causing data from Daily Sales to be linked multiple times in the intermediate group relating Chains to Cities.

Workaround

To avoid this problem, redesign the model, replacing linked measure groups with local measure groups. Alternatively, modify the original cube to include all of the dimensions and many-to-many relationships that you want to use, which means both the source and destination cubes have the same objects.

Contributors

Wayne Robertson, a programmer on the Analysis Services development team, helped me out this with the post. Thanks, Wayne! If you can improve this article, please do so and then add your name to this list so we know who to thank.