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.
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 In-Memory DB 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, existing data must be read from some element in this dimension, and data for that element must be distributed across the other dimensions as it was.
To achieve this, Jedox uses default element settings for dimensions. When a dimension is added to a cube, a Default Write Element must be defined for this dimension. Existing cube data will then be assigned to this element. If a dimension is removed from a cube, the dimension must 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 In-Memory DB server will then be those of “Total Year”.
Reports can 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 In-Memory DB server creates a new cube with the chosen dimension layout and then copies data from the existing cube accordingly. Any rules that existed in the original cube migrate to the new cube. Additionally, 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. Finally, 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 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.
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.