It has been a terribly busy month – I actually started writing this post at the end of January, but only now am I able to finish it and fire it off to my blog. Feel free to post or e-mail me with any follow up questions

I ran across an issue at one client where a calculated measure was not behaving as desired (though it was behaving as designed!). Since this is a topic not likely to be broached in bar conversation or in the break room over hot tea, I wanted to cover a few cogent points here that might be useful to others.

Though several items were at the root of the measure not being calculated as expected, the issue that is common to anyone using calculated members is a misunderstanding of calculation passes and solve order (more here on MSDN). The MSDN article does a great job of showing calculated members in an MDX query, using solve order and the different result sets you will get with differing solve order sets, however the article feels a little dearth in its explanation of calculation passes and why solve order sometimes is necessary for correct calculations in your queries.

Analysis Services loads data into the cube in passes – on the first pass (pass 0) leaf members are loaded from fact data, the second pass (pass 0.5) loads the values of cells associated with sum, count, min, max, distinct and distinct count aggregate functions. After this is completed, then the load process can start on the cube calculations represented in the calculations tab. This is where, though intuitively you may recognize this, it is important to understand how calculation passes and solve order come into play.

For this example, I will reference the ubiquitous AdventureWorks example, now available via CodePlex:

In this cube, the calculated member [Total Sales Ratio to All Products] has a calculation reference to [Total Sales Amount]. Calculation pass comes into play here via the order of the calculated sets in the script. Notice that [Total Sales Amount] is at line 2 and [Total Sales Ratio to All Products] – just via placement in the calculation pane, you are declaring the pass order for these cube calculations. This is easy to see in this example, however on the customer that was experiencing a calculation problem, they literally had hundreds of cube calculations, with multiple developers having touched the product, so the last person in to work on new calculations and tweak old ones had not noticed the order of referenced calculations and thus had a hard time finding where the calc had gone wrong.

It is also important to remember that the last, or highest pass trumps previous passes when it comes to calculations. In the AdventureWorks cube, if we added another measure at line three that named [Total Sales Amount Revised], with the expression = [Total Sales Amount], then provided nothing else changed, we would then have two measures that had the same value. If we add to the script down at the last line that [Total Sales Amount] equals 20, then both [Total Sales Amount] and [Total Sales Amount Revised] would now equal 20 as the last pass wins in assigning values to the calculated members.

This is where FREEZE comes into play – in the scenario above, we can say: FREEZE([Total Sales Amount Revised]) on the line right after we set [Total Sales Amount Revised]'s value, and then when [Total Sales Amount] gets reset at the end of the script, the [Total Sales Amount Revised] will retain its values as calculated in the earlier pass. This can be incredibly useful for using role playing calculations in financial scenarios (which was part of my client's mission).

Solve order then, is used when you are writing out your query or sub-cube in MDX and need to replicate the calculation passes that the Calculations Tab manages in Visual Studio. You do not normally use solve_order in calculations in the cube designer, however it can be used to give Analysis Services your desired calculation path if in one script block on the calculations pane, you are doing two more advanced calculations that are linked. Since these calculations would be solved in the same pass, i.e., you placed them within the same scope statement, etc, then solve_order would explicitly spell out the order for the engine to calculate. Mostly, however, solve_order is used in MDX query examples as provided in the MSDN article.