What You Need for ManyToMany

In the Dimensional usage spec, you establish which cube, what dimension, and what dimension ID. You specify a measure group and an intermediate measure group. So, you need all of these ingredients to use the ManyToMany relationship type.

For example, how do you establish a dimensional usage of relationship type ManyToMany from the Revenue measure group to the Constituency dimension? if you already have a fact (measure group) for Constituency, you can create a measure group based on it to use as an intermediate measure group. Here is an example measure group spec for a constituency pivot:

<MeasureGroupDeploymentInfo>
  <Cube>BBDW</Cube>
  <DSVTable>BBDW_FACT_CONSTITUENCY</DSVTable>
  <MeasureGroupName>Constituent Constituency Pivot</MeasureGroupName>
  <Measures>
    <Measure>
      <Name>Constituent Constituency Pivot Count</Name>
      <AggregateFunction>Count</AggregateFunction>
      <Visible>false</Visible>
      <!-- This field is actually not needed for counts -->
      <Column>CONSTITUENCYFACTID</Column>
    </Measure>
  </Measures>
</MeasureGroupDeploymentInfo>

This measure group spec defines a measure group with a measure that is a count of constituencies. If there was not already a data source view for constituency, you would have to define one with a DSV table extension spec. If the existing table was not sufficient, you would have to extend it with a fact extend extension spec. The measure group will contain the columns that are on BBDW_FACT_CONSTITUENCY.

The measure in the example is not visible because it will not be used directly when exploring the cube. But how is Constituent Constituency Pivot connected to the Constituency dimension? Through a Regular dimensional usage...

<DimensionalUsageDeploymentInfo>
  <Cube>BBDW</Cube>
  <DimensionID>Constituency</DimensionID>
  <DimensionName>Constituency Pivot</DimensionName>
  <MeasureGroupRelationships>
    <MeasureGroupRelationship>
      <RelationshipType>Regular</RelationshipType>
      <MeasureGroup>Constituent Constituency Pivot</MeasureGroup>
      <MeasureGroupRelatedColumn>CONSTITUENCYDIMID</MeasureGroupRelatedColumn>
      <DimensionKeyAttributeID>CONSTITUENCYDIMID</DimensionKeyAttributeID>
    </MeasureGroupRelationship>
    <MeasureGroupRelationship>
      <RelationshipType>ManyToMany</RelationshipType>
      <MeasureGroup>Revenue</MeasureGroup>
      <IntermediateMeasureGroup>Constituent Constituency Pivot</IntermediateMeasureGroup>
    </MeasureGroupRelationship>
  </MeasureGroupRelationships>
</DimensionalUsageDeploymentInfo>

The dimension, Constituency Pivot, is defined in DimensionName. It is a reuse of Constituency. A dimension for Constituency, referenced in DimensionID, comes out-of-the-box with BBDW OLAP. The dimension structure for Constituency appears in Business Intelligence Development Studio like this:

The table DIM_CONSTITUENCY contains the key CONSTITUENCYDIMID. This is referenced in DimensionKeyAttributeID.

What is the Intermediate dimension and how is that connected to the Intermediate measure group? This is accomplished with another regular dimensional usage. This time on the Constituent dimension:

<DimensionalUsageDeploymentInfo>
  <Cube>BBDW</Cube>
  <DimensionID>Constituent</DimensionID>
  <DimensionName>Constituent</DimensionName>
  <MeasureGroupRelationships>
    <MeasureGroupRelationship>
      <RelationshipType>Regular</RelationshipType>
      <MeasureGroup>Constituent Constituency Pivot</MeasureGroup>
      <MeasureGroupRelatedColumn>CONSTITUENTDIMID</MeasureGroupRelatedColumn>
      <DimensionKeyAttributeID>CONSTITUENTDIMID</DimensionKeyAttributeID>
    </MeasureGroupRelationship>
  </MeasureGroupRelationships>
</DimensionalUsageDeploymentInfo>

The ManyToMany is defined in the same dimensional usage spec that established the regular relationship between Constituency Pivot and Constituent Constituency Pivot.

<DimensionalUsageDeploymentInfo>
  <Cube>BBDW</Cube>
  <DimensionID>Constituency</DimensionID>
  <DimensionName>Constituency Pivot</DimensionName>
  <MeasureGroupRelationships>
    <MeasureGroupRelationship>
      <RelationshipType>Regular</RelationshipType>
      <MeasureGroup>Constituent Constituency Pivot</MeasureGroup>
      <MeasureGroupRelatedColumn>CONSTITUENCYDIMID</MeasureGroupRelatedColumn>
      <DimensionKeyAttributeID>CONSTITUENCYDIMID</DimensionKeyAttributeID>
    </MeasureGroupRelationship>
    <MeasureGroupRelationship>
      <RelationshipType>ManyToMany</RelationshipType>
      <MeasureGroup>Revenue</MeasureGroup>
      <IntermediateMeasureGroup>Constituent Constituency Pivot</IntermediateMeasureGroup>
    </MeasureGroupRelationship>
  </MeasureGroupRelationships>
</DimensionalUsageDeploymentInfo>

The connection is wrapped up with a regular dimensional usage of Constituent. This is out-of-the-box with BBDW OLAP.