Create a hierarchy dimension logical table

Introduction to hierarchy dimension tables

A hierarchy dimension table corresponds to a hierarchy object. It has the following features:

• Small data volume.

• The data (rows) have a hierarchical relationship in a business context. Examples include administrative regions, enterprise organizational structures, and product categories. Countries, provinces, and cities are independent records, but they have a hierarchical relationship: Country -> Province -> City. Other examples are an enterprise organizational structure, such as Business Group -> Business Unit -> Primary Department -> Secondary Department, and product categories, such as Food -> Snacks -> Dried Fruit.

Example

Data pattern

Hierarchical data in business systems typically follows this pattern.

Data example

The region data table in a business system.

The hierarchy dimension table created from the Dataphin region business object.

Prerequisites

A hierarchy business object must be created. For more information, see Create and manage business entities.

Procedure

1. On the Dataphin home page, choose Development > Data Development from the top menu bar.

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

3. In the navigation pane, choose Standard Modeling > Logical Dimension Table.

4. In the list of logical dimension tables, click the New icon.

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

   Parameter	Description
   Business Object	Select Hierarchical Object. For example, the region business object.
   Table Type	If you select Hierarchical Object for the business object, the table type is automatically set to Hierarchy Dimension Logical Table and cannot be changed.
   Data Section	The default value is the business unit associated with the project. This parameter cannot be changed.
   Data Domain	The default value is the data domain of the business object. This parameter cannot be changed.
   Data Timeliness	Defines the scheduling type for the hierarchy dimension logical table task in the production environment. The default value is T+1 (daily task).
   Logical Table Name	Dataphin automatically fills in the logical table name. The default naming convention is {Business Unit Name}.dim_{Business Object Name}.
   Chinese Name	Enter a Chinese name for the dimension logical table. For example, Regional Dimension Logical Table. The naming conventions are as follows: The name cannot exceed 128 characters in length. All characters are supported.
   Description	Enter a brief description of the hierarchy dimension logical table. The description can be up to 1,000 characters in length.

6. Click OK.

7. On the Computation Logic page, configure the mapping between the source data and the primary key of the dimension logical table.

   Parameter	Description
   Column Name	The default value is the name of the business process. You can also change the field name. For example, region. The naming conventions are as follows: The name cannot exceed 128 characters in length. All characters are supported.
   Data Type	Select the data type of the primary key for the hierarchy logical table. For example, bigint.
   Description	Enter a description for the primary key field of the hierarchy logical table. For example, Region_Primary_Key ID.
   Source Table	Select the data source for the hierarchy dimension. For example, the region data table.
   Number Of Levels	Configure the number of levels for the hierarchy dimension. For example, 5.
   Generate Leaf Dimension Table	Select whether to generate a leaf-level dimension table. A leaf-level dimension table represents the lowest or most detailed level in the hierarchy. For example, the leaf-level dimension table for a product category dimension typically contains specific product information, such as product ID, product name, price, inventory, and sales quantity.
   Child Field, Parent Field	Select the data fields in the source table that represent the child and parent levels. For example, the id (child field) and parent_id (parent field) in the region data table.
   Column Name	Select the data field in the source table that represents the name of the hierarchy dimension table field. For example, the name field in the region data table.
   Root Node Definition	Define the record that corresponds to the root node of the hierarchy. You can define an SQL condition based on the fields of the main source table to identify the root level. For example, in the region data table, China is the root node. The condition can be level = 0 or parent_id is null.
   Filter Condition	Optional. You can define a filter condition for the source table fields. For example, you can filter out dirty data or logically deleted fields. Note You can define conditions for data updates and retrieval periods, which are time partitions, only based on the fields of the main source table.

8. Click Save And Next.

9. On the Scheduling & Parameter Configuration page, configure the dimension logical table by setting its Scheduling Properties, Scheduling Dependencies, and Scheduling Parameters, and Runtime Configuration. The following list describes these configurations:

   • Scheduling Properties: Defines how the dimension logical table is scheduled in the production environment. You can use the scheduling properties to configure the scheduling type, period, logic, and execution of the dimension logical table. For more information, see Configure scheduling properties for a logical table.

   • Scheduling Dependencies: Defines the node of the logical table in a scheduling task. Dataphin runs the nodes in a business flow in an orderly manner based on the configured scheduling dependencies of each node. This ensures that business data is generated efficiently and on time. For more information, see Configure upstream dependencies for a logical table.

   • Runtime Parameters: Allows you to configure parameters by assigning values to variables in the code. This allows parameter variables to be automatically replaced with their corresponding values during node scheduling. On the scheduling parameter configuration page, you can Ignore a parameter configuration or Convert It To A Global Variable. For more information, see Configure runtime parameters for a logical table.

   • Runtime Configuration: Allows you to configure a task-level runtime timeout period and a rerun policy for failed tasks for the dimension logical table based on your business scenario. This prevents resource waste caused by long-running computing tasks and improves the reliability of task execution. For more information, see Configure the runtime properties of a computing task.

   • Resource Configuration: Allows you to configure a scheduling resource group for the current logical table task. When the logical table task is scheduled, it consumes the resource quota of this resource group. For more information, see Configure resources for a logical table.

10. After you configure the hierarchy dimension logical table, click Save And Submit.

    When you submit the task, Dataphin parses the data lineage and performs a submission check. For more information, see Submission description for standard modeling tasks.

What to do next

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

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