Finding related members in the same dimension

The dimensionality of a cube equals to the number of hierarchies used in it. This encompasses all the user and attribute hierarchies, including a special hierarchy Measures, visible or not, as long as they are enabled. A cube with 10 dimensions, each having 10 attribute hierarchies is a 101D object! Comparing that to any 3D objects in your environment, such as a Rubik's cube, you will immediately be amazed by the space a typical SSAS cube forms. The number of coordinates, or shall we say cells, in that space is simply beyond our imagination.

Fortunately, a great deal of that space is empty and the SSAS engine has ways to optimize that. It even exposes some of the optimization features to us through several MDX functions we can use when needed.

In this recipe, we are going to look at two hierarchies, Color and Subcategory, and find the number of available colors in each of the product subcategories. Although these two hierarchies are from the same...