Recursive MDX Doesn't Have The Same Need in SQL Server 2008

I am a great fan of SQL and often encourage people to exploit its power rather than use a procedural language to do the same. However MDX is a much more powerful language for data analysis. There really isn't any question that can't be answered in one MDX query, if the answer is in the data. This isn't exactly true for SQL.

For the last 8 years I have been teaching and mentoring MDX. One of my favourite topics is recursive MDX, which is where the MDX query queries itself recursively. Sounds a bit scary, but it has useful applications. A very common one was to efficiently add a series of members. Such as YearToDate, which might add up all the days (or seconds/minutes/hours if that is the lowest granularity of the Time dimension) to the current member. To do this, Analysis Services typically iterates through all the members, which might be thousands. By writing recursive MDX we can ask the engine to add the previous members until it gets to a FirstChild, when it can go up a level and add members at a higher level. For example, consider a Time dimension with granularity to hour, and we want to aggregate from the first hour in the year to the current hour. This might involve the addition of over 7,000 cells, and no aggregations would be used to reduce this number. But if we add the hours until we get to the first hour of the current day, then add preceding days until we get to the first day of the month, then add preceding months until we get to the first month of the year. This could reduce the calculation from 7000 to 30 cells, and make the query execution correspondingly faster.

Here is an example of recursive MDX for a YearToDate calculation. Measures.rt is a standard aggregation of all the cells. Measures.rto adds up to the same, but it navigates up to the parent member at the FirstChild of a member.

with member measures.rt as
sum([Date].[Year Month Day].[Day].members(0):closingperiod([Date].[Year Month Day].[Day]),Measures.hits)
member measures.rtro as
Measures.Hits+iif([Date].[Year Month Day] is [Date].[Year Month Day].parent.firstchild,(measures.rtro,[Date].[Year Month Day].parent.prevmember),(measures.rtro,[Date].[Year Month Day].prevmember))
select {Measures.hits,Measures.rt,Measures.rtro} on columns,
[Date].[year month day].members on rows
from EasternMining

I have always thought that it is a bit silly that we can write MDX queries that run faster than functions (PeriodsToDate, YTD etc) and that appears to have changed in SQL Server 2008. I have done a little performance testing with SQL Server 2008 and discovered that standard functions will perform faster than recursive MDX to navigate up hierarchies. Does this mean that we can forget about recursive MDX? I don’t think so, but it won’t be so close to the top of the toolbox.