Dataphin:Create a General Logical Dimension Table

Limits

• If you have not purchased the Data Standard module, you cannot set the Data Standard field in the table.

• If you have not purchased the Asset Security module, you cannot set the Data Grading and Data Classification fields in the table.

• If you have not purchased the Asset Quality module, you cannot implement Unique and Not Null validation for primary key fields.

Prerequisites

You have created business entities. For more information, see Create and manage business entities.

Procedure

Step 1: Create a general logical dimension table

1. In the top menu bar on the Dataphin home page, select Development > Data Development.

2. Select Project in the top menu bar (Dev-Prod mode also requires selecting Environment).

3. In the navigation pane on the left, select Standard Modeling > Logical Dimension Table.

4. In the list of logical dimension tables on the right, click the new icon.

5. In the New Logical Dimension Table dialog box, configure the parameters.

   Parameter	Description
   Business Object	Select General Object.
   Table Type	When the business object is General Object, the table type is General Logical Dimension Table and cannot be modified.
   Data Section	Defaults to the data section associated with the project and cannot be modified.
   Data Domain	Defaults to the subject area where the business object is located and cannot be modified.
   Compute Engine	Set the Dataphin instance to support the selection of compute engines for tenants of the Hadoop compute engine, including Hive, Impala, and Spark. Important The corresponding compute engine must be enabled before it can be selected. For more information, see create a Hadoop compute source. When the compute engine is TDH 6.x or TDH 9.3.x, this option is not supported. There are the following restrictions on the compute engine, detailed as follows: Hive: Cannot read source tables stored in Kudu format. Impala: Can read source tables stored in Kudu format, but does not currently support storing logical tables in Kudu. If there are no source tables in Kudu format, it is not recommended to use. Spark: Spark cannot read source tables stored in Kudu format.
   Data Timeliness	Data timeliness is used to define the data timeliness of the subsequent logical dimension table. The data timeliness of a general logical dimension table supports selecting T+1 (daily table), T+h (hourly table), and T+m (minute table). Note ArgoDB, StarRocks, SelectDB, and Doris compute engines only support offline T+1 (daily table).
   Logical Table Name	Enter the logical table name. The total length of the table name must be within 100 characters. After selecting a business object, the system will automatically fill in the logical table name. The fill rule is <data section name>.dim_<business object code>_<data timeliness>. Important Only letters, numbers, and underscores (_) are allowed, and the first character must be a letter. Case is not sensitive. When entering uppercase letters, the system will automatically convert them to lowercase. label_ is a reserved prefix in the system and cannot start with label_. The total length of the table name for AnalyticDB for PostgreSQL must be within 50 characters. According to the selected data timeliness above, the <data timeliness> displays different information, detailed as follows: df: T+1 timeliness, daily full data, stores full data up to the current day every day. hf: T+h timeliness, hourly full data, stores full data up to the current hour every hour. mf: T+m timeliness, minute full data, stores full data up to the last 15 minutes every 15 minutes.
   Chinese Name	The naming convention is as follows: The name can be up to 128 characters in length. Any character is supported.
   Description	Enter a brief description of the logical dimension table, within 1000 characters.

6. Click OK to create the general logical dimension table.

Step 2: Configure general logical dimension table field information

