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, the In-Memory DB (OLAP) 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. Setting default elements can help to automate this process.
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.
Flexible Cube Layout
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.
Rule syntax
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 March 27, 2024