Saturday, March 11, 2017

EPM 11.1.2.x – Planning/PBCS Best Practices for BSO Business Rule Optimisation

Recently I found a very good article about Hyperion Planning business rules' best practices, and share as below,

1. Introduction
This document is intended to provide best practices for Business Rule Optimisation for Planning and PBCS models.
It will cover items which can be edited within the script only i.e. syntax. Essbase.cfg settings for on premises Planning will not be discussed in this blog.
2. Environment Setting
Below are recommended Calculation Commands, which are the elements that instruct the business rule how to execute the calculations.
  • SET UPDATECALC OFF turns off intelligent calculation, which is best practice for business rules which use cross dimensional operators and where there may be multiple users accessing the same data block combinations. Using SET UPDATECALC OFF is considered best practice for Planning/PBCS applications where multiple users write to the database. If intelligent calculation is used, ensure it is producing expected results.
  • SET AGGMISSG OFF should be set for Planning/PBCS designs where versions are Standard Target or where non leaf data regions are used and cannot be excluded during your calculation. If the Planning/PBCS design has versions setup as Standard Bottom Up, then data is loaded at level0, where SET AGGMISSG ON will benefit.
  • SET NOTICE and SET MSG SUMMARY should only be used in development environment for individual calculation analysis. These calc commands should be removed once in production and/or after analysis is completed.
  • SET EMPTYMEMBERSETS ON should be used when Run Time Prompts (RTP) are included in FIX statements for Planning/PBCS models so that empty sets are not calculated. Prevents a calculation from running on all members when the set is empty.