1. On the Table Schema configuration page, configure the fields, data types, field categories, and other schema information for the current general logical dimension table.

   

   Note

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

   Block	Description
   ①Field Positioning	Click to locate the Basic Configuration or Asset Administration configuration in the field list.
   ②Search and Filter	You can search for fields by table field name. Click to filter by Data Type, Field Category, With or Without Associated Dimension, Associated Dimension, Field Constraint, Data Sensitivity Level.
   ③Field List Operations	Reorder Columns: Before adjusting the field order, ensure that downstream references to this logical table do not use select * to query this logical table, to avoid data errors. Introduce Fields: Add new fields to the table. Supports introduction from Create Table Statement and From Table. For specific operations, see introduce fields to a logical dimension table. Add Fields: Add Data Fields and Partition Fields. You can edit the field's Name, Description, Data Type, Column Type, Associated Dimension, Recommend Standards, Field Constraint, Data Class, Data Sensitivity Level, and Remarks information according to business requirements. Note The MaxCompute engine supports creating partition fields with no more than 6 levels. The ArgoDB, StarRocks, SelectDB, and Doris compute engines do not support adding partition fields.
   ④Field List	The field list displays detailed information about fields such as Serial Number, Column Name, Description, Data Type, Column Type, Associated Dimension, Recommend Standards, Field Constraint, Data Class, Data Sensitivity Level, and Remarks. Serial Number: The serial number of the table field. Each new field increases by +1. Field Name: The name of the table field. You can enter the field name or Chinese keywords, and it will automatically match the standard preset field name. Description: The description information of the table field, limited to 512 characters. Data Type: Supports string, bigint, double, timestamp, decimal, Text, Numeric, Datetime Variable, and Other data types. Column Type: Supports setting as Primary Key, Partition, Property. Note Only one primary key field is allowed. Only fields of type string, varchar, bigint, int, tinyint, and smallint are supported as partition fields. Associated Dimension: For specific operations, see Add Associated Dimension. Recommend Standards: Select the field standard for the field. To create a standard, see Create and manage data standards. Field Constraint: Select the field constraint for the field. Supports Unique and Not Null constraints. Data Class: Select the data classification for the field. To create a data classification, see Create data classification. Data Sensitivity Level: After selecting the data classification, the system will automatically detect the data level. Remarks: Enter the remarks information for the field. Limited to 2048 characters. At the same time, you can perform Delete operations on fields in the operation column. Note Fields cannot be revoked after deletion. The primary key and system partition fields of the logical dimension table do not support deletion.
   ⑤Batch Operations	You can batch select table fields and perform the following operations. Delete: Click the icon to batch delete the selected data fields. Data Type: Click the icon to batch modify the selected data types. Column Type: Click the icon to batch modify the selected field categories. Recommend Names: Click the icon, and the system will segment the Description content of the field and match the created word roots to recommend field names. You can replace the selected field names with modified values in the Word Root Naming dialog box. As shown in the following figure: Note If the recommended field names do not meet the requirements, you can modify them in the Modified Field Name input box. Click Reset to reset the Modified Field Name to the system's hit word root. Recommend Standards: Click the icon, and the system will recommend field standards based on the field name. You can set the field to the recommended field standard in the Field Standard dialog box. Field Constraint: Click the icon to batch set field constraints. Important Sub-dimension logical tables do not support setting field constraints.

2. Click Save And Next.

Introduce fields to a logical dimension table

• Introduce from Table

  1. On the configuration page of the logical dimension table that you created, click Import From Table.

     

  2. In the Introduce From Table dialog box, select a Source Table and then select the fields that you want to add.

     Parameter	Description
     Source Table	Supports selecting all physical tables (non-Dataphin automatically generated physical tables), logical tables, and views (non-parameterized views) with read permissions under the current tenant. For how to obtain read permissions for physical tables, see apply, renew, and return table permissions.
     Field List	Select the fields you need to add. Note You can select fields from multiple source tables by switching the source table method.
     Selected Fields	The added fields will be added to the selected fields. You can perform Delete operations on the selected fields.

  3. Click Add to import fields from a physical table into the logical dimension table.

     After a field is added to the New Field area, you can edit its name, data type, field category, and associated dimension as needed.

• Introduce from Create Table Statement

  1. On the configuration page of the created logical dimension table, click Import From CREATE TABLE Statement.

  2. In the Introduce From Create Table Statement dialog box, enter the CREATE TABLE statement and click Parse SQL.

  3. In the field list, select the fields that you need and click Add to create new fields for the logical dimension table.

Add associated dimension

