PerformancePoint Back-End Rules vs. Excel Formulas
Posted by Alan on April 22, 2008
So we have had some discussion here about the best time to use back-end MDX or SQL rules created in the Modeler vs. Excel formulas that are created within the Excel input form. Some scenarios are simple and some are not so clean cut.
For instance, it makes sense to use a back-end rule when you have to move data between multiple models or when you want to seed values into an input form for users to see when they open the form (i.e., last year’s actuals in this year’s budget input form).
However, some situations are more vague. How about when you want to multiply the value found in one cell by the value in another and have the results appear automatically inside a third cell? Either a back-end rule or an Excel formula could work here, so which do you use?
For me it all comes down to the level of control you need. The advantage of a back-end rule is that the end-user cannot overwrite it and the calculation is carried out exactly like the author wants. On the other hand, the Excel formula is easier to create and has the advantage of helping guide the user to the final outcome but gives them the flexibility to go outside the “rules” if necessary.
So which is best? Ultimately it comes down to “it depends”. I see use for both when deploying PerformancePoint.