Create and configure a regular dimension-logic table - Dataphin

Limitations

You cannot set the data standard field in a table without the data standard module.
You cannot set the data classification and data categorization fields in a table without the asset security module.
You cannot enforce unique and non-null validation for primary key fields without the data quality module.

Prerequisites

The business entity is created. For more information, see Create and manage business entities.

Procedure

Step 1: Create a regular dimension logical table

In the top menu bar of the Dataphin home page, choose Develop > Data Development.
In the top menu bar, select a project (in Dev-Prod mode, you must also select an environment).
In the left-side navigation pane, choose Standardized Modeling > Dimension Logical Table.
In the list of dimension logical tables on the right, click the Create icon.

In the Create Dimension Logical Table dialog box, configure the following parameters.

Parameter	Description
Business object	Select standard object.
Table type	When you select standard object for Business object, the table type is automatically set to Standard Dimension Logical Table. This parameter cannot be modified.
Data Block	Defaults to the Data Block associated with the project. This parameter cannot be modified.
Subject area	Defaults to the subject area of the business object. This parameter cannot be modified.
Compute engine	If your Dataphin instance is configured with a Hadoop compute engine, you can select a compute engine, such as Hive, Impala, or Spark. Important You can select a compute engine only after you enable it. For more information, see Create a Hadoop compute source. If the compute engine is TDH 6.x or TDH 9.3.x, you cannot set this parameter. The compute engines have the following limitations: Hive: Cannot read source tables stored in Kudu format. Impala: Can read source tables stored in Kudu format but does not support storing logical tables in Kudu format. We recommend using Impala only if your source tables are in Kudu format. Spark: Cannot read source tables stored in Kudu format.
Data timeliness	Specifies the data timeliness for the Dimension Logical Table. A Standard Dimension Logical Table supports T+1 (daily table), T+h (hourly table), and T+m (minute-level table). Note The ArgoDB compute engine supports only offline T+1 (daily table). The Doris and SelectDB compute engines do not support T+m (minute-level table).
Logical table name	Enter the logical table name. The name must not exceed 100 characters. After you select a business object, the system automatically populates the table name based on the following rule: `{Data Block name}.dim_{business object code}_{data timeliness}`. Important The name can contain only letters, digits, and underscores (_). It must start with a letter. The name is not case-sensitive. Uppercase letters are automatically converted to lowercase. `label_` is a system-reserved prefix. The name cannot start with `label_`. For AnalyticDB for PostgreSQL, the table name must not exceed 50 characters. The `{data timeliness}` part of the name varies based on the selected data timeliness. The following list describes the values. df: For T+1 timeliness, represents a daily full. The table stores a full snapshot of all historical data up to the current day. hf: For T+h timeliness, represents an hourly full. The table stores a full snapshot of all historical data up to the current hour. mf: For T+m timeliness, represents a minute-level full. Every 15 minutes, the table stores a full snapshot of all historical data up to the most recent 15-minute interval.
Display name	Follow these naming conventions: The name must not exceed 128 characters. All characters are supported.
Description	Enter a brief description of the Dimension Logical Table. The description must not exceed 1,000 characters.

Click OK to create the Standard Dimension Logical Table.

Step 2: Configure regular dimension logical table fields

On the Table Structure page, configure the fields, data types, and field types of the dimension logic table.

image..png

Note

Click the icon next to the logic table name to view its basic information.

Section	Description
①Field navigation	Click to navigate to the basic settings or data governance section of the field list.
②Search and filter	Search for fields by field name. Click the icon to filter by criteria such as data type, field type, with or without associated dimensions, and associated dimension, constraint, and data security level.
③Field list operations	Reorder fields: Before reordering fields, ensure that no downstream dependencies use a `select ` query on the table to avoid data errors. Import fields: You can import from a DDL statement* or import from a table. For more information, see Import fields to a dimension logic table. Add field: Add a data field or partition field and edit its name, description, data type, field type, associated dimension, field standard, constraint, data classification, data security level, and comment. Note The MaxCompute engine supports a maximum of six levels of partition fields. The ArgoDB, StarRocks, SelectDB, and Doris compute engines do not support adding partition fields.
④Field list	The field list displays the following details for each field: sequence number, field name, description, data type, field type, associated dimension, field standard, constraint, data classification, data security level, and comment. Sequence number: The order of the field. This number increments by 1 for each new field. Field name: The field's name. When you enter a name or keyword, the system suggests matching standard field names. Description: A description for the field, up to 512 characters in length. Data type: Supported data types include string, bigint, double, timestamp, and decimal, as well as generic types like Text, Numeric, Datetime, and Other. Field type: The field's type, which can be primary key, partition, or attribute. Note Only one primary key field is allowed. Only fields of type string, varchar, bigint, int, tinyint, or smallint can be used as partition fields. Associated dimension: For more information, see Add an associated dimension. Field standard: Select a standard for the field. To create a standard, see Create and manage data standards. Constraint: Supports unique and not null constraints. Data classification: Select a data classification for the field. To create a data classification, see Create a data classification. Data security level: After you select a data classification, the system automatically identifies the data security level. Comment: Enter a comment for the field, up to 2,048 characters in length. You can also Delete a field in the Operations column. Note Field deletion is irreversible. The primary key and system partition fields of a dimension logic table cannot be deleted.
⑤Batch operations	You can select multiple fields and perform the following batch operations: Delete: Click the icon to delete the selected data fields. Data type: Click the icon to change the data type for the selected fields. Field type: Click the icon to change the field type for the selected fields. Root-based naming: Click the icon. The system tokenizes the content in the description field and matches it with existing root words to recommend field names. In the Root-based naming dialog box, you can replace the current names of the selected fields with the suggested ones. Note If a recommended field name is not suitable, you can enter a custom name in the Corrected field name box. Click Reset to revert the Modified field name to the original system-suggested name. Field standard: Click the icon. The system recommends field standards based on the field names. In the Field standard dialog box, you can apply the recommended standards to the selected fields. Constraint: Click the icon to set constraints for the selected fields. Important Sub-dimension logic tables do not support constraints.

