Create a fact logical table

Limitations

• 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 sensitivity level and data classification fields in the table.

• If you have not purchased the asset quality module, you cannot perform Unique and Not Null validation for primary key fields.

Prerequisites

You must have created a business activity object. For more information, see Create and manage business entities.

Procedure

Step 1: Create a fact logical table

1. In the top navigation bar of the Dataphin homepage, choose Develop > Data Development.

2. In the top navigation bar, select a Project. If you are in Dev-Prod mode, also select an Environment.

3. In the left-side navigation pane, choose Standardized Modeling > Fact Logical Table.

4. In the fact logical table list on the right, click the New icon.

5. In the Create Fact Logical Table dialog box, configure the parameters.

   Parameter	Description
   Business activity	Select the business activity for which you want to create a fact logical table. Business activities can be of three types: Business Event, Business Snapshot, and Business Process.
   Table type	The logical table type is determined by the business activity type. Set Business Activity to Business Event and Table Type to Event Fact Logical Table. Business Activity is set to Business Snapshot, and Table Type is Snapshot Fact Logical Table. Business Activity is set to Business Process, and Table Type is Process Fact Logical Table.
   Data domain	Defaults to the data domain of the business activity and cannot be modified.
   Subject domain	Defaults to the subject domain of the business activity and cannot be modified.
   Data timeliness	Defines the scheduling type for the fact logical table task in production. Options: Offline T+1 (daily), Offline T+h (hourly), or Offline T+m (minute).
   Logical table name	Enter a name for the logical table, up to 100 characters. After you select a business object, the system auto-populates the name in this format: {data_domain_name}.fct_{business_object_code}_{data_timeliness_suffix}. Important The name can contain only letters, digits, and underscores (_). It must start with a letter. The name is case-insensitive, and uppercase letters are automatically converted to lowercase. label_ is a system-reserved prefix. Identifiers cannot start with label_. The {data_timeliness_suffix} varies based on the data timeliness setting: di: Suffix for T+1, daily incremental. Stores the incremental data for the current day. hi: Suffix for T+h, hourly incremental. Stores the incremental data for the current hour. thi: Suffix for T+h, hourly incremental. Stores the incremental data from 00:00 to the current hour. mi: Suffix for T+m, minute-level incremental. Stores the incremental data for the last 15 minutes in a partition every 15 minutes. tmi: Suffix for T+m, minute-level incremental. Stores the incremental data from 00:00 to the current minute every 15 minutes. df/da: Suffix for T+1, daily full. Stores all historical data up to the current day. hf: Suffix for T+h, hourly full. Stores all historical data up to the current hour. mf: Suffix for T+m, minute-level full. Stores all historical data up to the most recent 15-minute interval.
   Display name	Custom names must follow these rules: Can contain Chinese characters, letters, digits, underscores (_), or hyphens (-). Cannot exceed 64 characters.
   Description	Enter a brief description of the fact logical table, up to 1,000 characters.

6. Click OK.

Step 2: Configure table schema

