Splashing: Data Entry on Consolidated Cells

image_pdfimage_print

Consolidated cells don’t hold a single, physically stored value, but instead, are calculated in memory as a result of the underlying base element data. You can enter numeric values into consolidated cells only by using the specific syntax available. This prevents you from unintentionally entering values into a cell containing consolidated data, thereby corrupting the data. After all, consolidated data values are the sum of base element data values.

However, to indicate that you are entering a value intentionally, with the purpose of splitting the value among the subsequent base elements, you can enter a command (such as #, !, “copy”, and “like”) followed by a value. This process of entering data into consolidated cells is called splashing. Jedox simplifies splashing with the Planning Assistant.

The screenshot below shows cells from the cube Market, a newly created and empty cube, with the exception of the entry in C7 (175):

There are three months as column titles and different products as row titles.

Although this looks like a regular view, the following message appears when you try to enter a number into a cell (e.g. D7):

The reason for this message is that these are consolidated cells, which means they are not only defined by one element of the dimension Months and one of the dimension Products; they are also influenced by the header dimensions in the cell range D3:G3. To write into a consolidated cell, you must use Splashing parameters and commands.

Nevertheless, by splashing values into such cells, the values can be delivered to the underlying base cells without being manually entered there.

Common error messages

When using splashing, the following errors can occur, depending on the context:

Error in Excel Add-in Error in Jedox Web Reason
Cannot write into consolidated cell Cell path is consolidated and no splashing parameter has been used You entered a numeric value without using the correct syntax (#, !, copy, like).
Wrong argument received. Function received a wrong argument. (Error -32) Function Received a wrong argument. You included other values that are not numbers (such as alphabetic characters) on a numeric cell.
Insufficient rights Insufficient access rights You don’t have the required rights to perform a splash.
Held cell(s): writeback not possible, active hold detected, hold ID = 1 (Error 5025) Writeback not possible, active hold detected, hold ID = 0 The cell input is blocked by a cell hold.
Wrong user for locked area; Cube area is locked by user: admin (Error 5014) Current area is locked by user:admin The cell input is blocked by an undo lock.
Splashing is not possible : sum of weights is 0.0, cannot splash (Error 5012) Sum of weights is 0.0, cannot splash You are trying to perform a splash on a cell where the sum of the element weights is 0.0 and all base cells currently contain the same value, for example 0.
Splashing is not possible: too many cells (Error 5012) Too many cells You are trying to perform a splash on a number of cells larger than the maximum number defined by the splash-limit parameter in the palo.ini configuration file.
Invalid copy value: like/predict is not allowed when the source cell is 0 (Error 5007) Like/predict is not allowed when the source cell is 0 You cannot use like or predict when the value of the source cell is 0.
Copy operation not possible : cannot copy from numeric path to string path or vice versa (Error 5020) Cannot copy from numeric path to string path or vice versa You cannot use the Copy splashing command to perform a splash from numeric paths to string paths or from string paths to numeric paths.
Internal error : cannot copy between overlapping areas (Error 2) Cannot copy between overlapping areas You cannot use this splashing command if any of the elements in the specified source area is also in the specified target area.
Element <name> not unique! Please specify dimension by prefixing the element with dimension name followed by colon; e.g. Months:January Non-unique element name used. Please try using dimension name prefix. If the name of the element exists in multiple dimensions, you must use the dimension prefix when performing a copy command.
Goalseek error: no non-empty source-target pair found (Error 5018) Goalseek error: no non-empty source-target pair found The pairs of base cells in the source and target areas of a Transfer operation must include at least one pair of cells where none of the values are 0 or empty.

Other error messages can be custom generated by the SVS scripts, under the sep_error function.

Important: Back up your database regularly. Using these splashing techniques allows you to play with the data in very useful ways. For example, you can check what would happen if you changed the data by x%?. But data splashing can result in data corruption, as when you no longer know the values of your original data and it cannot be retrieved again. There is a simple solution: create a backup of the database before you play with your data.

image_pdfimage_print