Here are some things to remember, think about and some “Do’s and Don’ts” when using Persistent Calculations:
- Save the Database.
Committing a Persistent Calculation is not the same as saving the data. The database has to be saved to permanently preserve the values written in a Cube, whether they are entered manually, or are the result of a committed Persistent Calculation. If you are working from a local database, remember to save. If you are on a server, the database will be saved automatically.
- Lighten the Formula Load.
In a case where you can’t use Dependencies in your Cube (i.e., some of your formulas use the CURRENT function or you are using children or subsets in formulas for which it would be difficult to maintain Dependencies), consider changing some formulas into Persistent Calculations. Simple data transfer type formulas, such as those that move detail data from one Cube to another, may balance the performance load in your Cube between what is calculating solely in RAM (formulas) and what has been written to disk and is less often recalculated (Persistent Calculations).
- Speed up the Complex.
Persistent Calculations will make what would be normally very complex and slow formulas optimized for speed as they do not need to be recalculated on each Slice refresh.
- Dependency or Persistent Calc.?
The fastest way to calculate a formula is to write it as an “All and” formula and check on Dependencies. If the formula has the “Details and” qualifier you must decide through testing whether the formula would be best with a Dependency written for it or written as a Persistent Calculation.
- Be Careful When Going from Small to Big.
Always consider the source of the driving members in your Persistent Calculations and how many cells they would affect when changed. If the driving data point comes from a Parameter cube with few Dimensions and will impact a larger Cube with more Dimensions, the re-generation time on the Persistent Calculation could be enormous. A good example is a Reporting Currency formula which would state: Reporting Currency = Local Currency x Exchange Rate. The Exchange Ratelikely resides in a small Cube. When these rates change, they will fire off the Persistent Calculation in the report Cube which may contain millions of affected data points.
- Don’t use with the CURRENT function.
Certain formulas can not be made into Persistent Calculations. Any formula which contains a dynamic variable like a CURRENT function or a CURRENTINDEX function.