Another use of a goal-seeking formula might be to determine how much revenue your company must make next month to meet revenue objectives for the year. To determine this amount, you can create a goal-seeking algorithm that tries various revenue values for next month; with each value, the formula uses a forecasting algorithm to determine what your total revenue for the year might be.
To control both iterative formulas and evaluation order, you can use pass numbers and solve order together. Solve order specifies the evaluation order within a single evaluation pass. You can then use pass numbers to control which iteration of a multipass formula will use a cell calculation. The pass number specifies the first pass in which the formula is used, and pass depth specifies the number of passes to use the formula. For example, you could specify that a cell calculation has a pass number of 3 and a pass depth of 2—meaning that Analysis Services will perform the calculation at pass number 3 and pass number 2. The calculation won't be in effect for pass number 1 because a calculation defined with a pass number of 3 would have to be active for 3 passes to reach pass number 1 (e.g., a pass depth of 3).
If you can use solve order to control the evaluation order of two calculated members, why would you need to use pass numbers? Although you can use pass numbers to control evaluation order, that's not generally why you use them. Pass numbers are important in more complicated formulas, such as recursive calculations (i.e., calculations that reference themselves) or goal-seeking calculations.
Consider the code example that Listing 4 shows. The first thing you might notice is the abundance of a function called CalculationPassValue(). This function lets you control which pass numbers Analysis Services uses to determine a formula's value. For example, CalculationPassValue(Time.CurrentMember, 0) means that you want the value for Time.CurrentMember after pass number 0, ignoring all other passes. Referencing pass number 0 is useful when you want the actual value that's loaded in the cube. In other words, you don't want other cell calculations to affect the value you're retrieving.
The MDX query in Listing 4 returns the Unit Sales for the months of 1997, which Figure 3, page 68, shows. The row labeled OldUnitSales shows the Unit Sales values as they exist in the cube. The row labeled Unit Sales contains values affected by the cell calculation MinRecentValue. MinRecentValue is a recursive formula that returns the lesser of the current month and the previous month values. The formula is recursive because the same formula determines the previous month's value. The formula continues searching through previous months as long as the values continue to get smaller. Two conditions cause the formula to stop searching: the formula finds a month value that is larger than the successive month or the formula has exceeded its pass depth.
Because the MDX query in Listing 4 has a pass depth of 1, the cell calculation looks back only 1 month. Figure 3 shows that month 6 returned the value 21,081.00. The cell calculation MinRecentValue compared month 5 with month 6 and returned month 5 because it was smaller.
Figure 4 shows the results you get if you change the query's CALCULATION_PASS_DEPTH property to 3. Notice in this result that the month-6 value is 20,179.00. Because the pass depth increased, the formula could recurse (call itself) three times before it reached pass number 0, in which the cell calculation was no longer in effect. For month 6, the formula goes back to month 4. The formula determines that month 3 has a value greater than month 4, so it stops and returns the month-4 value.
Note that when a formula calls itself, that recursive call doesn't change the pass number—in other words, a recursive call doesn't constitute another execution pass. You can always reference another pass with the CalculationPassValue() function, as the MinRecentValue function does. The reason the MinRecentValue formula changes pass numbers when it calls itself is that it uses CalculationCurrentPass()-1 to reference the next lower pass number.
To write effective cell-calculation formulas, you need to have at least a cursory understanding of pass numbers. Even the simplest cell-calculation formulas can mushroom into infinite recursion if you're not careful. For example, say you write the following formula to try to scale down all the month values in the Sales cube by 10:
(Time.CurrentMember, [Unit Sales]) / 10
The problem with this formula is that it references the same cell that initiated your cell calculation. Therefore, the formula will call itself indefinitely. The only way to avoid this infinite-recursion problem is to reference the cell value from a lower pass number, as the following formula shows:
CalculationPassValue( (Time.CurrentMember, [Unit Sales]), 0 ) / 10
The concepts of solve order and pass numbers are complex, and you need practice to learn when and how to use them. If you're writing queries that select calculated members on more than one axis (e.g., rows and columns), you definitely need to consider using solve order. If you're using calculated cell formulas, you probably need to use pass numbers. And if you use multiple calculated cell formulas with recursion, you might need to use both solve order and pass numbers—but I'll leave those scenarios for you to explore.
To practice writing MDX queries that return ordered lists, tackle the puzzle in the Web sidebar "September MDX Puzzle," http://www.sqlmag.com, InstantDoc ID 21988. For the answer to the August puzzle, see the Web sidebar "August MDX Puzzle Solution Revealed," InstantDoc ID 21989.
End of Article
Prev. page
1
[2]
next page -->