Click Save and Next.

Import fields into a dimension logical table

Import from Table

On the configuration page of the dimension logical table, click Import from Table.

In the Import from Table dialog box, select a Source Table, and then select the fields to add.

Parameter	Description
Source Table	You can select any physical table (excluding those automatically generated by Dataphin), logical table, or view (excluding parameterized views) from the current tenant for which you have read permission. To learn how to get read permissions for a physical table, see Apply for, renew, and revoke table permissions.
Field List	Select the fields to add. Note You can select fields from multiple source tables by switching the source table.
Selected Fields	The added fields are added to Selected Fields. You can perform a Delete operation on the selected fields.

Click Add to import the selected fields into the dimension logical table.

After the fields are added to the New Field section, you can edit their name, data type, field category, and associated dimension as needed.

Import from DDL Statement
1. On the configuration page of the dimension logical table, click Import from DDL Statement.
2. In the Import from DDL Statement dialog box, enter a DDL statement and click Parse SQL.
3. In the field list, select the fields you need, and click Add to create new fields for the dimension logic table.

Associated dimension

On the configuration page for the dimension logic table, click the icon in the Associated dimension column to open the Edit model relationship dialog box.

In the Edit model relationship dialog box, configure the parameters.

Section	Parameter	Description
Null replacement value		If a join fails between the primary table (the current dimension logic table) and the associated dimension logic table, Dataphin automatically sets the value of the join field to -110.
Dimension logic table	Associated entity, Dimension logic table	Select the associated entity and dimension logic table that you have created.
Edit association logic	Association logic	By default, this displays the join field from the current table and the primary key from the associated dimension logic table. This configuration cannot be modified.
	Dimension table versioning policy	Specifies which partitions to use when the primary table (the current dimension logic table) joins with the associated dimension logic table. By default, the policy uses the same scheduling cycle as the current dimension logic table. The following options are available: use same-cycle dimension: The system joins the primary table and the associated dimension logic table using time partitions from the same cycle. For example, if the business date is 20220101 and you need to join the `ds=20220101` partition of the primary table with the `20220101` partition of the dimension logic table, select this option. use latest dimension table: The system uses the latest partition of the associated dimension logic table for the join. For example, consider a product category that changes frequently. An item might have been in the 'Mobile Phones' category 10 days ago but is in the 'Appliances' category today. If your business requires you to reprocess the data from 10 days ago by using the latest category ('Appliances'), select the use latest dimension table option.
	Non-join policy	Specifies how to handle records from the primary table (left table) that do not have a match in the dimension logic table (right table). You can choose between keep original non-joined data and replace non-joined data with default value: keep original non-joined data: The original data from the unmatched records in the primary table is retained. replace non-joined data with default value: For records in the primary table without a match in the dimension logic table, their corresponding attributes from the dimension table are set to the default value of -110.
Edit dimension role	Role English name, Role name	A dimension role is an alias for a dimension. When you join the same dimension multiple times, you must define a unique role for each instance. Each role requires a role English name and a role name: The prefix for the Role English name is `dim` by default, and the naming rules for the custom part are as follows: Must contain only letters, numbers, and underscores (_). Must be 64 characters or fewer. The role name must follow these rules: Must contain only Chinese characters, letters, numbers, underscores (_), and hyphens (-). Must be 64 characters or fewer.

Click OK.

Step 3: Configure the calculation logic

On the calculation logic page, you can configure the mapping between the source data and the primary key of a dimension logical table.

Click source configuration to open the source configuration dialog box. Click the +Add source object button to configure the source parameters.

Note

Do not add an extra filter on event time in the filter condition or in custom SQL.

