Almost two years ago, in January 2005, I have done a little research on the popularity of MDX functions. The methodology was to search the Analysis Services newsgroup for the names of MDX functions. Since some of the MDX function names are also very common English words - they were excluded from the statistics. But there were enough function left to do interesting analysis. Back in January 2005 most of the posts on the newsgroup were about Analysis Services 2000. About June 2005, Microsoft switched from public newsgroups to MSDN forums as a community support system. MSDN forums have many advantages compared to newsgroups - it is possible to mark posts as "Answers", subscribe to email alerts for the interesting threads etc. The migration started slow, but by now activity on the Analysis Services MSDN Forum seem to surpass activity on the microsoft.public.sqlserver.olap newsgroup (as judged by number of new topics per day). Anyway, since MSDN forums started around the time when Analysis Services 2005 was only couple of CTP's away from shipment, vast majority of threads on the Analysis Services MSDN Forum are about AS2005. So I decided it would be interesting to compare the statistics about 10 most frequently cited MDX functions in AS2005 vs. AS2000. Below are results:
| Analysis Services 2000 | Pct | | CurrentMember | 29.21% | | IIF | 10.69% | | Descendants | 6.59% | | Generate | 5.35% | | Crossjoin | 4.73% | | NonEmptyCrossJoin | 2.98% | | Avg | 2.79% | | YTD | 2.27% | | IsEmpty | 2.11% | | TopCount | 1.68% | | | Analysis Services 2005 | Pct | | CurrentMember | 15.67% | | Generate | 6.88% | | YTD | 6.43% | | IIF | 6.19% | | Descendants | 4.32% | | CrossJoin | 3.83% | | Max | 3.78% | | AllMembers | 3.34% | | NonEmpty | 3.14% | | TopCount | 2.90% | |
There are several interesting points to make here.
- The distribution of functions seems to be less skewed in AS2005. Top 10 functions made 68% of all in AS2000, but only 56% of all in AS2005. I don't have good explanation why is that. It's true that AS2005 has few more MDX functions, but most of them had very little impact (KPIValue mentioned only 3 times for example), with NonEmpty being the only exception (and I didn't count neither Exists nor EXITSING - since both of them are common English words).
- CurrentMember is still a clear and undisputed leader, but it lost half of its market share from 30% to 15%. Is it because people follow mine and others advice to omit redundant CurrentMember in order to improve performance. Or, perhaps, they realize that redundant CurrentMember breaks multiselects in WHERE clause as described here.
- NonEmptyCrossJoin is kicked out of top 10 !!! And newcomer NonEmpty gets bigger market share - 3.34% vs 2.98% (although a lower rank in the table). I like to think that people realize that NonEmptyCrossJoin is evil, and NonEmpty is a good, performant replacement when applicable.
- YTD rose from position 8 to position 3 in the table, and gained market share from 2.27% up to 6.43%. YTD is almost exclusively used in running sum calculations, i.e. SUM(YTD()), and everybody should be happy that exactly this kind of calculations is now much much faster in SP2!
- IIF dropped both on position from #2 to #4 and in market share from 10.69% to 6.19%. I don't hide that I don't like IIF function, and I always tell people to rewrite their MDX not to use IIF. The most common technique is to use SCOPE instead. SCOPE is not MDX function, but I counted it as well, and if it was MDX function it would've made the table on the 5th place with 5.5% market share. So could it be that people realize that IIF should be replaced with proper SCOPE ? (And it's not only me writing about it in the blog here, here and here - the official Analysis Services Performance Guide talks about it as a first thing in the "Writing Efficient MDX" chapter!).
- Descendants lost some market share, and slipped from position #3 to position #5. This can be probably attributed to the fact, that in UDM world Descendants is not as essential. For example, Descendants(Geography.USA, Geography.City, SELF) can be rewritten using attribute hierarchies as Exists(City.City.MEMBERS, Country.USA). Or inside calculations SCOPEs, the explicit use of Exists is not even needed because of autoexists, i.e. SCOPE (City.City.MEMBERS, Country.USA) is preferable to SCOPE (Descendants(Geography.USA, Geography.City, SELF))
- TopCount remains popular enough to just make the Top 10 :) Again, everybody will be delighted that SP2 fixes problems with TopCount and WHERE clause when they use attributes from the same dimension.
- IsEmpty is gone from the table (it is now #16 down from #9). It got replaced with NonEmpty function, which is a better way to handle Filter(..., IsEmpty(...)).
- Avg got replaced by Max on the 7th place. I have no good explanation for that. Perhaps usage of Avg declined due to AverageOfChildren semiadditive measure ?
I am sure that some of the speculations above are just my imagination, but the overall trend is good. The use of functions which I labeled as "bad/deprecated" in AS2005 has declined, and the use of good functions has increased. The OLAP world is becoming a better place :)