1. On the Table structure tab, configure the parameters.

   

   Area	Description
   ① Table field locator	Click to navigate to the Basic Configuration or Data Governance settings in the field list.
   ① Search and filter area	Search for fields by name. Click to filter fields by Data type, Field category, Has associated dimension, Associated dimension, Field constraint, or Data sensitivity level.
   ② List operations	Adjust field order: Before reordering fields, ensure that no downstream consumers that reference this logical table use a select * query to avoid data errors. Import fields: Import new fields into the table. You can Import from DDL statement or Import from table. For more information, see Import fields into a fact logical table. Add field: Add a Data field or Partition field. You can configure the field's Name, Description, Data type, Field category, Associated dimension, Field standard, Field constraint, Data classification, Data sensitivity level, and Remarks. Note For the MaxCompute engine, you can create up to six levels of partition fields.
   ③ Field list	The field list displays detailed information for each field, including No., Field Name, Description, Data type, Field category, Associated dimension, Field standard, Field constraint, Data classification, Data sensitivity level, and Remarks. Serial Number: The serial number of a table field. It automatically increments by 1 when a new field is added. Field Name: The name of the field. You can enter a field name or keyword, and the system will automatically match it with standard preset field names. Note: The table field description is limited to 512 characters. Data type: Supports String, Bigint, Double, Timestamp, Decimal, Text, Numeric, Date/Time, and Other. Field Category: Can be set to 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 the data standard for the field. To create a data standard, see Create and manage data standards. Field constraint: Select the constraint for the field. The Unique and Not null constraints are supported. Data classification: Select the data classification for the field. To create a data classification, see Create a new data classification. Data sensitivity level: After you select a data classification, the system automatically identifies the data sensitivity level. Description: Enter a description for the field. The maximum length is 2048 characters. You can also Delete fields in the Actions column. Note Field deletion cannot be undone.
   ④ Batch operations	Select multiple fields to perform the following batch operations. Delete: Click the icon to delete all selected data fields. Data Type: Click the icon to batch-modify the data types of selected fields. Field Category: Click the icon to batch-modify the categories of selected fields. Radix-based naming: Click the icon. The system tokenizes the content in the Description of the field, matches the tokens with existing radixes, and recommends a field name. In the Radix-based Naming dialog box, you can replace the name of the selected field with the modified value. Note If a recommended name is unsuitable, you can modify it in the Corrected field name input box. Click Reset to restore the Modified field name to the system-recommended name. Field Standard: Click the icon. The system recommends field standards based on the field name. In the Field Standard dialog box, you can assign a recommended standard to the field. Field Constraint: Click the icon to batch-configure field constraints. Important Field constraints are not supported for sub-dimension logical tables.

2. Click Save and Next.

Import fields

• Import from table

  1. On the configuration page of the fact logical table, click Import from table.

  2. In the Import from table dialog box, select a Source table, and then select the fields you want 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) in the current tenant for which you have read permissions. To get read permissions for a physical table, see Apply for, renew, and return table permissions.
     Field list	Select the fields that you want to add. Note You can select fields from multiple source tables by switching between them.
     Selected fields	The added fields appear in the selected fields list. You can Delete fields from this list.

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

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

• Import from DDL statement

  The source for measure fields depends on whether a primary key is defined. If a primary key is defined, measure fields can only come from the primary source table. Otherwise, they can come from both the primary source table and other data tables.

  1. On the configuration page of the fact logical table, click Import from DDL statement.

  2. In the Import from DDL statement dialog box, enter the DDL statement and click Parse SQL.

  3. In the field list, select the fields you need and click Add to create new fields in the fact logical table.

Add an associated dimension

Associate the foreign key fields in a fact logical table's fact attributes with a dimension.

1. On the configuration page of the fact logical table, click the icon to open the Edit Model Relationship dialog box.

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

   Area	Parameter	Description
   Null replacement value		If the foreign key field of the fact logical table cannot be joined with the dimension table, the system sets the Default value setting to -110.
   Associated dimension	Associated Entity, Dimension Logical Table	Select an existing Associated entity and Dimension logical table.
   Edit association logic	Association logic	Select a field from the fact logical table and configure its relationship with the primary key of the dimension logical table to be associated.
   	Dimension table version policy	Select the version (partition) of the associated dimension table. By default, the system uses the partition version matching the main table's cycle.
   	Missing join policy	Defines how to handle data from the source main table (left table) that has no match in the dimension logical table (right table). Options: Keep original unmatched data and Replace unmatched data with a default value: Retain original unmatched data: Retains the original data from the left table when you create a derived metric. Replace unjoined data with a default value: If a field in the dimension table is not associated with the fact logical table, the default value -110 is used.
   Edit dimension role	Role Name, Display Name	A dimension role is an alias that lets a dimension play multiple roles, each with a unique name. Define the Role English Name and Role Name: The prefix for Role English Name defaults to dim, and the naming rules for the custom part are as follows: Can contain letters, digits, or underscores (_). Cannot exceed 64 characters. Naming rules for Role Name: Can contain Chinese characters, digits, letters, underscores (_), or hyphens (-). Cannot exceed 64 characters.

3. Click OK.

Step 3: Configure compute logic

1. On the Compute logic tab, configure the mapping between the source data and the primary key of the fact logical table.

