PALO.DATA(): Why are there Different Functions?

Each PALO.DATA() function is a connection to one unique cube cell. All PALO.DATA functions contain the same arguments in the same order:

PALO.DATA([connection],[cube name],[element of the first dimension],[element of the second dimension],[element of the third dimension])

  • The first argument of the function defines the connection and the database.
  • The second argument defines the cube.
  • The remaining arguments are the cell coordinates.

This connection between the server (where the values in the cubes are stored) and the front end (e.g. Excel, Jedox spreadsheet) enables Jedox to read and edit the values. In the context of database views, all functions are processed in the same way on the server. However, in custom-designed reports and spreadsheets, each function has a unique behavior. The differences between the PALO.DATA functions are listed below.

Each spreadsheet cell, and in conjunction, each OLAP database cell, is calculated separately. This process is slower, but the calculated value can be passed to other OLAP functions.

A cluster is built in adjacent spreadsheet cells, and all those functions are calculated in one big request to the server. Therefore, this function is faster than PALO.DATA. However, the results of this function shouldn’t be used for further calculations, as this can lead to incorrect results.

PALO.DATAX (only available in Jedox Excel Add-In)
Returns the value of the described element from the cube if the corresponding cell is on the currently active work sheet.This speeds up the computation by avoiding a recalculation of the whole workbook. If, however, you switch to a different worksheet and later come back to the worksheet containing the PALO.DATAX formulas, you may have to manually trigger a re-computation of the worksheet.

Returns the value of a data cell from a cube when you use an Excel array formula. This function works even faster than PALO.DATAC,  because an array formula is generated for the complete area. Use of PALO.DATAV is only possible in connected cell areas. A maximum of two arrays can be used in PALO.DATAV.

This function allows only reading access, no writing back. As an array function, it controls several cells simultaneously.

Writes the value into the cell specified through the coordinates. Set Splash to “True” if you want to write in a consolidated element. Otherwise, set it to “False”. The parameter “Value” can be a number or the reference to a spreadsheet cell with a number, e.g., A1. Alternatively, it can be a formula.

For more information on Jedox functions, see Jedox PALO/OLAP Functions.