THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

What are the popular MDX functions ?

There are many MDX functions. The exact number depends on how you count (are methods and properties are counted, what about operators, are polymorphic functions counted once or as many times are there are overloads, what version of the Analysis Services we are talking about etc), but around 150 is probably right. Of course, not all of them are equally useful. Some seem to pop up in almost every MDX expression (CurrentMember for example), while others are so rarely used, that nobody even know they exist (LinRegR2 anyone ?). I always wanted to find out which functions people really use, and which ones we should not have bothered to add. So I decided to mine microsoft.public.sqlserver.olap newsgroup for answers. First, I thought I would use Google Web API, and even wrote a small C# program which hooked the output of DISCOVER_FUNCTIONS schema rowset with doGoogleSearch SOAP method. However, it turned out, that doGoogleSearch doesn't work with "group:<newsgroupname>" query term, so I had to send the search queries manually. For each MDX function I checked with Google how many different threads in microsoft.public.sqlserver.olap newsgroup was it mentioned. Note - this is not the number of times it was mentioned, but number of distinct threads. I collected results for some 108 MDX functions, but data required some more filtering/cleaninsing:

  • Some of the MDX function names are also common English words, like Except, Order, Count, Lead etc. Those functions are excluded from statistics.
  • Some other MDX function names are English words commonly used in OLAP terminology, like Filter, Aggregate, Sum, Members, Hierarchy etc. Those functions are excluded from statistics as well.
  • Some MDX functions names are concatenation of two words for similar concepts in Analysis Services, but don't have space, like VisualTotals, DistinctCount, DefaultMember. Such functions are not excluded from statistics, because cursory look over the threads revealed that the discussion was around the functions.
  • Still there were many MDX functions which also had independent meaning in English and OLAP, like Descendants, Ancestor, Head, Min, Rank etc. I decided to include them (otherwise I would only be counting functions with names like DrilldownMemberBottom - surely cannot be confused with any English word, but how useful is it). The numbers for these functions are probably overrepresented.
  • The frequency of postings into newsgroup doesn't necessarily indicates how useful the function is, but also how much trouble people have with it. Best example is NonEmptyCrossJoin, which made it into Top 10. Yet I am inclined to think that this is not because everybody uses it, but because nobody understands this function. I have actually never met anybody who fully understands how NonEmptyCrossJoin works. Even I have trouble with it from time to time.

Taking into account all the if's and but's above, the results are still interesting. Some things are predictable. CurrentMember is a clear leader, as expected. IIF, Generate, CrossJoin - are all in the top 10 as well. Another expected thing is symmetric functions like PrevMember and NextMember. PrevMember was mentioned 10 times more then NextMember - and this is natural, in calculations we tend to go back in time rather then forward. In fact, I was surprised that NextMember got 17 hits at all. Same story with TopCount vs. BottomCount - who cares about worst ten, we always look at best 10, aren't we ? Well, I will put both statistics in my report though :) But Head and Tail are going head to head with almost equal ranking. Not sure why is that, given that Head and Tail are used in pretty distinct scenarios. Also, the popularity of Ancestor surprised me, somehow I didn't deal with it much, but it overtook StrToMember, PrevMember and Lag, which are thought are much more widely used. Overall, results are interesting to look at. Definitely, when choosing which MDX function to tune performance for before we ship AS2005, I am going back to this list.

Functions mentioned in more then 200 threads

MDX Function # threads
CurrentMember 3660
IIF 1340
Descendants 826
Generate 670
Crossjoin 593
NonEmptyCrossJoin 373
Avg 350
Ytd 285
IsEmpty 265
TopCount 211

Functions mentioned in less then 5 threads:

MDX Function # threads
DrilldownMemberBottom 0
DrilldownMemberTop 0
IsSibling 0
LinRegR2 0
LinRegVariance 0
StdevP 0
VarianceP 0
CalculationCurrentPass 1
CovarianceN 1
DrilldownLevelTop 1
DrillupMemebr 1
StddevP 1
StripCalculatedMembers 1
VarP 1
BottomSum 2
DrilldownLevelBottom 2
DrillupLevel 2
LinRegIntercect 3
TupleToStr 3
Covariance 4

Full list can be found here: http://www.mosha.com/msolap/data/MDXFunctionsPopularity.xls

Published Monday, January 31, 2005 10:55 AM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement