Jedox Planning Assistant: Overview

image_pdfimage_print

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 pre-defined ratios. It also enables controlled writeback, for when you don’t want data to land in your cube immediately, and other steps, such as using named ranges. 

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.

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.

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 the 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. 

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. That means that similar elements must have consecutive numbers as IDs. For example, to predict months:

ID name
2 2018-01
3 2018-02
3 2018-03

.

.

.

.

.

.

12 2018-12
13 2019-01
14 2019-02
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)
ID name
2600 2018
2601 2019
2062 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 rebuilt.

Important: changing element IDs can cause data loss. The best way is to:

  • export all base values (without rule calculation) of cubes which contain the Month dimension
  • rebuild the Month dimension (so the element IDs are in the right increasing order again)
  • import all values
image_pdfimage_print