Editing Cubes and Stores

Opening the Cubes panel

Click on the "Cubes" tabulator in the configuration editor to open the cube panel. If all dimensions are already loaded, this will immediately display the cube editor.

Otherwise, a "Loading..." message will be displayed until all dimensions are loaded. If no error occurs, the panel is displayed - otherwise an error message is shown and you must return to the dimension editor and correct the errors before you can edit the cubes.

The editor show all cubes and their dimension- and fact-mappings in a matrix: Every cube is displayed as a column at the top, all dimensions and facts are displayed as rows at the left. The main area of the matrix is split into two regions: The upper half for dimension mapping and the lower half for fact mappings.

Each cell can be be empty or contain the mapping-symbol. This symbol shows if at least on mapping exists in the cube for the dimension or fact in that row. This gives you a short overview of all cubes and which fact they deliver for what dimensions.

Editing Cubes

Creating a new Cube

To create a new cube, use the "Add SQL-Cube" button from the toolbar at the right side. This will instantly add a new cube to cube-matrix.

The new cube uses the first of all existing datasources by default. To use another datasource, you must change the "Database" property of the cube.

Changing the Cube properties

If you want to change to cube properties, you must select its header which represents the cube itself. Click on the header to select it - a blue border around the header indicates a valid selection.

After a cube was selected, its properties are displayed in the property editor and can be edited now.

Unfolding and folding Cubes

Initially, all cubes are folded and their header and mappings are displayed as a small column. The cube name is shown vertically and existing mappings (if there is one or more mappings) are indicated with the mapping symbol in the matrix.

Before you can edit a cube, you must unfold it. You can use the context menu of the cube-header and use the "Expand" item. Or double-click on the header or on any mapping-cell (with or without mapping) to unfold the cube.

After the cube was unfolded, all mappings of the cube are displayed in the mapping cells. If there is more than one mapping in a cell, the row becomes higher and the mappings are show in a list.

If you want to fold a cube, you can again use its context menu or double click on its header. To click on a mapping does not work here, because this would only select the mapping.

As an alternative, you can use the "Collapse all" button from the toolbar. This will collapse all cubes in the matrix.

Mapping facts to Cubes

After a cube was created and unfolded, you can map the facts and dimension for which the cube should deliver data.

To map a fact, you must create a new mapping entry in the fact cell. Use the right mouse button over cell to open the context menu of the cell.

For an empty cell, the menu only contains one item: "Add mapping". This items create a new mapping entry for the fact. Because the mapping is empty and does not contain any SQL expression (which is mandatory), it displays an error message at this point of time.

Now you can edit and correct the mapping. Click on the mapping cell to select it (if it is not selected yet) and to display its properties in the property editor. The most important and also mandatory property is "SQL Expression".

Usually, a fact is stored as a simple column in the fact table. In this case, the basic SQL-Expression would be "<fact-table>.<fact-column>" (e.g. "F_SALES.QUANTITY"). But a fact should always deliver aggregated values for the case it queried for a higher aggregation level of one of the dimension. Therefore, you need also to surround the expression with an aggregation function as SUM, MIN, MAX, AVG, COUNT or COUNT( DISTINCT ).

Fact should always be mapped with an aggregation function (unless they are used in list-reports).

You can add the aggregation manually to the expression (e.g. "SUM(F_SALES.QUANTITY)") or use the context menu of the fact mapping after you entered the basic expression.

This menu offers the basic SQL aggregation functions mentioned above. Select one of them to change the expression and to add the function.

You can also use more complex expression for the fact or use other aggregation functions. For example, in the "TUTORIAL" cube, the fact "Amount" is mapped to the calculated expression "SUM( F_SALES.UNIT_PRICE * F_SALES.QUANTITY)".

Mapping facts using drag&drop

The faster and easier way to map facts in to use drag&drop. You can drag columns out of the database explorer and drop them in an empty fact-mapping cell of the cube.

