CalculationPassNumber, CalculationCurrentPass are some functions of Analysis Services that I want to run away from, but it makes me sick to know that they are one of the most important functions to know whilst in an extremely, perhaps dreadfully complex calculation situations. I have been beating my head to understand this for quite sometime now but instead have learnt more about some other things in MDX and Analysis Services. Though my knowledge of MDX and Analysis Services is much better now but this "Pass Number" is something that is turning out be my worst struggle for knowledge.

## Comments:

I feel a little guilty, because I promised I would get back to you on the issue of calculation pass number (and then, of course, completely forgot).

The key to understanding calculation pass number is to remember that, inside a cube, you might need to be able to refer back to what value a cell *had* (at some point) before its current value. This is typically the case with recursive or "goal-seeking" applications. It is particularly important when you may legitimately need multiple cell calulations inside a cube, which interact with each other, but where the order of that interaction is vital to a correct outcome.

So, when constructing the cube, you identify the cell calculation(s) you think you need. For each, ask yourself if this cell calculation needs to be applied multiple times; if you can't decide whether it does or doesn't, then it probably doesn't! ;-) That should give you your calculation pass *depth*. If you have other cell calculations, ask yourself the same question for each, until you have determined the calculation pass depth for each.

Next, you can decide the order of precedence between the cell calculations (if you have more than one). The ones that must apply first, have lower calculation pass *number*.

Finally, ask yourself whether any cell calculation depends on knowing the value in a cell at a specified pass number (e.g. the original data, which is pass number 0; the previous pass, in which case you use a relative reference; or a specific pass, in which case you specify an absolute pass number); if that's the case, then you use the CalculationPassValue() function to specify it. If you're not sure which pass you're actually on, then that's returned by CalculationCurrentPass().

Simple, really! ;-)

In reality, this falls into the category of the kind of MDX which, until you actually *need* it, you may find difficulty in understanding. But if you don't know it *exists*, you might not think to use it when you need it. What I suggest is that you don't give yourself a headache trying to work it out now; if or when you're faced with a situation when you know, intuitively, that cell calculations are the right way to tackle a problem, but you can't quite get them to give the results you want, *then* you're probably at a point where you need to specify more than the default behaviour for calculation pass number and / or calculation pass depth, and need to use CalculationPassValue(). And, faced with a specific requirement that makes sense to *you* (rather than one you've read in a book, or in Books Online), I'm sure you'll find that the meaning suddenly becomes clear, and you'll wonder why you ever had such a problem with it. Trust me! ;-)

software

## Post a Comment

<< Home