Appearance
Tables
Data is stored in tables that consist of dimensions and values. In multidimensional databases, these are often called cubes.
Name and Captions
Each table has a unique name in the database. Up to 10 captions can be added; these do not need to be unique. In this example, the unique name of the table is Sales. Captions 1 to 4 contain Sales translated into several languages.
Table Structure
Each table consists of dimensions and values. Each can be of type number or text. The name of each dimension and value must be unique within the table.
In this example, the Sales table contains five dimensions (Month, Region, KeyAccount, Customer, and Product), four number values (Amount, Turnover, Costs, and Profit), and one text value (Comment).
Dimension
A dimension can only be assigned to a table once. A table can only contain one date dimension and one time dimension. This is necessary because these dimensions are linked and can be converted into each other in queries.
There are two kinds of date/time dimensions: main dimensions denote an absolute date/time range, while minor dimensions denote relative ranges, such as the day within a week. The following tables give some examples.
| Date Dimension | Main/Minor | Example |
|---|---|---|
| Day | Main | 2020-06-30 |
| Week | Main | 2020-27 |
| Month | Main | 2020-06 (Jun) |
| Quarter | Main | 2020-02 (Apr - Jun) |
| HalfYear | Main | 2020-01 (Jan - Jun) |
| Year | Main | 2020 |
| OffsetYear | Main | 2020-2021 |
| DayOfWeek | Minor | 2 (Tue) |
| DayOfMonth | Minor | 30 |
| DayOfYear | Minor | 182 |
| WeekOfYear | Minor | 27 |
| MonthOfYear | Minor | 06 (Jun) |
| QuarterOfYear | Minor | 2 (Apr - Jun) |
| HalfYearOfYear | Minor | 1 (Jan - Jun) |
| Time Dimension | Main/Minor | Example |
|---|---|---|
| Second | Main | 13:30:15 |
| Minute | Main | 13:30 |
| Hour | Main | 13 |
| SecondOfMinute | Minor | 15 |
| SecondOfHour | Minor | 1815 |
| MinuteOfHour | Minor | 30 |
| HalfDay | Minor | 2 (PM) |
Number Values (Doubles)
Number values are stored as type double. Double is a floating-point type used to store real numbers with high precision.
- Approximate positive range: 2.23e-308 .. 1.79e+308
- Significant decimal digits: 15-16
Text Values (Strings)
Text values are stored as type string. A string can store up to 1,073,741,824 characters.
Records
Dimension items and values are combined in records that represent a row in the table. In a record, an item must be specified for each dimension in the table. The combination of items is called a tuple and is unique for each table. The number and text values do not have to be unique.
Example of Sales records:
Table Visual Script
A visual script can be assigned to a table to calculate fields automatically. Whenever a record is changed—either a new record is created or an existing record is updated—the visual script of the table runs for that record.
Table Calculations
Table calculations are predefined visual scripts that can be used when querying data. When combining a table calculation with a directly specified visual script, the table calculation runs last.
Subtables
Tables/cube data can be viewed "rom all sides". Sometimes, a preselection is needed so that not all values are always displayed. This can be addressed by using subtables.
For each table, subtables can be defined to contain only specific records from that table. This is done by defining filters in the subtable. The subtable can then be specified directly in queries, so filters do not have to be set each time.
In this example, only customers in category A (as defined per tree) and in region North or South (as defined per items) are selected.
