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

Note: Unhandled NULL values in key columns will result in incomplete aggregate tables and unexpected query results. For more information, see Using Custom Empty Members For Levels And Attributes.

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:

1. Create a new normal dimension based on a bridge dimension dataset. When adding levels, make sure to specify Key Columns that can join this dimension to the model's fact dataset. Multi-valued dimensions typically have compound keys.
2. Open the the multi-valued dimension you just created and switch to the Canvas tab.
3. Edit the key level of the initial dimension hierarchy. Besure to disable the Unique and Visualize in BI tool? options.
4. Define additional levels that can be used to join to the secondary dimension. Be sure to disable the Unique and Visualize in BI tool? options for these levels, as well.
5. Drag the logical secondary dimension from the Repo Browser to the dimension Canvas.
6. Create a dimension-to-dimension relationship between the bridge dimension's dataset and the logical secondary dimension you are joining to.
7. In the model Canvas, create the relationship between the bridge dimension and the fact dataset.