3. Optimisation/Performance
  • Use templates in Calc Manager business rules to avoid repeating sections of code and make best use of RTP.
  • Review dependencies on dynamic calc members within each calculation. If possible, change the calculation to avoid including repeated use of dynamic calc or remove the dynamic calc.
  • Use FIX and IF to focus your calculation to ensure only data required is calculated.
  • Avoid the creation of a 0 (zero) data result (unless you want to see a zero e.g inventory levels). This will create a block which will be included in all FIX/IF statements and will be calculated. Remove zeros on data load, if possible, or prevent their creation in business rules.
  • Many rules have a check to see if, for example: IF ((“Budget”==#missing) OR (“Budget” ==0)). IF (“Budget”+1==1) or IF (Budget/Budget ==#missing) will give the same check but avoids the use of Boolean logic within the IF statement.
  • Where possible, perform dense calculations, which do not create blocks, before sparse calculations, which do create blocks. If you need to perform aggregations (e.g to get a total for an allocation calculation), ensure that you only aggregate the section of the data that is required for that allocation calculation.
  • Minimise the passes on a database where possible.
  • Avoid recursive formulas. Excessive recursion can create environment performance issues as well as adding a requirement to clear values to produce consistent results.
4. FIX Statements
  • FIX statements are used to focus the business rule i.e. to keep the # blocks being calculated to be as low as possible i.e. only calculate blocks that are needed.
  • FIX is recommended for sparse dimensions because, when used on sparse, it reduces the # blocks that are required for the calc.
  • Ensure calculations are done on level 0 of all dimensions when versions are bottomup in Planning/PBCS models.
  • Use outer FIX statements on sparse dimensions with inner IF statements on dense dimensions where possible.
  • All FIX statements should include members from ALL dimensions, except dimensions that are within the calculation. If a dimension is excluded, all members from that dimension will be included and it is likely that this is not required.
  • Nest FIX statements where possible to reduce the number of passes on the database. Each full FIX requires a pass on the database. For example, use an outer FIX for Version, Scenario, and/or any other dimension selections that are static throughout the business rule.
  • For Planning/PBCS business rules associated with web forms, leverage the selected page and POV members in FIX statements to reduce the number of blocks calculated.
5. IF Statements
  • IF can be used in member formula. FIX cannot.
  • IF should be used within FIX statements to reduce the #blocks that need to be accessed. IF brings all blocks within the FIX into memory.
  • Use outer FIX statements on sparse dimensions with inner IF statements on dense dimensions where possible.
  • Use ELSE instead of a combination of NOT and ELSEIF where possible to avoid unnecessary analysis of member values during the calculation. However, if an ELSE is not required, it is not necessary.
  • Order IF statements, if possible, where the most number of cases hit the first IF in the block. Use NOT within the IF to ensure this, if applicable. See blog for more information on using NOT in IF statements.
  • Review the Calc Member Block choice. A sparse member without dynamic calc dependencies would be a better choice.
6. Only Calculate Blocks Required
  • For Planning/PBCS models, use RTP to ensure that only the data required is included in the business rule.
  • Only aggregate/calculate data that is required at each stage of the calculation to ensure you keep the number of blocks included in the calculation as low as possible for as long as possible.
7. Level of Calculations
  • For Planning/PBCS models, ensure calculations are done on level 0 of all dimensions when versions are bottomup.
  • For Planning/PBCS models, aggregations should only be included in the BSO Plan Type if required for the approval process. Other aggregations should be moved to the ASO Plan Type.
  • Try and keep the number of blocks included in your calculations to be as low as possible, for as long as possible.
8. Syntax
  • Always use @LEVMBRS rather than @RELATIVE if used on the entire dimension.
  • Use @CHILDREN instead of @RELATIVE, if applicable.
  • Use @REMOVE and @LEVMBRS if you only want to exclude some members from a FIX.
9. Block vs Cell Mode
  • Using block mode, where cells are grouped within the block and simultaneously calculated, is generally faster but data dependencies must be carefully considered e.g. SalesYTD = CurMth + PriorMth would have to be calculated in cell mode so that each month is calculated in the order of the outline.
  • Using cell mode, each cell is calculated sequentially in the order of the dense dimensions in the outline, is generally slower than block mode.
  • Use @CALCMODE to manually control block vs. cell mode.
  • Use debug mode application logs to verify calc mode. If a calculation is performed in block mode, no message will appear. A log message will be shown where calculations are performed in cell mode.
10. BottomUp vs TopDown
  • Add calculation function @CALCMODE(BOTTOMUP); or calculation command SET FRMLBOTTOMUP to calculate existing blocks only (BOTTOMUP) instead of potential blocks (TOPDOWN).
  • TOPDOWN calculations will calculate all potential data blocks with the member. For this reason, it is important that any irrelevant members within sparse dimensions are removed.
  • Thoroughly test calculations using BOTTOMUP to ensure that blocks are created correctly when using @CALCMODE.
  • Ensure testing is completed with clearing data and re-running the calculation to ensure all blocks are created correctly, especially when using BOTTOMUP.
  • Use debug mode application logs to verify calcmode. If a calculation is performed BOTTOMUP, no message will appear. A log message will be shown where calculations are performed TOPDOWN.
11. Create Blocks
  • Blocks, generally, will be created on the following actions:
    • Data load
    • Sparse calculations e.g. AGG or SparseMember = X * X/X;
    • A sparse calculation is triggered:
      • Where sparse members are on the left of the =
      • Where the formula is within a sparse calc member block e.g. “Budget”(“Sales” = “Sales”->”Actual” * 0.95;) where Scenario is sparse and Measures are dense.
  • Creating blocks can be carried out using the calculation commands SET CREATEBLOCKONEQ, SET CREATENONMISSINGBLK or the calculation function @CREATEBLOCK. It is recommended that if these settings are required that they are used very sparingly and within a tight FIX statement. Test to see if it is possible to avoid the use of these statements by changing the type of calculation being performed.
  • Block creation is a design related topic. Where there is an issue, it is important to prove that this is a block creation issue before using these calculation commands or calculation function. Submit a 0 into the target block and re-run the calc to prove a block creation issue.
12. Aggregations
  • A sparse dimension aggregation should be ordered starting with the dimension that creates the fewest blocks to the one that creates the most blocks in order to keep the number of blocks as low as possible for as long as possible.
  • In Planning/PBCS models, end user business rules should not aggregate entire sparse dimensions
  • In Planning/PBCS models, any aggregations required for reporting only should be moved to the ASO Plan Type.
  • n Planning/PBCS models, only aggregate data that is required for the Planning approval process.
  • AGG vs CALC DIM Calculation Commands
    • CALC DIM will execute any member formula
    • CALC DIM will aggregate dense or sparse dimensions.
    • AGG performs aggregations based on outline structure.
    • AGG does NOT execute member formula.
    • AGG will only aggregate sparse dimensions.
    • Test both AGG and CALC DIM as performance can differ depending on levels of aggregation involved in the calculation.
  • Exclude dimensions with dynamic calc on upper levels from all aggregations.
  • Only aggregate data that is required.
  • For Planning/PBCS models i.e. multi-user applications with potential for rules running concurrently, it is best practice for end user business rules to be run in serial mode.
  • Only use SET CALCPARALLEL around full sparse dimension aggregations in batch calculations.
  • Parallel calculation is not recommended on small scripts (for example, less than 10 or 20 seconds) as the overhead of creating parallelism may outweigh the benefit.
  • When used, always test SET CALCPARALLEL to ensure that it does give a benefit. Sometimes serial calculation or calculations with lower levels of parallelism can give better results.
  • Test to determine if FIXPARALLEL would provide better results than SET CALCPARALLEL? Use Calc Manager debug mode to view logs to review.
  • Always consider user concurrency when using SET CALCPARALLEL or FIXPARALLEL.
  • For more information on FIXPARALLEL, please refer to the documentation: E66975_01/doc.1221/essbase_tech_ref/fixparallel.html
14. Debug Methodology for Developing Business Rules
  • Always create a unit test i.e. a small subset of data where you know the source data and the expected results and can easily follow the calculation through manually for this set of data.
  • Always use Calc Manager debug mode, or application logs, to view calculation entries to help with any debug during development.
  • Ensure all data that is required is present (e.g. if a total is required for allocations) and has been pre-calculated prior to the calculation taking place.
  • If the script is long, start debug at the top of the script and work down. An issue further up a script may be creating an issue further down. Debug section by section to ensure that all data is created correctly. Check that later sections of script do not overwrite earlier sections etc.
  • Use debug mode (or application log) to ensure that each section of script is calculated sequentially where required.
  • Always clear data and reload (i.e. do not use a clear script) when testing any business rule in order to ensure that all blocks are created successfully. 
  • Always test re-running a script to ensure that the syntax creates correct results for input data and/or populated data.
  • Always test all data results with more than one data set.
  • Where user input may change data sets e.g. allocations, also test data changing from #missing to a value and from a value to #missing, to ensure that previous calculated results are removed (if required) in second and subsequent runse.g. if a user inputs against Product A,B, C in their first calculation run and then Product B, C, D (and not A) in their second, is the allocation result for Product A correctly removed in the second run.

Thursday, July 16, 2015

FDMEE - Import Mappings

Because FDM Classic will no longer be available in version, more and more clients would prefer to implement FDMEE even they are using version Although FDMEE is very similar with FDM Classic in concept, they still have some small differences. For example, FDMEE is an entire web-based application, it doesn't need the FDM client like Workbench. Today, I will explain some minor differences in the mapping tables import step.

FDMEE is integrated into EPM workspace platform already, but it is called "Data Management" in this version ( We can locate to Data Load Mapping in the Workflow tab, and you can find all the existing Account mappings here.

In the Import drop down box, you can find the mapping table import Excel template can be downloaded here. Click "Download Excel Template"  to download it.

After the download, you can open the file as below. Actually, it is quite similar with the MapLoader in our FDM Classic version.

You can find there are two rows hidden in the MapLoader file, which stands for the table name and field names of RDBS table. Same as before.

And also you can find the "upsMap" in the Name box, it will cover the range for the import.

But you can find there is one more row (Row 6) is covered in the upsMap range like a header definition, which is not covered in the FDM Classic version. It is very important for FDMEE mapping table import, we must follow this rule to insert the header row before the table name's row (Row 7, tDataMap) in the upsMap name definition, even it is a blank one.

As I said in another blog post, if you don't like to use the MapLoader, you can simplify it to your own custom import Excels, just follow the rules above. For example, you can custom the Excel to this.

Remember to define the upsMap Name to cover all the related information. (Header, RDBS table information, and mapping table records.)

The first column PartitionKey is a unique key for each location, you can find it in the Location Setup page.

You can hide the RDBS table information rows, and then save the file.

The other difference between FDMEE and FDM Classic is that you can't load your local PC's file to maintain the mapping tables directly in FDMEE. Every time you change the mapping table import Excel, you need to upload it to the FDMEE target application's root folder in your FDMEE server first, and then you can import it to update the mapping tables.

You can find where is your target application's root folder in the Application Settings' page.

Now you can import the mapping table from Excel now, click "Import From Excel" as below.

You can find the Excel file you just copy it to the target application's folder.Click OK to continue. (Or you can Upload your local file here by clicking "Upload" button.)

Select "Merge" Import Mode and "Validate" to continue.

Then you can find the file imported successfully.

You can find your Account mapping table is updated as below.

Thursday, April 16, 2015

HFM - Calculation Manager

Calculation Manager and load rule directly in HFM are two methods for HFM rules management. Both of them can work for EPMA and classic HFM. Now today I start to explore the calculation manager usage for HFM.

Open the Calculation Manager and you will find each HFM application contains the folders which are almost the same with HFM's standard rule types.

Expand the Calculate one, we can create a new rule here.

Input the rule's name, and then click OK. Actually it is a sub routine of the main calculate procedure.

The designer mode is a graphical one, you can drag and drop the objects such as Formula and Condition in it.

For example, we can drag a Condition object and drop in the designer.

Click the Add Condition button.

Then we can select a Function here.

For example, we can select the @Value.Currency function here, click OK to continue.

Select a Member for the Value.

Select the member <Entity Currency> in Value Dimension, and then click OK to continue.

Click Add to add this Metadata Condition.

Then you can find the condition is added as below, click OK to continue.

Then it will return to the designer mode, click Save to save it first.

The following information shows, click OK to continue.

Then you can switch to Edit Script mode, to see what's happening for the script.

You can find the rule's script is as below.

Switch it back to Designer mode, drag and drop a Formula object to the "Y" condition.

You can find the object is added here, select Member for the Formula. By default, it will generate an HS.Exp expression for the direct member selection.

Select the member for the formula, click OK to continue.

And then you can select the Variable, Member or Function in the right-hand side for the formula, or only input a number in it. Then click Validate and Save.

The following message shows. Click OK to continue.

So a new rule has been created successfully. Let's move on to create a new ruleset.

Ruleset is the main rule type of the HFM rule, depends on where you create the ruleset it will return to the corresponding rule type. In this case, we create the ruleset under the Calculation folder, so no matter which name we named it, it will return to Sub Calculate() in the HFM rule at the end.

Then you can drag the rule created before and drop it under the new ruleset. Click Save to continue.

Return to the System View of the Calculation Manager. Switch to Deployment View as below.

Expand the "To be Deployed" items, you can find only rulesets can be checked the Deployable CheckBox. Check it on for the Calculation one. (It is a very important step to make the ruleset as deployable.) By default, all the rulesets are not deployed and not validated.

Right-click the HFM application, then you can Validate all the rules at the same time.

Right-click the HFM application again, then we can deploy all the rules this time.

The following message shows, means the deployment is success. Also, you can deploy the rules in the EPMA application library if it is an EMPA application.

Then let's open the HFM application to double check the deployment. Click Extract Rules in the Extract Application Elements page.

The following messages shows, click OK to continue.

Click Download to download the HFM rule.

Then you can open the rule in a text editor. You can find the ruleset is a main rule type and the rule is a sub routine which can be called by the ruleset/main rule type.


  • Rulesets are corresponding to the main standard rule types of HFM (Calculate, Consolidate, Dynamic, Input, NoInput, Translate...)
  • Rules are the sub routines which can be called by the rulesets
  • Mark the rulesets as Deployable, make sure only one ruleset is deployable for each main rule types