Creation of dimensions, data tables and extended amount types
Written by Bettina Schmoll
Updated at May 12th, 2025
The most fundamental elements of any BI system are the fact tables and the dimensions. The fact tables contain the data to be prepared and analyzed. The dimensions, on the other hand, are, if you can call them that, the properties assigned to a data set and based on which the data sets can be evaluated. Without them, the data tables would simply contain numbers that no one could do anything with.
Let’s take the following as an example:
The data contains a record with the value 12.76.
Without another property, you can't do anything else with this value because you can't assign it anywhere.
This value can now represent 12.76 meters of string that were sold, or 12.76 euros that were earned from the sale of a roll of string, or 12.76 m² of carpet that were purchased.
You see, without further description of what the number 12.76 stands for, there is nothing more to be done with it.
This is where dimensions come in. Each data set can be linked to multiple dimensions, giving each value several properties that further refine its definition.
One of the possible dimensions is the service date. This gives the data record a date and time when it was created.
Another possible dimension is a unique article number. This identifies the article that created this data record.
Now let’s get to the design of the dimensions.
The dimensions are created in the configuration under Dimensions:
A new dimension is created by clicking the " Insert " button in the menu bar. The input mask looks like this:
To begin with, fill in the fields Code, Name and Favorite shown.
The dimension has been created. However, you can't work with it yet, as it must first be created in the BI4 Controlling service.
For this purpose, there is a button in the menu bar called “ Update Cache Configuration ”:
Clicking this button recreates the dimension definitions for the BI4 Controlling service.
For versions updated before January 2019, a restart of the BI4 Controlling service is required. However, the restart can be postponed, as creating a data table also requires a restart.
However, before importing data into a dimension or data table for the first time, the table and dimension definition must be updated in the BI4C service. In versions prior to January 2019, this is done by restarting the service.
For all other versions, simply click on the “ Update cache configuration ” button.
Grouping dimensions is primarily a nesting of dimensions.
An example of this would be the unit and an article.
Since only one base unit can be assigned to an item, nesting is a direct option. This links the item to the unit, eliminating the need to store the unit in the data entry if the item has been specified.
To set up a grouping, select the dimension from the list of dimensions and click “ Configuration ” in the menu bar.
In the "Grouping" tab, enter the dimension to be grouped. In our example, this is the "Customer" dimension:
Once the grouping has been entered, the window can be closed again by clicking on the arrow.
Afterwards, the dimensions must be updated again so that these changes can be used.
If several changes are made, it is sufficient to restart the service after the last change, provided it was updated before January 2020.
With the latest version, a restart is no longer necessary. Simply update the dimensions and reload the data tables.
The data table is used to store the individual values. It is created in the configuration under Data Tables.
A new data table is created by clicking the " Insert " button. The input mask looks like this:
For the initial creation, the fields “Code”, “Description”, the Favorite column and “Storage medium” must be filled in.
Then select the data table and click the Configuration button:
The input mask looks like this:
At least one of the value fields must be filled in here.
The " Code " field is the unique identifier for the data table . Value names such as "Quantity" or "Amount" are entered in the "Name - Value 1" to "Name - Value 7" fields. These also serve as the column headings in the data table's transaction overview. Without this heading, the value will not be displayed in the transaction overview.
If changes are made to the value fields or dimensions, these changes to the data table must also be reflected in the BI4C service. For versions prior to January 2019, a restart is the safest way to update. For recent versions, it is also possible to manually initiate the update in the BI4C service.
To do this, the button “ Reload data ” must be clicked in the data table overview. can be clicked:
This will reload the table definition in the BI4C service, the changed dimension mapping and the data
Before data can be filled into the data table, the system needs to establish a connection between the dimensions and the data. This is done either by entering the dimension into the template dimension or by assigning it directly to a data table.
The difference is that when the dimension is entered into the template dimension, it becomes available in all data tables based on it. However, when entered into a single data table, it becomes available only in that data table and all those that have entered it as the base data table—the data table in which the dimension was entered.
The entry for the template dimension is made in Navision under Departments / Controlling Module / Setup / Basic Setup. There, on the Navigate tab, you'll find the "Template Dimensions" button.
If you click on this, the following window opens:
The new dimension is entered by creating a new row at the bottom of the list. First, a unique value must be entered in the "Key Priority" field. Then, the dimension is entered in the "Filter Type" field. Finally, a "Flexible Dimension Number" must be assigned. This is done by clicking in the field and then selecting "Assign Dimension Index" from the menu. The system will then ask if the unique dimension key is longer than 20 characters. This question helps the system decide whether a short or a long key field is needed in the data table. The system then returns a number for the dimension index accordingly.
When assigning the dimension to a single data table, the procedure is the same as for the template dimension. The difference is that the data table must be edited . Select the data table and click the Dimensions button:
The input window looks like this:
If a dimension index has already been assigned to a dimension in another data table, the same number can also be entered manually into the new table. This is useful because the data tables then contain the same content in the same dimensions. Otherwise, it may happen that in data table A, the article number is in dimension 5, while in data table B, it is in dimension 7.
If the dimension is assigned exclusively via the template dimension, all tables based on this template in the same dimension will automatically have the same content.
! IMPORTANT !
Changing the dimension number is only possible during creation, provided no data record has yet been written to that dimension. Changing it later will inevitably lead to data chaos and loss!
After assigning a new dimension to a data table or creating a new data table, the Controlling Service must be restarted for the changes to take effect and for work to begin. For newer versions, it is sufficient to update the dimensions and reload the changed data table.
The extended amount types make it easier to access the values stored in the data tables in reports. The various filter criteria, such as the data table, the field in the data table, and, if desired, additional filter criteria, such as a financial accounting account or item type, are stored and assigned a name. Then, in the reports, only this amount type needs to be selected, and the system knows where to import the data and which filter settings to use.
For program versions updated after January 2019, there is also a checkbox labeled " Generate Amount Types " on the data table setup page. If this checkbox is checked, the system automatically generates the amount types for this table. These only contain the field and the filter for the corresponding data table.
However, if an additional filter such as an item type or a financial accounting account is required, an extended amount type must be created manually .
This is done in the configuration under Amount Types:
In the table, enter the name of the amount type in the " Amount Type " column. This should be descriptive to make it easier to select the amount types later when creating reports.
You then need to set the data table and other required/desired filters for the amount type under the “ Filter ” button:
You can use the Insert button to create new rows/filters and the Edit button to edit existing filters.
