Project Botticelli

What is a Subselect in MDX? Get Free Access Purchase this course

2 April 2014 · 1 comment · 5709 views

Source of the most common MDX confusion

Chris explains the MDX Subselect

Chris Webb introduces the subselect in this short 3-min video. Log-in or get a free account to watch it!

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. 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. While this short video introduces the concept of a subselect, make sure to watch Chris’s full-length module on Exists, MDX Solve Order, and Subselects to learn about those important differences.

Log in or register for free to access this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

rahul.joshi · 15 June 2016

If we need to pre-filter the hierarchy members - but based on a dynamic condition - say which is driven by user input - how would we use the sub-select ? I tried using a Where clause inside the sub-select query - but the outer query (which contains the main computation) still runs across the entire set of members of the hierarchy - which was pre-filtered in the sub-select ? Is there a different and more elegant solution here ?

Jump to a chapter

Online Courses