Link Members from Two Data Sources

If you create a master detail relationship using queries that reference two different dimensional data sources, you may need to create a relationship between levels with the same member captions but different Member Unique Names (MUNs).

For more information about MUNs, see Recommendation - Use Member Unique Name (MUN) Aliases.

For example, you may want to link the Americas member in the Sales territory level in the Sample Outdoors cube with Americas in the GO Subsidiary level in the Sample Outdoors Finance cube. To create the relationship, you must alias the levels that contain the members to link.

Procedure

  1. Select the parent frame or the report page, click the More icon More icon, and click Go to query.
  2. Add a query calculation to the query.
    1. Click the Toolbox icon The toolbox icon and drag the Query calculation tool to the Data Items box.
    2. In the Create calculation window, click the Other expression radio button and click OK.
    3. In the Name box, type a name for the calculation.
    4. In the Expression Definition box, type the following expression:

      caption (data item name)

      For example, caption ( [great_outdoors_company].[Sales Territory].[Sales Territory].[Sales territory] )

  3. Replace the level that contains the member that will provide the primary information with the calculation.
    1. Click the report page from step 1.
    2. Click the Insertable objects icon Insertable objects and then click the Data items tab Queries icon.
    3. From the query where you created the query calculation, drag the calculation to the parent frame or the report page, beside the level that you are replacing.
    4. Delete the level.
  4. Repeat steps 1 to 3 for the level in the data container that contains the details.

    A caption alias is created for each level. You can now use the caption alias for each level to create a master detail relationship using member captions, which are the same, instead of MUNs, which are different.

Results

You can now create the master detail relationship between the two levels.