SQL Server Magazine

Solve order and pass numbers are among the most complex concepts in SQL Server 2000 Analysis Services' MDX language. Many analytical applications require calculations—in the form of calculated members, custom formulas, and cell calculations—to be embedded in the cubes. Individual MDX queries also frequently require embedded calculations. Because embedded and nested calculations are so common, the order in which MDX evaluates these calculations is crucial to achieving the correct results. You can use a solve-order keyword and pass numbers to control the order of evaluation. This topic is advanced, even for regular MDX users, so put on your crash helmet—we're diving in!

You often use two calculated members in combination in an MDX query, such as when you include a calculated member in the list of members for the columns and another calculated member in the list of members for the rows. Listing 1 shows a simple example of a new FoodDrink member that's the sum of Food and Drink and a new CAOR member that's the sum of CA and OR (California and Oregon). I included these members on the rows and columns, respectively. Figure 1 shows that these calculated members are totals of the columns and rows. When the two calculated members intersect (in the bottom right cell), you end up with a grand total. You can think about the combination of these calculated members as the sum of the right column, which you can express as

(CA, FoodDrink) + (OR, FoodDrink)

This formula is equal to

((CA,Food) + (CA,Drink)) + ((OR,Food) + (OR,Drink))

Or the formula could be the sum of the bottom row. You can express that sum as

(CAOR, Food) + (CAOR, Drink)

which is equal to

((CA, Food) + (OR, Food)) + ((CA, Drink) + (OR, Drink))

In Listing 1's query, the order of the calculated members doesn't matter because the formulas are simple summations. But the order does matter when the mathematical operators aren't transitive.

The MDX query that Listing 2 shows has two calculated members. The first calculated member, called Canned Percent, returns Canned Foods' percent of the total of Canned Foods and Canned Products. The second calculated member returns California's percent of all states in the United States. Both of these calculated members are percent-of-total calculations that are embedded within their own dimensions; they don't specify which measure (or numeric quantity) they operate on. Creating a calculation in a dimension rather than as part of a measure's definition can be convenient because you can combine such a calculation with any measure in a query to determine the percent of total for that measure. The MDX query in Listing 2 returns these two calculated members and a couple of the members from which these calculations derive. Figure 2 shows the result of the MDX sample application we're working with.

Again, look at the lower right cell, in which the two calculated members intersect. If Analysis Services evaluates the Canned Percent member first, the expression looks like

(CA, Canned Percent) / (USA, Canned Percent)

Expanding Canned Percent's formula gives you

((CA, Canned Foods) / ((CA, Canned Foods)
 + (CA, Canned Products))) /
((USA, Canned Foods) / ((USA, Canned
 Foods) + (USA, Canned Products)))

If you fill in the numbers, you get the totals that Figure 2 shows:

(5,268/(5,268 + 448)) / (19,026/(19,026 + 1,812)) = .9216/.9130 = 1.01

Now, look at how the formula appears if Analysis Services evaluates CA Percent before Canned Percent:

(CA Percent, Canned Foods) / ((CA Percent, Canned Foods) + (CA Percent, Canned Products))

If you expand CA Percent's formula, you get the following formula:

((CA, Canned Foods)/(USA, Canned Foods)) /
(((CA, Canned Foods)/(USA, Canned Foods)) +
 ((CA, Canned Products)/(USA, Canned Products)))

or

(5,268/19,026) / ((5,268/19,026) + (448/1,812)) = 0.528

The result of this evaluation order is different from the result that Figure 2 shows. If Canned Percent is evaluated first, the formula evaluates to 1.01 (or 101 percent), but if you evaluate CA Percent first, the formula evaluates to 0.53 (or 53 percent). If you needed to evaluate CA Percent first to get the appropriate result, you could use the SOLVE_ORDER keyword to force Analysis Services to give CA Percent priority. Listing 3 shows the query in Listing 2 with the SOLVE_ORDER keyword added so that CA Percent is evaluated first. You can use SOLVE_ORDER to control the evaluation order of calculated members and of calculated cells and custom rollups.

Now let's add complexity to the solve-order problem by introducing the concept of pass numbers. When executing an MDX query, Analysis Services resolves the embedded calculations in passes. Evaluation passes are identified by number. The last pass (i.e., the most nested) is always pass number 0. If a query doesn't contain cell calculations, custom rollup formulas, or custom rollup operators, Analysis Services executes the MDX query in a single pass—pass number 0. Analysis Services executes calculated members at pass number 0 and executes custom rollup formulas and custom rollup operators at pass number 1. Analysis Services performs cell calculations at pass number 1 unless you specify otherwise by changing the value of CALCULATION_PASS_NUMBER.

You can use this multipass execution process with pass numbers to create iterative formulas, such as goal-seeking formulas. Goal-seeking formulas are formulas for which you know what the outcome needs to be; you simply adjust formula input until you achieve the desired outcome. For example, you might want to know how much you need to decrease your product cost to increase your overall profitability by 10 percent. To solve this problem, your formula must try different product costs until it finds the value that achieves a profit increase of 10 percent.

   Prev. page   [1] 2     next page