2. Click Source Configuration to open the Source Configuration dialog box, and in the Source Configuration dialog box, configure the source parameters.

   Note

   • Do not add extra filters for event time in the filter condition or custom SQL.

   • A logical table without a primary key supports only one source. To use multiple tables as a source, you must first join them by using custom SQL.

   Parameter	Description
   Source type	Supported source types: Physical table, Custom SQL, and Logical table. Source table type description: Source table with a primary key: You can configure multiple sources for a logical table with a primary key. The first source is always the main source and determines the total number of rows in the logical table. Source table without a primary key: You can configure only one source for a logical table without a primary key. If the source consists of multiple tables, use custom SQL to perform the joins in advance. Note To configure multiple source objects, click Add source object.
   Source object	Select Physical Table: You can select all physical tables (not automatically generated by Dataphin) and physical views (non-parameterized views) in the current tenant for which you have read permissions. To get read permissions for a physical table, see Apply for, renew, and return table permissions. Select Custom 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: You can select all logical tables to which you have read permissions in the current tenant. To get read permissions for a logical table, see Apply for, renew, and return table permissions. Important Using a logical table as a data source for another logical table increases the complexity of compute logic and operations and maintenance (O&M). Use this option with caution.
   Object alias	Enter a custom alias for the source table, such as t1 or t2.
   Object description	Enter a description for the object, up to 1,000 characters.
   Filter condition	Click the icon and enter your content in the editor. Example: ds=${bizdate} and condition1=value1 Custom SQL does not have a filter condition.
   Join field	The field in the source object that corresponds to the logical table's primary key and can be used for an equi-join.
   Delete	The main source cannot be deleted. For a logical table without a primary key, deleting the source clears the compute logic for all fields.

3. Click OK to complete the source configuration.

4. After configuring the source, drag the source fields into the compute logic.

   You can also click Map Fields with Same Name to automatically map source data fields to logical table fields with the same name.

   Click the icon to edit the compute logic expression in the editor. Aggregate functions such as sum, count, and min are not supported. Examples:

   • 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. Click the icon at the bottom to validate the expression.

   Click Preview SQL to view the SQL for the compute logic.

6. Click Save and Next.

Step 4: Configure constraints

1. Based on the field constraints, the system creates quality rules for the current logical table in the data quality module. You can set the rule strength for each field to strong or weak. For more information, see Data table quality rules.

2. Click Save and Next.

Step 5: Configure scheduling and parameters

1. On the Scheduling & parameter configuration tab, configure Data latency, Scheduling properties, Upstream dependencies, Parameter configuration, and Runtime configuration for the fact logical table.

   Parameter	Description
   Data latency	When enabled, the system automatically reruns all data for this logical table within the Max latency days period. For more information, see Configure data latency for a logical table.
   Scheduling properties	Defines the scheduling method for the fact logical table in production. Configure the scheduling type, cycle, logic, and execution. For more information, see Configure scheduling properties for a logical table.
   Upstream dependencies	Dataphin runs nodes in a business process according to their configured scheduling dependencies. This ensures that business data is produced efficiently and on time. For more information, see Configure upstream dependencies for a logical table.
   Parameter configuration	Assigns values to variables used in the code so that parameter variables are automatically replaced with their corresponding values during scheduling. On the parameter configuration page, you can Ignore a parameter or Convert to global variable. For more information, see Configure runtime parameters for a logical table.
   Runtime configuration	Configure a task-level runtime timeout and a retry policy for task failures. This prevents resource waste from long-running tasks and improves compute task reliability. For more information, see Configure runtime settings for a logical table.
   	Configure a scheduling resource group for the logical table task. The task consumes this group's resource quota during scheduling. For configuration details, see Logical table resource configuration.

Step 6: Save and submit the logical table

1. After configuring the fact logical table, click Save and Submit. The system validates the configurations for Table Structure, Compute Logic, Scheduling Dependencies, and Runtime Parameters. If a configuration fails validation, use the Check Results to find and fix the error.

2. After all checks pass, enter submission notes, and then click OK and Submit.

   During submission, Dataphin analyzes data lineage and performs submission checks. For more information, see Standardized modeling task submission instructions.

Next steps

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

• After publishing a logical table to the production environment, view and manage its tasks in the O&M Center. For more information, see O&M Center.