1. On the configuration page of the logical dimension table, click the icon in the Associated Dimension column to open the Edit Model Relationship dialog box.

2. In the Edit Model Relationship dialog box, configure the parameters.

   Block	Parameter	Description
   Null Value Replacement		If the primary table (current logical dimension table) cannot be associated with the required logical dimension table, Dataphin automatically fills the foreign key field with a value of -110.
   Logical Dimension Table	Associated Entity, Logical Dimension Table	Select the Associated Entity and Logical Dimension Table you have created.
   Edit Association Logic	Association Logic	By default, it displays the fields that need to be associated with the dimension and the primary key of the associated logical dimension table, which cannot be modified.
   	Dimension Table Version Policy	Define the partition of the primary table (current logical dimension table) and the associated logical dimension table, by default using the same scheduling cycle as the current logical dimension table. More details are as follows: Use The Same Cycle Dimension (the Primary Table And Dimension Table Use The Same Cycle Partition): The primary table and the associated logical dimension table use the same cycle time partition during computation. For example, if the business date is 20220101, you need to query the data of the ds=20220101 partition of the primary table, and the time partition of the associated logical dimension table is also 20220101, then you need to select to use the same cycle dimension table. Use The Latest Dimension Table (the Dimension Table Uses The Latest Partition): During data computation, the latest partition of the latest associated logical dimension table is used. For example, a product category is often adjusted. 10 days ago, it was a mobile phone category, and today it is an appliance category. If the business needs to rerun the data from 10 days ago according to the appliance category, the dimension table version policy needs to be selected as Use The Latest Dimension Table (the Dimension Table Uses The Latest Partition).
   	Missing Association Policy	The missing association policy is used to define the calculation logic for fields that exist in the source primary table (left table) but do not exist in the logical dimension table (right table), that is, fields that cannot be associated. You can choose Retain Original Missing Association Data and Use Default Value For Missing Association Data: Retain Original Missing Association Data: When creating derived metrics, retain the original data of the left table. Use Default Value For Missing Association Data: If the fields in the primary table are not associated with the logical dimension table, use the default value -110.
   Edit Dimension Role	Role English Name, Role Name	The dimension role serves as a dimension alias. When the same dimension is referenced multiple times, it cannot be redefined with the same name, playing a role assumption effect. You need to define Role English Name and Role Name: The prefix of the Role English Name defaults to dim, and the naming convention for the custom part is: Contains letters, numbers, or underscores (_). Cannot exceed 64 characters. The naming convention for the Role Name: Contains Chinese characters, numbers, letters, underscores (_), or hyphens (-). Cannot exceed 64 characters.

3. Click OK.

Step 3: Configure general logical dimension table computation logic

1. On the Computation Logic configuration page, you can configure the mapping between the source data and the primary key of the logical dimension table.

2. Click Source Configuration. In the Source Configuration dialog box, click the +Add Source Object button and configure the source parameters.

   Note

   It is recommended not to set additional filters on event time in filter conditions or custom SQL.

   Parameter	Description
   Source Type	Supports three source types: Physical Table, Sql, and Logical Table. Source Table Type Description: Source Table with Primary Key: Logical tables with primary keys allow multiple sources to be configured, with the first one fixed as the main source. The total amount of data (number of rows) in the logical table is determined by this source. Source Table without Primary Key: Logical tables without primary keys only allow one source to be configured. If there are multiple tables as sources, use custom SQL to complete the association of multiple tables in advance. Note To configure multiple source objects, you can click Add Source Object to add new ones.
   Source Object	Select Physical Table: Supports selecting all physical tables (non-Dataphin automatically generated physical tables) and physical views (non-parameterized views) with read permissions under the current tenant. For information about how to obtain read permissions for physical tables, see apply, renew, and return table permissions. Select SQL: Click the icon and enter content in the edit box, 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: Supports selecting all logical tables with read permissions under the current tenant. For information about how to obtain read permissions for physical tables, see apply, renew, and return table permissions. Important Using a logical table as the data source for another logical table increases the complexity of the computation logic and the difficulty of operation and maintenance.
   Object Alias	Customize the source table alias. For example: t1, t2.
   Object Description	Please enter the description information of the object. Limited to 1000 characters.
   Filter Condition	Custom SQL filter conditions. Click the icon and enter content in the edit box, for example: ds=${bizdate} and condition1=value1.
   Foreign Key Field	The field in the source object that corresponds to the primary key of the logical table and can be associated with the primary key for equality.
   Delete	The main source does not support deletion. For logical tables without primary keys, deleting the source will clear the computation logic of the fields.

