Project Botticelli

Exists, MDX Solve Order, and Subselects Purchase this course

2 April 2014 · 5 comments · 3359 views

MDX Solve Order

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 below to the premium version of 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

mnkashama · 19 January 2015

Very good stuff! Thx Chris!

rahul.joshi · 16 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 ?

rtharappel · 16 June 2016

Thank you Chris for the tutorial its is very good information. Is there a best practice for having an MDX measure that references the currentmember function work in a subselect? Given the example below based off of AdventureWorks we have a measure that gets the prior year orders using paralle period and currentmember:

WITH
MEMBER [Measures].[Prior Orders]
AS
'([Measures].[Internet Order Count], ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember))'
select
{[Measures].[Order Count], [Measures].[Prior Orders]} on columns,
Hierarchize(
Generate
(
{[Date].[Calendar].[Calendar Quarter].&[2007]&[1]}
,Ascendants([Date].[Calendar].CurrentMember)
)
)
on rows
from
(
select
{[Date].[Calendar].[Calendar Quarter].&[2007]&[1]} on columns
from [Adventure Works]
)

We see the following returned:

Order Count Prior Orders
All Periods 1,091 (null)
CY 2007 1,091 2677
H1 CY 2007 1,091 1193
Q1 CY 2007 1,091 558

We are expecting the Prior Orders to be 558 for all the ascendants for Q1 2007.
Would there be a general approach for detecting the subselect and adjust the calculation in this case.

Thanks,

Chris Webb · 20 June 2016

Handling multiselect in MDX is a bit of a problem, and a scenario that Microsoft should have dealt with a long time ago and never did. There are a few different ways of dealing with the problem but the most popular is to use dynamic named sets as described here: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

youssef23 · 15 November 2016

simply an excellent course. Are you planning a course on SSIS? it will be helpful !

Thank you so much for your amazing training

Online Courses