Many-to-Many Relationships: Add a Multi-Valued Dimension

This section explains how to model a logical dimension from a multi-valued or bridge dimension dataset.

Note: Unhandled NULL values in key columns will result in incomplete aggregate tables and unexpected query results. See the Custom Empty Member feature for more details.

About this task

Multi-valued dimensions are those where the key level that is used to join to the fact dataset can have duplicate values. Multi-valued dimensions are often used to resolve a many-to-many relationship between the fact dataset and another dimension. You model a multi-valued dimension by choosing a dataset and specifying the dataset columns that represent the key level of the dimension. The key level is not unique - the same value can appear in more than one record of the dataset. Once the dimension is created, you can then edit it to model additional levels, and connect it to a secondary dimension.

Procedure

To add a multi-valued dimension:

Create a new normal dimension based on a bridge dimension dataset. Make sure to specify the Key Columns that can join this dimension to the fact dataset of the cube. Multi-valued dimensions typically have compound keys.
Open the dimension editor canvas for the multi-valued dimension you just created.
Edit the key level of the initial dimension hierarchy, and make sure that it is marked as Not Unique and Not Visible.
Define the additional hierarchy levels that can be used to join to the secondary dimension. Mark these as Not Unique and Not Visible as well.
Drag the logical secondary dimension onto the dimension canvas.
Create a dimension-to-dimension relationship between the bridge dimension's dataset and the logical secondary dimension you are joining to. Choose many-to-many as the Relationship Type.
On the main cube canvas, create the relationship between the bridge dimension and the fact dataset. All relationships for dimensions directly connected to the fact data must be one-to-many.