Finding related members in another dimension

As mentioned in the introduction of the previous recipe, Finding related members in the same dimension, this recipe deals with a slightly different scenario. It explains how to find the related members from two or more different dimensions.

Before we start, please keep in mind that when we say a dimension, we mean any hierarchy in that dimension from now on.

Dimensions, unlike hierarchies of the same dimension, are unrelated and therefore independent objects. Without a third table in the form of a third fact table, they are unrelated, at least in the dimensional modeling sense. When a fact table is inserted among them, the many-to-many relationship comes into existence.

There are two different types of combination we can make with the dimensions. One type is the Cartesian product because they are unrelated. It is obtained by cross-joining members in both dimensions. In relational terms, that would represent the CROSSJOIN of two tables. Since those...