Parameter	Description
Source type	Supported source types are physical table, custom SQL, and logical table. Source table type descriptions: Source tables with a primary key: You can configure multiple sources for a logical table with a primary key. The first source is always the primary source, which determines the total number of rows in the logical table. Source tables without a primary key: You can configure only one source for a logical table without a primary key. If the source involves multiple tables, use custom SQL to pre-join them. Note To add more source objects, click Add source object.
Source object	Select physical table: You can select any physical table (excluding those automatically generated by Dataphin) or physical view (excluding parameterized views) from the current tenant that you have read permissions for. For more information about how to obtain read permissions for a physical table, see Apply for, renew, and revoke table permissions. Select custom SQL: Click the icon and enter your query in the editor. For example: `select id, name from project_name_dev.table_name1 t1 join project_name2_dev.table_name2 t2 on t1.id = t2.id` Select logical table: You can select any logical table from the current tenant that you have read permissions for. For more information about how to obtain read permissions for a physical table, see Apply for, renew, and revoke table permissions. Important Using a logical table as a data source for another logical table increases calculation logic complexity and operational overhead.
Object alias	Enter a custom alias for the source object, such as t1 or t2.
Object description	Enter a description for the object. The description must not exceed 1,000 characters.
Filter condition	Specifies the filter condition for the custom SQL source. Click the icon and enter the condition in the editor. For example: `ds=${bizdate} and condition1=value1`.
Join key	The field in the source object used to perform an equi-join with the logical table's primary key.
Delete	The primary source cannot be deleted. For logical tables without a primary key, deleting the source removes the fields' calculation logic.

Click OK to complete the source configuration.
After you configure the sources, drag the source fields into the calculation logic area. Alternatively, you can click the quick mapping for fields with the same name button to automatically map source fields to logical table fields that share the same name.

Click the icon to edit the calculation logic expression in the editor. The expression does not support aggregate functions, such as sum, count, and min. For example:
- Example 1: substr(t1.column2, 3, 10)
- Example 2: case when t1.column2 != '1' then 'Y' else 'N' end
- Example 3: t1.column2 + t2.column1
After configuring the calculation logic, click the icon at the bottom to validate the expression.

Click the SQL preview button to preview the generated SQL.
Click Save and Next.

Step 4: Configure constraints

The system uses field constraints to create quality rules for the logical table in the quality module. You can set the rule strength for each field to either strong rule or weak rule. For more information, see Data table quality rules.

Note
The rule strength for a logical table's field constraints can be configured only at this stage and cannot be edited in the quality module.
Click Save and Next.

Step 5: Configure logical table scheduling

On the Scheduling & parameter settings page, configure the data latency, scheduling properties, upstream dependencies, parameter configuration, and runtime configuration for the dimension logic table.

Parameter	Description
Data latency	If you enable data latency, the system automatically reruns all data for this logic table within the Maximum Latency Days window. For more information, see Configure data latency for a logic table.
Scheduling properties	Use scheduling properties to define how the dimension logic table runs in the production environment. You can configure the scheduling type, cycle, logic, and execution details. For more information, see Configure scheduling properties for a logic table.
Upstream dependencies	Upstream dependencies define the position of the logic table as a node within a scheduling task. Dataphin uses these dependencies to run all nodes in a business process in the correct order, ensuring that data is produced efficiently and on time. For more information, see Configure upstream dependencies for a logic table.
Parameter configuration	Use parameter configuration to assign values to variables in your code. When a task is scheduled, these variables are automatically replaced with their assigned values. On this page, you can choose to Ignore a parameter or Convert to global variable. For more information, see Configure parameters for a logic table.
Runtime configuration	You can configure task-level runtime configurations for the dimension logic table, such as timeout periods and retry policies for failed tasks. This helps prevent resource waste from long-running computations and improves task execution reliability. For more information, see Configure runtime configuration for a logic table.
Resource configuration	You can assign a scheduling resource group to the logic table task. When the task runs, it uses the resource quota from the assigned group. For more information, see Configure resource configuration for a logic table.

Click Save and Submit.

Step 6: Save and commit logical table

After you configure the regular dimension logical table, click Save and Submit.

The system validates the configuration of the table structure, computing logic, scheduling dependency, and runtime parameters. Based on any failed check results, you can review the configuration information and complete the configuration.
After all check results pass, enter a submission comment, and click Confirm and Submit.

When you submit, Dataphin performs a lineage analysis and a submission check. For more information, see Submission instructions for standard modeling tasks.

Single tenant multiple engines

When configuring the field list using import from table, you can only import tables from projects that use the same engine type.
You can only select a source table from a project in the same cluster as the current project.

Next steps

For projects in Dev-Prod mode, deploy the logical table to the production environment. For details, see Manage deployment tasks.

After you deploy the logical table to the production environment, you can view and manage its tasks in the Operation Center. For details, see Operation Center.