The dimension table is the technical basis for the the dimension tree in the Web Client (= user interface for collector users).
The most important
The dimension tree has three functions:
- (Bottom) Elements are defined by a key, name, sort, visibility and element type.
Data can be entered only into bottom elements.
- Hierarchy nodes are important
(1) for the real-time aggregation of the entered data
(2) to restrict user access (row-level-security RLS)
The Hierarchy fields ("Level 1 - 10") generate the dimension tree in the Web Client for the collector users. Hierarchy Level 1 is the top element of the tree, Hierarchy Level 2 the second level and so on. On the last level you will always find the bottom elements ("Level 11"). You can define symmetric as well as asymmetric hierarchies, e.g. the tree can have variable depth. Please make sure that the hierarchy definition has no "holes", i.e. no blanks between 2 defined levels of an element.
- The sorting of the whole tree is determined by the SORT field
The SORT field determines not only the sorting of the bottom level but also the sorting of all hierarchy levels. Get more details further down.
- Attributes can be used to add master data to your bottom elements. They can be used e.g. via dynamic tables in your workbook or via the ODATA feed in connected (Business Intelligence) systems. For every bottom element 40 alphanumeric, 10 numeric and 10 date attributes can be defined.
We strongly recommend to create the dimension tree by uploading the dimension table from an Excel file. The Upload file must fulfil the technical requirements for XLSX upload files.
You can see the result of your dimension tree definition later in the "Preview" function of the Workbook definition.
How to Upload the dimension table
In order to get a template file, start with a download of the existing (default) dimension table as an Excel file:
This file gives you a template with the correct column titles (in row 1) ...
... which you can use for your own individual dimension table definitions:
Before you upload the file with the new definitions, you usually have to delete the existing records:
Now you can upload your individual XLSX dimension table:
Import successfully completed:
The Upload file must fulfil the technical requirements for XLSX upload files. Please not that the element type must be defined with the UID and not with the NAME in the import file.
How to sort the dimension tree
The SORT field works as follows:
- The SORT field determines of course the sorting of the bottom level elements in the dimension tree
- Furthermore, the lowest SORT number of an hierarchy node determines the sorting of the elements in the individual hierarchy levels
- You can fill the SORT field also with text but we strongly recommend to use the SORT field only with numbers. Alphanumeric text will be converted into numbers what means that only the left part of the string until the first non-numeric character is used as sort property.
- If you leave the SORT field empty or if there are duplicate numbers in the SORT field then the sorting order results from the technical order of the records within the dimension table (which can be random).
You can see the result of your dimension tree definition later in the "Preview" function of the Workbook definition: