Tecflix

Exists, MDX Solve Order, and Subselects Purchase the entire course

2 April 2014 · · 3934 views

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.

Log in or purchase access to play the video.

  • MDX Basic Concepts 29-min Watch with Free Subscription

  • MDX Queries 24-min Watch with Free Subscription

  • MDX Calculated Members 52-min

  • MDX: Member and Set Functions 38-min

  • What is the MDX Current Member Function? 5-min Free—Watch Now

  • MDX Numeric Functions and CURRENTMEMBER 33-min

  • MDX: Previous Period Growths, Shares & Moving Averages 38-min

  • More MDX Member & Set Functions 1-hour 37-min

  • What is a Subselect in MDX? 3-min Watch with Free Subscription

  • Exists, MDX Solve Order, and Subselects 49-min

  • MDX Performance Diagnosis with Profiler 29-min

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo