Rule Upgrade Troubleshooting

Prevent error cell references - e.g. empty attributes used in PALO.DATA or DCR (Dynamic Cell Reference). This can lead to invalid or missing recursion or mappings.

Typically, PALO.DATA uses parameter values from:

• Attribute content
• Hierarchy navigation functions – PALO.EPREV, PALO.EOFFSET, PALO.ECHILD, …
• name mapping – e.g. two different time dimensions: Plan_Period and Fact_Period when 2018 from Plan_Period is missing in Fact_period dimension
• String manipulation – e.g. Period "~" concatenated with "_YTD" can be an invalid name

Symptoms

• Non-zero "error" counter in rule calculation statistics
• Warning message in palo.log: "Invalid source for rule xxx dimension nr. ..."
• Debug messages in palo.log "element xxx not found in dimension yyy" - verbose level has to be set to debug
• in the Modeler reports "rule error" for some of the cube rules
• OLAP memory usage is high
• Slow calculation, calculation stack not changing

Solution

• Check consistency of attribute cubes. All cells used as element names should exist and have valid content. Temporarily define a rule for an attribute to validate content that checks for typos and completeness.
  For example, the following rule checks that all attribute values for 'Company Code' are valid element names in the Cost Center dimension.
  

  ['Company Code'] = IF(ISERROR(PALO.EINDEX("","Cost Center",[])),CONCATENATE("invalid value: ",[]),null)

  You can then browse the attribute table and fix all reported cells. Once finished, disable the rule.
• Define a bumper rule for the end of rule recursion, or add IF in the recursive rule to handle termination of the recursion.

Unlike previous versions, Jedox OLAP 7.0 distinguishes between zero and null. Before 7.0, a zero value was interpreted as null. Operations with null results mean nothing; DDE just ignores such values. It doesn't allocate memory to store it and doesn't use CPU to process them. It is the default value of all cube cells after the cube is created and before any value is loaded into it or any rule is defined.

This change means that Jedox 7.0 users can configure a cube to store 0 or use rules to generate 0 at rule calculation time. Before 7.0, numeric literal 0 in rules were processed as null.

Symptoms

• Non-zero "zero result" counter in rule calculation statistics (if 0 is used instead of null)
• Slow calculation
• High OLAP memory usage

Solution

• Search your exported rules for "0", typically found in these sequences: ",0," or ",0)"
• Replace 0 or "" with null wherever possible.
  For example, change
  ['X'] = IF(['X']>0, ['X'], 0)
  to
  ['X'] = IF(['X']>null, ['X'], null)
  or change

  ['X'] = max(['X'],0)
  to
  ['X'] = max(['X'], null)

• If literals 0 or "" are used in equality comparisons as part of a condition within an IF statement, this comparison can be omitted completely. For example:
  ['X'] = IF(PALO.DATA("","#_Months","PrevMonth",!'Months',"~") != "", ['Y'], 0)
  or
  ['X'] = IF(PALO.DATA("","#_Months","PrevMonth",!'Months',"~") != 0, ['Y'], 0)
  or
  ['X'] = IF(PALO.DATA("","#_Months","PrevMonth",!'Months',"~") != null, ['Y'], 0)
  can be changed to:

  ['X'] = IF(PALO.DATA("","#_Months","PrevMonth",!'Months',"~"), ['Y'], null)

Null input should produce null output in expressions, if the business logic permits this. Typically OLAP data is sparse, meaning most of the (theoretical) cells in the cube have the value null, i.e. are not stored. Hence, expressions in rules preserve this sparsity by not "filling" large areas of the cube with rule-calculated values.

Sparsity can be maintained when expressions return null output for null input. For example, the expression (as used in condition of an IF construct)

['a'] == 1

preserves sparsity, whereas the expression

['a'] != 1

does not, as it produces non-null output (value 1 for "true") for null input, because a null value is not equal to 1.

Example:

IF(AND(!'Year'=="2020", !'Month'=="May"), X, Y)

can be changed to

IF(!'Year'=="2020", IF(!'Month'=="May", X, Y), Y) 

to speed up IF rule processing.

Note that this also applies when the source is referenced via PALO.DATA or DCR, e.g. attribute cube data with one "open" / non-restricted dimension.

Sometimes zeros are produced as secondary data, often in cases when a change of account in time was calculated from the opening and closing value. For non-null values, subtraction is evaluated as 0.

Since version 7.0, OLAP is also strictly projecting error values of calculated rules into consolidations. For example, if cell ['Jan', '2018', 'ProductA', 'Actual'] is evaluated as an error, then all consolidated cells that depend on it are also errors. There can be billions of such cells, depending on the number of dimensions and their hierarchies. Previous OLAP versions made calculation faster, but users got no feedback about typos in the rules or inconsistencies in a calculation, such as when there is incomplete attribute data.

Typically, PALO.DATA uses parameter values from:

• attribute content
• hierarchy navigation functions, such as PALO.EPREV, PALO.EOFFSET, and PALO.ECHILD
• name mapping, when there are two different time dimensions and an element is missing from one of them, e.g. Plan_Period and Fact_Period dimensions, when the element 2018 from Plan_Period is missing in the Fact_period dimension
• string manipulation – e.g. period "~" concatenated with "_YTD" can be an invalid name

Symptoms

• Non-zero "zero result" counter in rule calculation statistics (if 0 is used instead of null)
• Slow calculation
• Possibly high memory usage

Solution

• Replace numeric literal 0 with null
• When calculating the difference from two nonzero values, use a non-equality test and return null if test is negative.
  For example, change
  ['diff'] = ['closing'] - ['opening']
  to
  ['diff'] = IF(['closing'] != ['opening'], ['closing'] - ['opening'], null)

When Integrator is used repeatedly to update dimensions, and it deletes elements and creates them again, dimension elements can get fragmented. OLAP cannot hold its optimal structure to access these elements and spends more CPU cycles to find them. In some operations, the slowdown can be tens of percents.

Symptoms

• Highest element ID in a dimension is much higher than lowest element ID (element IDs can be found by clicking on the dimension name in the OLAP server browser).
• Slow calculation/dimension browsing.

Solution

The database can be compacted using the database script feature. In the Jedox Web Modeler, right-click on the database and select "Create Database Script". Give the new script a temporary name and run the script created in the previous step within it. Rename the original database and use its name for the database created in the previous step. It reduced calculation times for several models by 30-50%.

Symptoms

• Red lines in OLAP admin cube analyzer graph, as in the example below:
  

  

• Slow calculation
• High counter in "Plan Nodes statistics" for nodes of type CELLMAP and TRANSFORMATION

Solution

• Make sure that all cubes linked via PALO.DATA formulas use the same order of shared dimensions.

OLAP doesn't yet optimally process expressions that are repeatedly used in one rule, e.g. ['max'] = IF(['A'] > ['B'], ['A'], ['B'])

Symptoms

• Slow calculation

Solution

• Try to use aggregation function when possible.
  For example, change
  ['X'] = IF(['A'] > ['B'], ['A'], ['B'])
  to
  ['X'] = max(['A'], ['B'])
• Move expressions repeated in IF outside of the IF.
  For example, change
  ['X'] = IF(['X'], ['X']*['Y'], ['Y'])
  to
  ['X'] = ['Y'] * IF(['X'], ['X'], 1)

Model rules use comparison operators that include a cell equality test ( ==, >= and <=) with zero, an empty string, or another cell reference.

Symptoms

• The high counter in "Plan Nodes statistics" for nodes of type EQ, LE, GE
• Slow calculation

Solution

• Switch 2^nd and 3^rd IF parameters and exchange comparison operator for one with the opposite meaning (== to !=, >= to <, <= to >)
  For example, change
  ['X'] = IF(['Y'] == 0, ['Z'], -['Z'])
  to
  ['X'] = IF(['Y'], -['Z'], ['Z'])
  
  or change
  ['X'] = IF(PALO.DATA("", "s", "a") >= PALO.DATA("", "s", "b"), ['A'], ['B'])
  to
  ['X'] = IF(PALO.DATA("", "s", "a") < PALO.DATA("", "s", "b"), ['B'], ['A'])

In earlier versions of Jedox, the default cache size was too small - 1,000,000 cell values per cube. In Jedox 2018.x and later, the default cache size is set to 100,000,000 (non-null) cell values per cube.
The value is used for many temporary structures. If set too small, OLAP can use slower modes of algorithms.

Symptoms

• Slow calculation

Solution

• Make sure that palo.ini value cache barrier is at least 100,000,000.

CPU DDE tries to parallelize rules calculation. In many cases, rules are too complex for parallelization and can slow down calculation.

Symptoms

• Slow calculation

Solution

• Try to disable multicore rules calculation with the following line in the palo.ini:
  engine-configuration 1
• If the engine configuration already exists in your setup, make sure the parameter contains 1. It can be added to the end of the parameter string.

For OLAP 5.1, it was recommended to replace the following IF statement

IF(condition, true_expression, false_expression)

with

(condition) * (true_expression) + (1-condition) * (false_expression)

Since Jedox version 7.1, this approach becomes a specific case of the calculated zeros scenario described above. The problem is in the part (1-condition). If the condition is true (1), result of this is 0, and when multiplied with non-null (false_expression), it results in a calculated zero for potentially large number of cells.

Symptoms

• Non-zero "zero result" counter in rule calculation statistics (if 0 is used instead of null)
• Slow calculation
• Possibly high memory usage

Solution

• Switch rule back to use IF statement. It is fully supported by the latest DDEs.

OLAP should be able to process recursions deeper than some hundreds of iterations. Once the system runs out of resources, it returns the error ERROR_RULE_CALCULATION_TOO_DEEP.

Symptoms

• Some rule time statistics are bigger than total engine rules calculation time (deeper recursion iterations are added multiple times)
• Slow calculation
• Sometimes high memory consumption

Solution

• If possible, use consolidations instead of recursion, e.g. _YTD hierarchy in Month dimension.

The function ISNULL has been effectively replaced by EXIST(x) and will be deprecated in a future version. By nature, ISNULL doesn't allow OLAP to use DDE efectively. Therefore, we recommend changing rules such as

= IF ( ISNULL( ['A'], ['B'], ['A'] )
to
= IF ( EXIST( ['A'], ['A'], ['B'] )

This form produces the same results and processes the sparse data of OLAP cubes more effectively.

DDE can handle most rule types. The following functions are natively supported by DDE of latest OLAP:

ABS, ADD, AND, AVERAGE, CEILING, CHAR, CLEAN, CODE, CONCATENATE, CONTINUE, DEL, DIV, EQ, ES, EVEN, EXIST, EXP, FACT, FLOOR, GE, GT, IF, INT, ISERROR, LE, LEFT, LEN, LN, LOG, LOWER, LOG10, LT, MAX, MEDIAN, MID, MIN, MOD, MUL, NE, NOT, NOW, ODD, OR, PALO.DATA, PALO.MARKER, PI, POWER, PROPER, QUOTIENT, REPT, RIGHT, ROUND, SIGN, SQRT, STET, STR, SUM, TRIM , TRUNC, UPPER, VALUE, VALUEDATE, WEEKDAY

Functions without native DDE support of latest OLAP:

ACOS, ASIN, ATAN, COS, COUNT, DATE, DATEFORMAT, DATEVALUE, EXACT, FIRST, ISNULL, LAST, PALO.CUBEDIMENSION, PALO.ECHILD, PALO.ECHILDCOUNT, PALO.ECOUNT, PALO.EFIRST, PALO.EINDENT, PALO.EINDEX, PALO.EISCHILD, PALO.ELEVEL, PALO.ENAME, PALO.ENEXT, PALO.EOFFSET, PALO.EPARENT, PALO.EPARENTCOUNT, PALO.EPREV, PALO.ESIBLING, PALO.ETOPLEVEL, PALO.ETYPE, PALO.EWEIGHT, PERCENTILE, RAND, RANDBETWEEN, REPLACE, SEARCH, SIN, SUBSTITUTE, TAN

The unsupported functions listed above can cause the engine to switch to the Structure/Marker Driven Engine – and potential slowdown - only if the following two conditions apply:

• the function is NOT used within the 3^rd or further PALO.DATA/PALO.MARKER parameter and when the parameter expression depends on one or fewer variables (!'dimension_name')
• the rule is being evaluated for more than 10.000 cells – typically B: rules

Rule examples :

[…] = PALO.DATA("","",CONCATENATE(!'Year', PALO.ENEXT("","Month",!'Month')))
Not DDE-supported: function is used within the 3rd parameter but uses 2 variables (!'Year' and !'Month')

[…] = PALO.DATA("","",PALO.ENEXT("","Month",!'Month'))
DDE supported: function used in 3rd parameter, uses only variable (!'Month')

[…] = EXACT(!'Month',!'Year')
DDE supported: the size of the Month dimension times  the size of  the Year dimension is less than 10,000 cells.

Symptoms

• Large evaluation counter of some rule(s), typically including large zero or null result counter in calculation statistics (if 0 is used instead of null)

Solution

• Try to eliminate unsupported DDE function usage if possible
• Create marker for such a rule

Symptoms

• Results that do not match your expectations, typically resulting in null (0) where you expect some number.
• Markers are defined in the rule calculating the cell or some source cell.

Solution

• The Trace Cell Value feature lets you recursively trace the whole cell calculation:

If the Cell Tracer result is different from what you can see in the Excel report, and your model uses markers, make sure these are correctly defined. In case markers are used, and the rule is not fully DDE compatible, the report could show 0 as a result but Cell Tracer shows a non-zero value. Of course, if other cube cells depend on this problematic rule, the error is propagated using the logic of dependent rules.

Example of rules with incomplete markers:

[] = if (['a'], ['b'], [['c']]) // [['b']] should be used.

[] = ['a'] +1 @ [['b']] // rule always has a nonzero result, but external marker [['b']] can cause that MDE skips cells for which ['b'] is null and returns null as a result.