Solve Order and how it can improve your MDX

I thought this time around, we'll discuss how solve order can affect your MDX calculations. I found that people tend to ignore this property, as it is not mentioned in the "Form View" of the Calculation Tab of the cube (which is what people usually tend to use when they write MDX for a cube. I hope this will be taken into consideration in SQL Server 2008). Now, this is a shame, as solve order can greatly affect your calculations.

For each calculation you do in MDX, you can specify the optional property of solve_order. If you do not mention this property, it's default value will be zero. Solve_order is the property which actually determines in what order calculations will be done in case of a conflict for a certain cell. The calculation with the highest solve_order will be the one that's used for that cell.

There is still an exception for this rule in Analysis Services in SQL Server 2005. In SSAS, calculated members with the Aggregate function have a lower solve order than any intersecting calculated measure.

As another small tip, I would recommend you use solve_order in your calculations in jumps of 5 (that is: 5, 10, 15 etc.). That way, if you find after a while that you need to add another phase to your calculation, you won't have to go through re – numbering all of your solve order's – as you'll have the "space" to just add them in.

Be wary though! If you use calculated members, which are "competing" for the same cell in the cube, with the same solve_order, the outcome will be unpredictable.