MDX: Implicit Recursion

A few months ago Richard Lees did a blog post showing how you can calculate a LastNonEmpty measure using recursion. In Richard's example he used what I call "explicit recursion" in that he explicitly referenced the measure in it's own expression.

An example of this sort of calculation against Adventure Works would look like the following.

WITH
MEMBER Measures.LastNonEmptyExplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember, Measures.[LastNonEmptyExplicit])
            ,Measures.[Internet Sales Amount])
SELECT
  {[Measures].[Internet Sales Amount]
    ,[Measures].[LastNonEmptyExplicit] } ON COLUMNS,
  TAIL([Date].[Calendar].[Month].&[2003]&[7].Children,15)
ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].[Gloves]

If you run this you will see results like the following, I have included the raw measure so that you can see that way the calculation carries the last non-empty value forward. I have drawn in some arrows so you can see how the calculation would evaluate for the July 23 figure.

image

However as the title of this post would suggest, there is another variation on the syntax for doing recursion which I call "implicit recursion" and it looks like the following

WITH
MEMBER Measures.LastNonEmptyImplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember)
            ,Measures.[Internet Sales Amount])
SELECT
  {[Measures].[Internet Sales Amount]
    ,[Measures].[LastNonEmptyImplicit] } ON COLUMNS,
  TAIL([Date].[Calendar].[Month].&[2003]&[7].Children,15)
ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].[Gloves]

Note that the output is identical.

image

The only difference between these two examples is that the second does not explicitly reference itself, but because there is a .PrevMember call on the calendar date hierarchy and no other measure is reference the use of the current measure is implied. This is very subtle and I have seen it done accidentally before which lead to a lot of confusion.

Effectively "implicit recursion" is evaluated the same as if you had entered the following.

WITH
MEMBER Measures.LastNonEmptyImplicit
    AS IIF(IsEmpty(Measures.[Internet Sales Amount])
            ,([Date].[Calendar].PrevMember, Measures.CurrentMember)
            ,Measures.[Internet Sales Amount])

And because at the point where the expression is evaluated the "CurrentMember" on the measures dimension is the calculation itself we end up with a recursion.

So my advice is to always use the "explicit" version when doing recursion. If you have a recursive measure in your MDX Script I would even go so far as to suggest putting a comment indicating that it is a recursive measure. Recursive measures are really powerful and are an extremely elegant coding technique, but they do have a performance impact, so you want to make sure that you are using them explicitly and that they are clearly identified.

Print | posted on Saturday, November 6, 2010 6:21 AM

Comments on this post

# Need to contact admin

Requesting Gravatar...
Hello.
I need to contact admin.
Thank you.
Left by Williamgefs on Feb 08, 2017 7:53 PM

# I think nishtyak!

Requesting Gravatar...
The abundance of interesting articles on your site amazes me! The author - good luck and new interesting posts!

Listen, let's Makhnev weekend out of town and there is all this talk. You're from Moscow?


http://www.ngcmta.com/index.php?topic=140273.new#new djhf784yh3oh873
Left by LstrBedy on Feb 11, 2017 6:37 AM

# MP3 Downlaods Private FTP Server

Requesting Gravatar...
Choose payment method: Bank wire, WesternUnion, MoneyGram, Google Wallet, Webmoney.
72TB File Storage 0day 1990-2017.
IP restrictions: 3 IP addresses per user at the same time.
Overal server's speed: 500 Mbps.
Easy to use: Most of genres are sorted by days.
Server’s capacity: 72 TB for all FTP.
More Seven years Of Archives.
No Waiting Time, No captcha,No Speed Limit, No Ads.
Never Deleted Original Albums, Labels, Save Time And Money.
Updated On Daily: 20GB-50GB, 300-500 Albums 0-day WEB, Promo, CD, CDA, CDM, CDR, CDS, EP, LP, Vinyl...
Up Time: 99%
All Genre: House, Club, Techno, Trance, Dance, Italo-Dance, Eurodance, Drum and Bass, Psychedelic, Goa, PsyTrance, Progressive House, Electro, Euro-House, Club-House, Hardtechno, Tech-House, Dutch House, Minimal, Deep-House, Nu-Disco, Hardstyle, Hardcore, Jumpstyle, Electronic, Alternative, Alternative Rock, Ambient, Avantgarde, Ballad, Bass, Beat, Black Metal,Blues, Classical, Chanson, Country, Dance Hall, Death Metal, Disco, Ethnic, Folk, Folk-Rock, Funk, Gangsta Rap, Gothic Rock,Hard Rock, Heavy Metal, Hip-Hop, Indie, Industrial, Instrumental, Jazz, Jungle, Pop, Rock, Metal, Latin, Lo-Fi, New Age, Noise, Oldies, Pop-Folk, Progressive Rock, Psychedelic Rock, Punk Rock, Rap, Reggae, R&B, Rock & Roll, Soul, Soundtrack, Speech, Synthpop, Thrash Metal, Top 40, Vocal etc.
Account delivery time: 1 to 48 hours.

http://0daymusic.org/premium.php
Left by JohnnieIdoro on Feb 12, 2017 5:09 AM

Your comment:

 (will show your gravatar)