This create a simple mapping for this column. Then, you can add an aggregation using the context menu of the mapping or edit the expression in the property editor.

Mapping dimensions to Cubes

Like facts, dimensions can be bound to SQL expressions inside the cube. But in difference to facts, dimensions usually must be bound multiple times, e.g. for each level of the dimension (because different level usually need to be aggregated by other columns of the fact- or lookup-table).

Creating mapping entries for dimensions works similar as for facts. Use the context menu of an empty dimension cell and use the "Add mapping" item to create a new and entry.

Then you can edit the item and enter the SQL-expression for the mapping. If the mapping is not valid for the whole dimension (which usually is not the case), you must limit it to a part of the dimension. The following limits are possible:

You can limit a mapping to a specific level of the dimension.
You can limit a mapping to keys containing a specific attribute and bind the result of the SQL-Expression to the attribute value instead of the key-IDs.
You can limit a mapping to a single key of the dimension. In this case, no SQL-expression is necessary and only the optional WHERE clause of the binding is used.

The limitation to an attribute is usually used to bind keys with an incompatible id (e.g. product keys have the product name as id, and contain the product-ID as attribute, the product-id from the fact table should be bound to the attribute instead to the key itself).

It is also possible to combine limits and bind an SQL expression to keys with and specific attribute and a specific level.

The easiest way to limit a dimension mapping is to use the context menu of an existing mapping.

There a two different items to limit the mapping: "Only map one level..." to limit the binding to a specific level and "Map with attribute..." to map a specific attribute instead of the key id. Also, there is a item "Map all levels" which removes an existing level limitation.

When using the "Only map level..." item, a new dialog opens which offers all levels of the dimension for selection.

Select a level and press "OK" to limit the binding. Alternatively you could also edit the "Level" property of the binding in the property edit.

If a mapping is limited to a level, the matrix shows the level name in brackets and with a following "=" before the SQL expression.

When using the "Map with attribute..." item, a similar dialog with the list of all unique attribute for the dimension opens:

Select an attribute and press "OK" to limit the binding to a single attribute. You can also edit the binding properties to change or delete an attribute limitation.

After a mapping is limited to an attribute, the matrix will indicate this by write the attribute name with "=" before the SQL expression.

Mapping dimensions with drag&drop

Also for dimension mappings, it is possible to drag columns out of the repository explorer and to drop them in dimension cells. This creates simple mappings for a single column.

When adding more than one mapping to a dimension cell, you must drop the column at the lower or upper border of the cell. A blue border will indicate valid drop positions when dragging a column.

After adding a dimension mapping using drag&drop, you can limit the mapping like any other mapping.

Automatic mapping

For simple dimension which are loaded with SQL-KeyLoaders, the workbench can automatically create the mapping for the cube. A dimension must fulfill the following criteria to be automatically mappable:

The dimension must be loaded with SQL-KeyLoaders only and every loader must load its data from the same database the cube does.
Every level of the dimension must own a level-name, because the automatic mapping only works on levels (only levels with names can be mapped in the cube editor).
There must not be more than one SQL-KeyLoader per level inside the dimension definition.

The automatic mapping uses all SQL-Expression from all SQL-KeyLoaders the dimension contains as mapping-expression. Each level from the dimension becomes one mapping in the cube.

Open the context menu of the dimension mapping-cell and use the item "Generate mapping automatically..." to create the mapping. If the automatic mapping is possible, the workbench will delete all existing mappings for the dimension and add the new ones. Otherwise, it will raise an error message - e.g. if a level of the dimension has no name.

Note that the automatic mapping generates very simple mappings which are not necessary the most performant solution. Especially the lowest level of dimensions are usually mappable directly within the fact-table, because the fact table references the lowest directly. When mapping the lowest directly, the system can use one table less in the generated SQL statement.

Changing the Cube order

The cube order can be very important and determines - in combination with the match modes of the cubes - where the system loads the data from:

