This 50-min video by Chris Webb covers three separate, advanced MDX topics: autoexists and the Exists() function and the Existing operator, solve order, and subselects.
Autoexists is something that happens when you crossjoin two sets containing different members on the same dimension together. When you do this, SQL Server Analysis Services filters out the tuples that can never contain values. For example, if you crossjoin a set of years and a set of dates, then Analysis Services will not return a set of tuples containing every combination of each year and each date; instead you only get tuples containing years and the dates in those years. This happens automatically, and you cannot stop it happening. You can take advantage of this type of filtering in your code by using the Exists() function, which filters the items in one set to return only those which ‘exist’ with the tuples in a second set. The Existing operator has a similar effect, but it filters a set so that only the items that exist in the current query context are returned.
Solve Order is a property of MDX calculations that controls what happens when you have two different calculated members from different hierarchies that overlap the same cell in a query. In some situations, changing the order of the calculations will change the final result returned in the query! The solve order of a calculation can be set in several different ways, including the SOLVE_ORDER property of a calculated member defined in the WITH clause, and the order that calculations are defined on the MDX Script of your cube. Calculations that are defined on the cube always have a lower solve order than calculations defined in the WITH clause unless the SCOPE_ISOLATION property is set, or a calculation uses the Aggregate() MDX function.
The purpose of subselects in MDX is often misunderstood and they are frequently used incorrectly. A query with a subselect has an MDX SELECT statement in its FROM clause, instead of the name of a cube, and what it does is this: it allows you to pre-filter the members on a hierarchy before you run a query. The main reason you would want to do this is to calculate subtotal values—a subselect runs a query in a what-if scenario, as if a hierarchy only contained some of its members and not all of them. You should not confuse a subselect with the WHERE clause because, although they return the same results in some cases, they differ in several important respects.
