Subselects, SCOPEs and hierarchy navigation MDX functions
One of the hardest things that Analysis Services users are facing is interaction of different features. Subselects and CREATE SUBCUBE statements can work fine by themselves, MDX Scripts containing SCOPE and assignment statements also work fine by themselves. MDX hierarchy navigation functions such as Children, Parent, Lag etc work fine by themselves too. But what happens when you put all of them together. The result could be quite unpredictable. In this article we will review such scenarios, and recommend solution which provides best performance.
Let's consider the following MDX query from the Adventure Works cube..