Jedox features flexible cube layout, which means that the layout of a cube can be changed after the cube has been created, without having to rebuild the cube from scratch. Dimensions can be added, removed, and re-ordered. Existing data in the cube will be preserved accordingly, depending on the operation performed by the user.
To change the layout of a cube:
- In Modeler, click Open Cubes for the desired database.
- Select the cube you want to change to open the cube properties. Click on the Change layout... button below the table of dimensions.
- The Edit cube dialog shows all dimensions that are currently used in the cube on the right side, and the dimensions that are not used on the left. The layout can be manipulated by selecting a dimension and then using the arrow buttons to add, remove, or reorder dimensions.
Reordering dimensions is straightforward. You simply move dimensions up or down in the list on the right side. See the section Flexible Cube Layout (below) for details on the impact of reordering dimensions in reports.
Adding and removing dimensions
When adding or removing dimensions, OLAP has to manipulate existing data in the cube. If a dimension is to be added to a cube, you must first define a default write element for this dimension so that OLAP can write the existing cube data to this element. Similarly, if a dimension is to be removed from a cube, you must first define a default read element for this dimension so that the values that are read from this element can then be reserved.
"Create new cube" option
Reports can be designed with functions that return correct values even after changing cube layouts. If a report is not designed with such functions, you can define the changed cube layout in a new cube and leave a read-only version of the original cube in place.
First, make the desired changes to the cube layout in the Edit cube dialog and select the option Create new cube:
OLAP then creates a new cube with the chosen dimension layout and copies data from the existing cube accordingly.
- Any rules that existed in the original cube migrate to the new cube.
- All values are cleared from the original cube and a single new rule, valid for all cells, is created to read data from the new cube.
- The existing cube is set as read-only; it will not be possible to enter values in it anymore. However, all existing reports reading data from the cube will continue to work. Using the automatically generated rule, they will return the data stored in the new cube.
How are PALO functions affected?
Functions that fetch or set cell values in cubes (such as PALO.DATA, PALO.DATAC, PALO.DATAV, but also PALO.SETDATA etc.) depend on the order of their arguments (or element coordinates) matching with the order of dimensions in a cube. Thus, if the layout of a cube is changed, the order of arguments in existing data formulas will not match anymore. This can be handled in a variety of ways when designing or adapting reports.
If a dimension is added at the last position in a cube, and a default read element is defined on this dimension, then existing PALO.DATA formulas (which do not yet include an element coordinate for the new dimension) will return the data from this default read element. If an input is made on this cube (or a SETDATA formula is executed), the data will be written on the defined default write element.
However, if the order of dimensions in the cube is changed, or dimensions are added in positions other than the last, or if dimensions are removed, then existing PALO.DATA formulas will return an error ("No default read element specified"). In this case, the formulas have to be adjusted for the new dimension layout in the cube.
PALO.DATA functions can be flexible to future changes in the cube layout by using the PALO.EL() function for each of the coordinate arguments. PALO.EL() expects two parameters as input: the first is the name of the dimension; and the second is either an element name or (for use in PALO.DATAV) an array of element names. Note that the PALO.EL() function must be used for all coordinate arguments; simple element coordinate syntax cannot be used in combination with PALO.EL().
When using the PALO.EL() formulas, the order of the coordinates does not matter. The dimension name is used to look up the specified elements, regardless of the dimension layout (order) in the cube. All hidden dimensions can be omitted, but if omitted dimensions don't have a default write element specified, writeback won't be possible.
An empty value as argument for one of PALO.DATA/DATAC/DATAV function coordinates is allowed for any dimension that has a single element in the view.
How are KPI cards in a Canvas report affected?
A KPI card detects if the layout of the cube from which its data originates changes and attempts to retain its definition as much as possible. Changing the order of the dimensions in a cube has no effect on a KPI card. An added dimension will be displayed on the “Data” tab of the KPI card the next time it is edited. If a dimension is removed from a cube, the data displayed by the KPI card will not change as long as the previously selected element of the dimension that was removed is identical to the default read element used for this dimension. However, a KPI card cannot usually process multiple changes to the cube layout at the same time (e.g. if a dimension is added to a cube and then another of the previously existing dimensions is moved to a position behind the added dimension).
How is Rule syntax affected?
The “cell value lookup” syntax in OLAP rules (expressions like [‘2019’,’Budget’]) is independent of the order or number of dimensions in the cube. The only prerequisite is that the named dimensions/elements themselves actually exist.
For system cubes, the rule engine supports additional, unnamed dimensions at the end of the cube in PALO.DATA and PALO.MARKER expressions. For example, a PALO.DATA rule formula pointing to an attribute cube is not affected by attribute cube's additional third dimension (the #_LANGUAGE dimension, automatically added for internationalization of databases) and does not have to be adjusted. Rules pointing to attribute cubes will return data from the default language element.
Dynamic cell reference
In Jedox, rules can be written with dynamic cell reference (DCR), which shortens and simplifies the use of PALO.DATA and provides support for flexible cube layout.
DCR requires a cube name to be specified in single quotes before square brackets. It can be empty to reference the "self" cube. This name specification is important to distinguish between old intracube cell references, which have many limitations and special STET representation ( STET() can be written as []). Each item MUST specify the source dimension name for the source cube in single quotes, followed by a colon and either the source element in single quotes or general string expression that evaluates an element name.
Benefits of DCR are:
- The dimension can be found automatically. It does not have to be specified.
- The default read element is used if a dimension is added to the source cube.
- The order of parameters is not important
For example, the rule below shows the syntax without DCR:
['Measures':'Turnover'] = PALO.DATA("Demo","Sales",!'Products',!'Regions',!'Months',!'Years',!'Versions',"Units")*20 The same rule with DCR syntax is much more efficient:
['Measures':'Turnover'] = 'Demo':'Sales'['Measures':'Units']*20 Dynamic cell reference is supported by the Rule Editor.
Updated February 7, 2025