Fields of the dataset
What they are¶
The dataset fields identify the data managed by the dataset. They can be of the following types.
| Field type | Description |
|---|---|
| Dimensional fields | Values of the dimensions available for the dataset. They are always synchronised with the dimension to which they refer and therefore an automatic mapping of the information on the two layers, Analytical and Financial, is created. |
| Custom fields | Fields defined directly in the dataset. They generally have headers and allow you to insert values according to the supported field types: - text - number - comments and attachments - date |
Text field characteristics¶
By default, text fields have a limit of 255 characters. Regardless of the set length, the maximum supported length in any case depends on the vendor database in use, the personal data protection settings and the encoded property.
The limits set according to the type of database are listed below:
| Vendor DB | Maximum length |
|---|---|
| MS SQL Server | 8 000 |
| Oracle | 4 000 |
| PostgreSQL | 10 485 760 |
| SAP Hana | 5 000 |
The limit on the number of characters may be lower in the presence of non-ASCII characters and based on the character set configured in the database. If the Encoding and/or Sensitive data protection options are active, the permitted number of characters may be reduced to one quarter. For example, a limit of 4000 characters can be reduced to 1000, or even less if both options are active. By activating these options for a second time, the permitted number of characters for the field is reduced.
If the dataset contains data, the limit can only be increased.
If the maximum supported length is less than what is inserted, the system shows a warning message.
IMPORTANT: if the dataset is used in a DTP, the user must pay attention to how many large sized text fields this contains. These reduce performance.
Rich Text Field¶
A rich text field does not contain the actual text, but a preview of a document in txt format, created in the programme's default endpoint. The dataset field contains JSON text for the decoding of this file.
Furthermore, this field cannot be set with Personal Data Protection, Size, or Dictionaries. Formatted fields are not visible in Partitions.
If the field type is once again changed into Simple, the JSON document will remain orphan. Orphan documents can be cleaned up using a special utility.
If the text field is configured as Formatted, the length is set to 4000, and cannot be changed.
IMPORTANT: for the field to be correctly formatted, an endpoint for rich text must be set. Otherwise an error will occur.
Number field characteristics¶
By default, number fields have a total limit of 38 figures, of which a maximum of 9 may be decimal digits. The number of decimals can be increased at the expense of whole figures included in the number.
If the Dataset contains data, neither the permitted length nor the permitted number of decimal digits can be changed.
Number fields allow you to decide how to aggregate the data on the scenario/period dimension used by the reporting engine in multidimensional matrices.
- Sum: (default) the data related to the scenarios/periods identified by the chosen period length are added together.
- Last: only the data related to the chosen scenario/period are considered, regardless of the defined period length.
IMPORTANT: in reporting, if the dataset contains the Account dimension, the aggregation logic for the data on the Scenario/Period dimension is deduced from the nature and type of the account, not from the measure. For example, if the measure specifies a "Last" type logic, but the account in use is of a Profit & Loss nature with "Normal" type, the applied aggregation logic is the "Sum".
For number fields, the conversion rate to be applied can be defined.
- Average FX rate: the cumulated amount is the result of the conversion at the "average FX rate" defined in the FX rates table.
- Period average FX rate: the amount is the result of the conversion of the period at the period average FX rate defined in the FX rates table.
- Final FX rate: the amounts are the result of the conversion at the "final FX rate" defined in the FX rates table. It is typically used for the Balance Sheet, Other stocks and details accounts, but not for the Net Equity accounts which are normally converted at the historical FX rate, not available on Analytical Workspaces.
- None: the default value is used for accounts not subject to the currency conversion process (e.g. number of employees, volume of sales, etc., as they are numbers not amounts of currency). It can be used for Other Variations/Stock accounts and initial balance variation accounts.
Note: when the period length is life-to-date, the on-the-fly FX rate is not supported.
Characteristics of comment and attachment fields¶
When a dataset is in use in a form:
- if the form is of the Line by Line type, a field of 'comments and attachments' type must be added manually in the dataset and then the values can be entered. This type of field can only be used on reporting forms and every dataset can only contain one field of this type.
- if the form is multidimensional, nothing needs to be added to the dataset. Comments and attachments are saved on dedicated database tables
The comments and attachments are never included in workspace data exports.