Project Botticelli

MDX: Member and Set Functions Purchase this course

18 December 2013 · 2 comments · 4717 views

Thinking in sets

Chris Webb explains the Ancestor MDX function

Being able to think in sets is the key to being able to write more advanced queries and calculations in MDX. It’s not possible to write procedural code in MDX; instead you have to use functions that return sets and members to express what you want to appear on rows or columns in a query, and to express more complex transformations inside your calculations.

Chris Webb introduces the commonest MDX functions that return sets in this 40-minute, demo-driven video, starting with the .MEMBERS function and the CROSSJOIN() function.

When used with a hierarchy, the .MEMBERS() function returns a set consisting of all the members on that hierarchy; when used with a level on a hierarchy it returns a set containing all of the members on that level. The CROSSJOIN() function takes two or more sets, and it returns a set of tuples containing all of the possible permutations of the members in each set.

Another very useful family of functions are those that allow you to move up, down, forwards and backwards in a hierarchy, and which return a set, or a member relative to another member: the hierarchical functions. To move up or down the hierarchy, you can use the .PARENT function, which returns the member immediately above a member in the hierarchy, or the .CHILDREN function, which returns the set of members underneath a member. You can also use the DESCENDANTS() function to return the complete set of members underneath a member at every level of the hierarchy or at a particular level and the ANCESTOR() function to return the ancestor of a member above it in a hierarchy at a given level. To move one member forwards, or backwards along a level from a given member, you can use the .NEXTMEMBER and .PREVMEMBER functions, while to move forwards or backwards more than one member you can use the .LEAD() and .LAG() functions. Finally, to find the set of members between two members on the same level of a hierarchy you can use the range operator, a colon (:).

Make sure to watch, and to follow Chris’s examples from this video, as these are some of the most useful, and fundamental MDX functions you will need in your work.

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

mgltd · 25 January 2016

Hello chris, I am trying to create a calculated measure for the induvidual months in a cube, please when I use the tail and nonempty function on rows it gives the correct value (query below)

select
{[Measures].[Vol]} on columns,
tail(nonempty
( [Dates].[Month Short Name].[Month Short Name].allmembers),6).item(1) on rows
from
[ICS];
----------------------------
but when I try creating a calculated member with similar expression it gives the result for the whole set instead of the member (the query is below)

WITH MEMBER
[Measures].[Value]
AS
(tail(nonempty
( [Measures].[Vol],[Dates].[Month Short Name].[Month Short Name].allmembers),6).item(1))
select
[Measures].[Value] on 0
from
[ICS]

please any advice

Chris Webb · 25 January 2016

Do you want to get the value of the most recent month that is not null? Does the following blog post help? http://blog.crossjoin.co.uk/2011/03/24/last-ever-non-empty-a-new-fast-mdx-approach/

Online Courses