Flexible Cube Layout


As of Jedox 7.0, user can change the layout of a cube after the cube has been created, without having to rebuild the cube from scratch. The user can add dimensions to the cube, remove dimensions, and/or change the order of dimensions in the cube. 

Existing data in the cube will be preserved accordingly, depending on the operation performed by the user.

Changing the layout of a cube

The layout of a cube can be changed in Modeler by clicking the Change layout button on the cube properties page. The follow dialog will appear:

On the right side of the dialog, all dimensions currently used in the cube are shown. On the left side, all dimensions that exist in the database but are not used in the cube are shown. 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 Formula Support for Flexible Cube Layout (below) for details on the impact of reordering dimensions in reports.

Adding and removing dimensions

When adding or removing dimensions, the OLAP Server has to manipulate existing data in the cube. If a dimension is added, the existing data has to be assigned to an element in the new dimension. Similarly, if a dimension is removed, OLAP has to read existing data from some element in this dimension and keep data for that element distributed across the other dimensions as it was.

To achieve this, new default element settings for dimensions are used. If you add a dimension to a cube, a Default Write Element has to be defined for this dimension. Existing cube data will then be assigned to this element.

If you remove a dimension from a cube, the dimension has to have a defined Default Read Element. The values that are read from this element will then be reserved. For example, if you remove a “Months” dimension from a cube in which “Total Year” was defined as the Default Read Element, the cube values that are stored in the OLAP Server will then be those of “Total Year”.

Compatibility Cubes

Reports can now be designed with formulas that return correct values even after changing cube layouts. However, in cases where reports are not designed in this fashion, it is possible to define the changed cube layout only for a newly created cube, and leave a so-called Compatibility Cube in place.

When setting the “Create New Cube” option in the Cube Layout dialog, the OLAP Server will create a new cube with the chosen dimension layout, and then copy data from the existing cube accordingly. Any rules that exist on the original cube will migrate to the new cube. Additionally, OLAP completely clears all values from the original cube and creates a single new rule in it, valid for all cells, which reads data from the newly created cube. Finally, OLAP also sets the existing cube 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. Via the automatically generated rule, they will now actually return data that’s stored in the new cube.

Formula Support for Flexible Cube Layout

Formulas 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. However, 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.

First of all, 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.

If the order of dimensions in the cube is changed, if dimensions are added in other positions than the last, or if dimensions are removed, however, existing PALO.DATA formulas will now return an error (the exact error message is “No default read element specified”). In this case, the formulas have to be adjusted for the new dimension layout in the cube.

It is however possible as of version 7.0 to create PALO.DATA formulas in a way that is flexible to future changes of the cube layout. For this, the new PALO.EL() function can be used for each of the coordinate arguments. PALO.EL() expects two parameters as input. The first is the name of the dimension; the second is either an element name, or (for use in PALO.DATAV) an array of element names. Note that if you want to use the PALO.EL() formula, it has to be used for all coordinate arguments. You can not mix the old, simple element coordinate syntax with usage of PALO.EL().

When using the PALO.EL() formulas, the order in which coordinates are specified then does not matter anymore. The dimension name is used to look up the specified elements, irregardless of the dimension layout (order) in the cube. Each dimension which defines a default read / write element can be completely omitted.

In case a dimension for which an empty value is passed does not have a default read element defined for the dimension in question, if a PALO.DATA/DATAC/DATAV function receives an empty value as argument for one of its coordinates, it immediately (without a call to the Jedox OLAP Server) returns an empty value. However, if there is a default read element, these formulas will now return the value of that default read element.

Rule syntax

The “cell value lookup” syntax in OLAP rules (expressions like [‘2017’,’Budget’] has already been 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, in Jedox 7.0 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.
However, PALO.DATA rule formulas pointing to other cubes will not continue to work if the dimension layout of those cubes is changed. These rules have to be manually adjusted in this scenario.