If the match mode is "first" or "exact", the first cube from the left containing a needed fact and mapping at least one (first) or exactly the number of needed dimensions (exact) will be used.
If the match mode is "best", the first cube mapping the fact and the maximum possible number of dimensions will be used. If there is any other cube mapping the same number (but different) of dimensions, the left cube has a higher priority.

Because of this, you should control the order of the cubes and arrange them in for the case that the same facts are mapped in different cubes (if a fact is only mapped in a single cube, their order is without any meaning).

To arrange order you can use the context menus of the cubes or drag them with the mouse.

The context menu can be opened with the right mouse button while holding the mouse over the header of a cube. Depending on the cube position it contains a "Move right", "Move left" or both items. Use this items to shift a cube.

To arrange cubes with drag&drop, grab a cube header and move it to the left or right border of another header. A blue border indicates a valid drop position, if you release the button here the cube will be moved before or behind the target cube.

Deleting existing Cubes

To delete a cube you can use the context menu of a cube or the keyboard.

The context menu can be opened with the right mouse button while holding the mouse cursor of the header of the cube.

It contains a "Delete" item - use this item to remove the cube from the configuration.

Alternatively you can select the cube with a mouse click on its header and use the DELETE key, to also delete the cube.

Editing Stores

Creating a new Store

To add a new store to an existing cube use the context menu of the cube and choose the item "Add store".

Stores are displayed as an additional column nested under the cube. The name of the store is displayed in its header and for each dimension and fact mapped by the cube there is a checkbox right to the mapping.

Changing the Store properties

Like cubes, you can display and edit the store properties by clicking onto its header and selecting it.

The store properties allow to change the store name, its build properties or to mark it as not active. These are the most important properties:

Name: The name of the store.
Cron Pattern: This pattern defines the time schema when a store will be automatically rebuild. By default a store has no pattern and will not be rebuild unless it is triggered manually or a dimension has changed.
Build timeout: The timeout for the store build process in seconds. The default value is 3600 (one hour).

Adding dimensions to a Store

After you created a new and empty store you will have to decide which dimensions you want the store to contain. Adding a dimension is very simple, just click onto the empty box next to the dimension mapping (in the column of that store) and a checkbox will appear and signal this dimension as being contained in the store. Alternative you can use the context menu of the empty checkbox, which only contains one item ("Add dimension to store").

After you added a dimension to a store, you can edit its settings for this dimension by selecting the checkbox with a single click. This will display the dimension properties, namely its name and the "Load Mode" of the dimension.

The "Load Mode" defines, if the hierarchy of the dimension will be calculated by aggregating the values bottom up or by querying the database for each single level. Read the "Concepts" chapter to learn more about the load mode. You can also change the mode by using the context menu of the checkbox.

To remove a dimension from a store, you can either double click the checkbox or use the context menu of the checkbox and select the "Remove dimension from store" item.

Adding facts to a Store

Like dimension, facts can be easily added with a single click on the checkbox right to the fact mapping or by using the context menu of the empty checkbox.

To display the properties of the fact, select it with a single click onto the fact. The only property you may want to change is the "Aggregation" of the fact which can also be changed with the context menu of the fact.

The aggregation defines if the fact will be aggregated using the sum, average etc. value from lower dimension levels. The default aggregation for facts is "auto" which tries to automatically determine the aggregation from the bound SQL expression. If this is not possible or if you want to change the aggregation, you can change it from "auto" to one of the desired values "sum", "min", "max", "avg" or "none".

To remove the fact from the store, double click it (like dimensions) or use the context menu of the fact checkbox.

Changing the Store order

Like cubes, stores have a defined order with its owning cube and the order can be very important. Whenever the system tries to load data from a cube which contains more than one store, it queries the stores from left to right for the data. Therefore you may want to change the store order.

If your cube contains more than one store, you can rearrange them with drag&drop or by using the context menu of a store. Use the items "Move store left" or "Move store right" here.

Deleting existing Stores

To delete a store you can select it and use the DELETE key or open its context menu and use the "Delete store" item.