❄️ Dimension Designer
Managing and analyzing dimensions is one of the most important functions of a multidimensional model. The Dimension Designer helps users define the dimensions, hierarchies, and relationships between dimensions to improve the efficiency of data analysis.
The planning and construction of dimensions are important technical standards for measuring multidimensional models, followed by the ability of indicator management from a business perspective. Without good dimension management, there is no good indicator management, and finally, there is good visualization display capability.
There are two ways to create dimensions:
- Inline dimensions;
- Shared dimensions;
Inline Dimensions
Inline dimensions refer to creating dimensions directly in the multidimensional dataset editing interface. The created dimensions can only be used by the current multidimensional dataset and cannot be referenced by other multidimensional datasets.
For more details, refer to Cube Designer #Dimensions.
Shared Dimensions
Shared dimensions refer to dimensions in a multidimensional data model that are shared by many multidimensional datasets. This design ensures data consistency and repeatability while reducing the cost of data storage and maintenance. Shared dimensions can be shared by multiple multidimensional datasets, and each multidimensional dataset can choose different dimension hierarchies and dimension members. Using shared dimensions can standardize dimension data and management.
A dimension can create one or more hierarchies.
Operational steps: Click the New button in the entity title bar, or drag the data table to the model entity area to open the dimension creation window, and select the type as dimension.
- Type: Multidimensional Dataset;
- Name: The unique technical name of the multidimensional dataset;
- Label: The language description of the multidimensional dataset;
- Table: Dimension physical table
- Primary Key: The primary key field of the dimension table;
- Visible: The default level field enabled by the dimension;
- Label: The language description of the dimension physical table field;
- Field: The technical name of the dimension physical table field;
The shared dimension editing interface has a more intuitive functional interface for richer data than inline dimensions. The dimension interface is divided into 4 functional areas:
- Lower left, Dimension Tables: Dimension physical tables that have been added, dragged from the data table area;
- Lower right, Dimension Tables Join: Edit the relationship between dimension tables;
- Upper left, Levels: List of created dimension levels, dragged from the dimension table area;
- Upper right, Dimension Data Preview: Preview dimension data determined by the created levels.
Edit properties:
- Click the more button on the left side of the navigation bar, select (edit) dimension, and set the dimension properties in the property editing interface;
- Click the more button on the hierarchy tab, select (edit) hierarchy, set the properties of the hierarchy in the property editing interface, and can also set the properties of the dimension;
- Click the selected level, set the properties of the level in the property editing interface;
Dimension Properties
Dimension properties set some basic information of the dimension. Detailed list
| Property | English Name | Description | Optional values |
|---|---|---|---|
| Name | Name | Unique name identifier for the dimension | Generally use English characters |
| Label | Caption | Dimension language description | User language |
| Description | Description | Long text description | User language |
| Fact foreign key | Fact foreign key | Fact table foreign key associated with the dimension, this property is available for inline dimensions | Fact physical table field |
| Dimension Type | Dimension Type | Except normal types, it is the Time dimension type | |
| Default Hierarchy | Default Hierarchy | Default hierarchy | |
| Semantics | Semantics | Dimension semantics, enhance dimension expression capability | Semantics settings |
Hierarchies and Levels
- Hierarchy
- Level
Hierarchy shows the relationship between column groups in the dimension table. For example, quarters contain months, and months contain days. Using hierarchies can drill down in reports.
A dimension can have one or more hierarchies. Hierarchies usually start from the total level, then child levels, and finally divide into the lowest detailed level.
All hierarchies of a dimension must have a common lowest level. For example, the time dimension can include a fiscal hierarchy and a calendar hierarchy, and day serves as the common lowest level. The day has two named parents (called fiscal year and calendar year), both of which are children of the All root level.
All levels except the total level must have at least one column specified as a key or display column. However, not all columns in the table need to be explicitly associated with the level. Any column not associated with a level will be associated with the lowest level in the hierarchy (corresponding to the dimension table).
There is no limit to the number of levels that can be included in a hierarchy. The total number of levels itself is not a determinant factor of query performance. However, note that even a few levels can affect performance for extremely complex queries.
Creating Hierarchies
Click the New button in the hierarchy navigation bar of the dimension to create a hierarchy.
- Open the property interface and edit the hierarchy properties: See the following
- Drag the physical table name from the data table to the dimension table area to add it as a dimension table for the hierarchy structure.
- One or more dimension tables can be added.
- When multiple dimension tables are added to the dimension table, the right dimension table relationship design interface will automatically open, or can be opened manually to select the relationship between the tables and add new left and right associations to associate the dimension tables.
Hierarchy Properties
The property list of all hierarchies
| Property | English Name | Description | Optional values |
|---|---|---|---|
| Name | Name | Unique name identifier for the hierarchy | Generally use English characters |
| Label | Caption | Language description for the hierarchy | User language |
| Description | Description | Long text description | User language |
| Visible | Visible | Whether visible to users | |
| Has All Member | Has All Member | Whether there is an All member. If there is, the hierarchy will contain a default All member at this level, which will be the first level | |
| All Member Name | All Member Name | Replace the default generated All member name | |
| All Member Caption | All Member Caption | Label of the All member | |
| All Level Name | All Level Name | Name of the All level | |
| Dimension Tables | Dimension Tables | Dimension table | Physical table array |
| Primary Key | Primary Key | Select a field of the dimension table as the primary key of the hierarchy | |
| Primary Key Table | Primary Key Table | When the number of dimension tables exceeds one, specify |
one table as the main table of the dimension | | | Default Member | Default Member | When no dimension member is specified in the query, use the default member to limit the dimension | |
Creating Levels
- Drag the fields from the dimension table to the level area in sequence, from coarse-grained fields to fine-grained fields, or adjust the order of fields in the level area.
- Drag level fields:
- Adjust the order between levels.
- Drag back to the dimension table area to remove the level.
- Drag to the delete icon to remove it.
- Select the level and open the property interface to set the properties of this level: See the property list below;
Level Properties
The property list of all levels
| Property | English Name | Description | Optional values |
|---|---|---|---|
| Name | Name | Unique name identifier for the level | Generally use English characters |
| Label | Caption | Language description | User language |
| Description | Description | Long text description | User language |
| Visible | Visible | Whether visible to users | |
| Unique Members | Unique Members | This property is used to optimize SQL generation. If you know that the values of a given level column in the dimension table are unique among all other values of that column in the parent level, set it to "true", otherwise set it to "false". | |
| Column | Column | Field corresponding to the level of the data table, as the primary key of the level | |
| Type | Type | Database type corresponding to the level field, which helps the engine generate correct execution statements | |
| Name Column | Name Column | Output ID of the level members, if not set, the member ID is by default composed of the value of Column | |
| Caption Column | Caption Column | Output Caption of the level members, if Caption Column is not set, the member caption is taken from Name Column, and if Name Column is not set, it is taken from the value of Column | |
| Ordinal Column | Ordinal Column | Field determining the order of level members | |
| Parent Column | Parent Column | When the record of the data table is hierarchical, setting the parent column can generate a tree-like member structure | |
| Null Parent Value | Null Parent Value | The value that determines whether the parent member exists, default is null, but some databases do not support null values, so it needs to be specified as empty string, 0 or -1 instead | |
| Table | Table | When the hierarchy structure is composed of multiple tables, specify a table name for the level | |
| Level Time Type | Level Time Type | When the dimension is a time type, set the time type (indicating the granularity of time) for each level | |
| Hide Member If | Hide Member If | Condition for hiding members. If a hierarchy has one or more levels with hidden members, not all leaf members are equidistant from the root, resulting in a serrated hierarchy. | |
| Semantics | Semantics | The semantics of dimension levels, enhance the expression capability of dimension levels | Semantic settings |
| Key Expression | Key Expression | Expression | |
| Name Expression | Name Expression | Expression | |
| Caption Expression | Caption Expression | Expression | |
| Ordinal Expression | Ordinal Expression | Expression | |
| Parent Expression | Parent Expression | Expression | |
| Closure | Closure | When there are hierarchical relationships between levels on the dimension, a closure table can be set to improve the efficiency of SQL execution | 1. Table; 2. Parent field; 3. Child field; |
| Property | Property | Property list of the level | 1. Property name; 2. Property field; 3. Property expression |
Ordinal Column
The Ordinal Column is used to sort level members, for example, the ordinal column of months can be the number of months.
The Ordinal Column attribute specifies a column in the Hierarchy table that provides the order of the members in a given level, while the nameColumn specifies a column that will be displayed.
Expressions
Using SQL expressions can replace field settings to express richer calculation functions. For example, the description field of the Month level can use the following expression to concatenate the year and month for display:
CONCAT("the_year",' ',"the_month")
Note that SQL expression syntax should be consistent with the actual database syntax executed.
Closure Table
When a level is set with a parent field, the members of this level are hierarchical. Uncertain depth of parent-child members will bring challenges to performance when calculating summary values. In this case, a closure table can be set for the parent-child relationship table to improve the performance of summary value calculation.
The closure table is a SQL table, such as an employee closure table that contains records of the relationship between each employee/supervisor (all superior supervisors, regardless of depth). (In mathematical terms, this is called the "reflexive transitive closure" of the employee/supervisor relationship. The distance column is not strictly required, but it makes the filling table easier.)
| employee_closure | ||
|---|---|---|
| supervisor_id | employee_id | distance |
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 1 | 5 | 3 |
| 1 | 6 | 2 |
| 1 | 4 | 1 |
| 2 | 2 | 0 |
| 2 | 3 | 1 |
| 2 | 5 | 2 |
| 2 | 6 | 1 |
| 3 | 3 | 0 |
| 3 | 5 | 1 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 6 | 0 |
The properties are set as shown in the figure below
Semantic Settings
- Semantic Type
- Calendar Formatting
Currently, only Calendar semantics are supported.
Preview
In the Dimension Designer, each hierarchy can preview dimensional data in the preview area, allowing users to verify the correctness of the dimensional data.
There are two ways to preview dimensional data:
- Table: Display dimensional data in tabular form;
- Tree: Display the hierarchical relationship of dimension members in a tree structure;
Tree Preview
The multidimensional engine divides the hierarchical relationship between dimension members according to the order of levels in the hierarchy, forming a tree structure for display:
- Level Sequence: Sequential numbering of levels.
- Each level is displayed as a column, with the column name being the level's name.
- Number of Child Nodes: The number of child nodes under each node, with the lowest level nodes having no child nodes.
- All Member Levels: When the hierarchy is set with the "All Member" attribute, an "All Member" node will be displayed at the top level of the tree structure.
In practical use, metric values will be aggregated and calculated based on this tree structure's relationships with dimensional data.