Aggregating Many-to-Many Relationships
Relax…this blog is all about Analysis Services and not about polygamy or something worse.
SSAS 2005 went beyond the “classic” OLAP model by introducing flexible dimension-to-measure group relationships, including many-to-many, referenced, fact relationships. M2M relationships can solve some nagging business problems. For example, in a recent project where we had to implement a financial cube, there was a requirement that a G-L account can belong to multiple account groups. This is a classic M2M scenario which can be elegantly solved by introducing a M2M relationship. Adventurizing this, let’s take a look at this schema.
Here, I changed the Adventure Works schema by adding a DimGroup and DimGroupEmployee tables. The DimGroup dimension is a parent-child dimension that lets the end user slice data by territory. An employee can be associated with one or more territories (think of a sales manager that covers multiple territories. This M2M relationship (groups-territories) is resolved via the DimGroupEmployee table. In the cube, the DimGroup is configured to join the Reseller Sales measure group via a Group Employee factless measure group that is bound to the DimGroupEmployee table. This lets us browse the reseller measures by group.
So, everything is cool, right? Yes, at least until I realized that the aggregated totals need to be adjusted for some account groups. To demonstrate this, suppose that you need a scope assignment that overwrites the Georgia’s total.
([Group].[Groups].[Georgia], [Measures].[Reseller Sales Amount]) = 10;
Do you see the problem? The Group1 total didn’t budge. However, if you do the same on a parent-child dimension that joins the measure group via a regular relationship, the total would change. Microsoft declared this behavior with M2M by design. When you come to think about it, it makes sense. To aggregate the measures, the server must go via the bridge table (DimGroupEmployee) to find the qualifying members (DimEmployee) that belong to the member on the one side of the relationship. So, when you make an assignment, you are essentially overwriting this behavior.
If a calculation has a slice in a M2M dimension, it applies to a cell only if the cell coordinate matches the M2M slice. When a cell is at a higher level for the M2M attribute, the calculation does not apply and will not be rolled up to the parent level. Intuitively, M2M attribute is non-aggregatable. In this case, when calculating grand total, the SSAS calculation engine will not sum up the subtotals from each group. However, if the assignment is made on many side (DimEmployee in our case), then the aggregating on DimGroup will work as expected.
Now, of course as a modeler, you would hope of a magic switch to let you roll up the assigned values to align the M2M behavior with regular relationships but for now we have to deal with the “by design” behavior. Microsoft suggested a workaround to introduce a scope assignment that forces the M2M members to aggregate, something like:
SCOPE ([Group].[Groups].Members);
this =
IIF (IsAncestor ([Group].[Groups].CurrentMember, [Group].[Groups].[Georgia]),
sum([Group].[Groups].CurrentMember.children),
([Group].[Groups].CurrentMember, [Measures].[Reseller Sales Amount])
);
End
scope;
This works but it may introduce a performance penalty with larger dimensions. Instead, in our case, we decided not to use an assignment at all. We introduced a new helper measure to the measure group (FactResellerSales in this case) that would perform the required calculations. Since the measure values are mapped multiple times to the many side, we use simple assignments to zero out the measure for the groups that it doesn’t apply.