3. Click OK to complete the source configuration.

4. After you complete the source configuration, drag the source fields into the computation logic. You can also click the Map By Name button to batch-map the source data fields to the computation logic of the logical table fields that have the same name.

   Click the icon, and you can edit the computation logic expression in the edit box (expressions do not support aggregate functions such as sum, count, 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

5. After completing the computation logic configuration, click the icon at the bottom to verify the validity of the expression.

   Click the Preview SQL button to view the computation logic SQL.

6. Click Save And Next.

Step 4: Configure constraints

1. Based on field constraints, the system creates quality rules for the current logical table in the quality module. You can set the rule strength for the fields here, including strong rule and soft rule. For more information, see Data table quality rules.

   Note

   Logical table field constraints can only be configured for rule strength here and cannot be edited in the quality module.

2. Click Save And Next.

Step 5: Configure general logical dimension table scheduling

1. On the Scheduling & Parameter Configuration page, configure the Data Latency, Scheduling Attributes, Scheduling Dependencies, Scheduling Parameters, and Running Configuration of the logical dimension table.

   Parameter	Description
   Data Latency	When data latency is enabled, the system will automatically rerun all data of this logical table within the Maximum Latency Days cycle. For detailed instructions, see Configure logical table data latency.
   Scheduling attributes	Used to define the scheduling method of the logical dimension table in the production environment. You can configure the scheduling type, scheduling cycle, scheduling logic, and execution of the logical dimension table through scheduling attributes. For detailed instructions, see configure logical table scheduling attributes.
   Upstream Dependency	Used to define the node of the logical table in the scheduling task. Dataphin orderly runs the nodes in the business process through the configuration results of the scheduling dependencies of each node to ensure the effective and timely output of business data. For detailed instructions, see configure logical table upstream dependency.
   Parameter Configuration	Parameter configuration is used to assign values to variables used in the code so that when the node is scheduled, the parameter variables can be automatically replaced with the corresponding variable values. On the scheduling parameter configuration page, you can perform Ignore or Convert To Global Variable operations on parameter configuration. For detailed instructions, see Logical table parameter configuration.
   Running Configuration	You can configure task-level runtime timeout and rerun policies for task failures for the logical dimension table according to the business scenario to prevent resource waste caused by long-term resource occupation of computing tasks while improving the reliability of computing task operation. For detailed instructions, see logical table running configuration.
   Resource Configuration	You can configure the scheduling resource group for the current logical table task. The logical table task will occupy the resource quota of this resource group during scheduling. For configuration instructions, see logical table resource configuration.

2. Click Save And Submit.

Step 6: Save and submit the logical table

1. After configuring the general dimension logical table, click Save And Submit.

   The system validates the configurations for Table Structure, Computation Logic, Schedule Dependency, and Runtime Parameter. You can review the configuration and make corrections based on the Check Results of any failed checks.

2. After all check results pass, enter the submission remarks and click Confirm And Submit.

   During submission, Dataphin will perform lineage parsing and submission checks for the task. For more information, see Standard modeling task submission instructions.

What to do next

If the project mode is Dev-Prod, you need to publish the logical table to the production environment. For more information, see Manage release tasks.

After the logical table is published to the production environment, you can view and maintain logical table tasks in the Operation Center. For more information, see Operation Center.