The Planning Assistant provides many options for entering data when consolidated elements are involved. It is especially useful for creating planning scenarios and entering data distribution along predefined ratios. It also enables controlled write-back, for when you do not want data to land in your cube immediately, and other steps, such as using named ranges.
To open the Planning Assistant in a Jedox spreadsheet: right-click on a cube cell in a Jedox view and choose Planning Assistant from the context menu, as shown below.
To open the Planning Assistant in Jedox Excel Add-in: select a cube cell in a Jedox view and then click on the Planning Assistant button in the Jedox Ribbon, as shown in the screenshot below.
The Planning Assistant offers several ways to manipulate your data. These options are described below the screenshot.
| Planning Type | Description |
| Splashing | Distributes a user-defined value in a consolidated cell across all the base cells in the aggregation. |
| Copy | Copies the values from one cube slice, including their distribution on the base level, to another cube slice. |
| Like | Applies a user-defined value to a cube slice using the distribution values from a different cube slice on the base level. |
| Linear regression | A prediction as linear regression is only possible if multiple elements (calculation base) of exactly one dimension are specified. Also, the final value of the multiple elements (calculation base) must not be zero. |
| Transfer | Allows transfer of an amount from one element to another within one dimension. The subtotals in all other dimensions will stay fixed on their current values after the transfer. |
| Reallocate | A hierarchy receives the new value X (with the option percentage of new value Y = old value*X%) and computes the values of sibling cells to preserve original values of all ancestor cells. |
| Hold | Holds a value to prevent any further changes to it and its base values, through either direct changes or splashing the value. |
Number of Target cells in Planning Assistant
The Planning Assistant estimates the number of cells involved in a splashing operation and informs you about the potential impact of your upcoming writeback. This information helps you ensure a cube was not accidentally overpopulated, e.g., when a large empty area is unintentionally populated.
The accuracy of the estimated number depends on the splashing type you use. Also, for the splashing commands like “Reallocate,” where the number of the affected cells is difficult to calculate, the theoretical cube slice size is given.
Note that this feature is also available in Excel Add-in.
Optimizing time dimensions
Due to the way time dimensions are built, using any prediction function can return incorrect or inconsistent results. This is because the function reads the dimension by element ID. Therefore, it is important to have well-defined time dimensions for correct calculations.
You must optimize the time dimension by making sure that the distance between element IDs remains constant. This means that similar elements must have consecutive numbers as IDs. For example, to predict months:
| ID | name |
| 1 | 2018-01 |
| 2 | 2018-02 |
| 3 | 2018-03 |
|
. |
. |
| 12 | 2018-12 |
| 13 | 2019-01 |
| 14 | 2019-02 |
To predict quarters:
| ID | name |
| 180 | Qtr 1 (2018) |
| 181 | Qtr 2 (2018) |
| 182 | Qtr 3 (2018) |
| 183 | Qtr 4 (2018) |
| 184 | Qtr 1 (2019) |
|
. |
. |
| 204 | Qtr 4 (2025) |
To predict years:
| ID | name |
| 2600 | 2018 |
| 2601 | 2019 |
| 2602 | 2020 |
|
. |
. |
| 2608 | 2025 |
If you want to use the predict function for the first time on this dimension, or if you want to add elements, the Month dimension has to be rebuilt.
Important: changing element IDs can cause data loss. The best way to change the IDs is by following these steps:
- Export all base values (without rule calculation) of cubes containing the Month dimension.
- Rebuild the Month dimension (so the element IDs are in the right increasing order again).
- Import all values.
Updated June 5, 2023