Create a logical model: fact table

Modeling perspective

Dimensional modeling organizes model tables into three levels: Common layer, Application layer, and Uncategorized. The Common layer is used to build reusable unified metrics, dimensions, and detailed fact data, and supports management from either data domain or business category perspectives. The Application layer addresses business-specific statistical needs and supports only the business category perspective. After selecting a level, you can create and manage model tables in the corresponding directory tree.

Create a fact table

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Modeling in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Modeling.

2. In the top navigation bar of the Data Modeling page, click Dimensional Modeling to go to the Dimensional Modeling page.

3. Create a fact table.

   1. On the Dimensional Modeling page, hover over the icon and choose Logical Model > Create Fact Table.

   2. Configure the basic information for the fact table.

      Parameter	Description
      Data Layer	Select a data layer. Only the DWD of the common layer is supported. The fact table's data is stored in this data layer. For more information, see Define data warehouse layers.
      Business Category	Select an existing business category. For more information, see Business Category.
      Business Process	Select an existing business process. For more information, see Business Process.
      Storage Policy	Defines how data is stored in the fact table. Options include Synchronize Incremental Data Daily and Synchronize All Data Daily.
      Naming Rule	Select a configured checker to verify that the table name complies with the specified naming rules. For more information, see Configure data warehouse layer checkers and Use checkers.
      Table Name	The internal name of the table. If you select a checker for the Table naming rule parameter, the name must comply with the defined rules.
      Table Display Name	The display name of the table.
      Lifecycle	The retention period of the fact table in days. The maximum value is 36000.
      Owner	The owner of the fact table. By default, this is the user who creates the table.
      Description	The description of the fact table.

4. After you configure the parameters, click Save. The new table appears in the left-side directory tree.

Add table fields

After you create the model, you must add fields to it.

You can add fields in Shortcut Mode or Script Mode. In Shortcut Mode, you can select Import from Table/View to import fields from an existing physical table or view in a compute engine. Search for and select an existing physical table or view from the Search for existing table/view drop-down list to import its fields.

-- After a model is published to create a physical table (before, during, or after approval), the table name cannot be modified.
CREATE DIM TABLE dim_ec_pub_department_df ALIAS 'Department dimension table'
(
    id            ALIAS 'id' STRING COMMENT 'id',
    gmt_create    ALIAS 'Creation time' TIMESTAMP COMMENT 'Creation time',
    gmt_modified ALIAS 'Modification time' TIMESTAMP COMMENT 'Modification time',
    name          ALIAS 'Department name' STRING COMMENT 'Department name',
    parent_id     ALIAS 'Parent department ID' STRING COMMENT 'Parent department ID',
    `level`       ALIAS 'Level; 0: Group; 1: Subsidiary; 2: Business unit; 3: Department.' BIGINT COMMENT 'Level; 0: Group; 1: Subsidiary; 2: Business unit; 3: Department.',
    ds            ALIAS 'Business date, yyyymmdd' STRING COMMENT 'Business date, yyyymmdd'
)
COMMENT 'Department dimension table'
WITH('life_cycle'='1000');

Configure field information

After you add fields to the model, you can configure their attributes based on your business needs, such as Associated Field, Redundant Field, and Associated Granularity/Metric.

1. Set field attributes.

   By default, the page displays basic attributes such as Field Name, Type, Field Display Name, Description, Primary Key, Not Null, and Actions. In the upper-right corner of the field list, click Field Display Settings to select the attributes you want to display and modify them as needed.

2. Set the Field Standard to Associate and Lookup Table to Associate for the fields. This helps standardize the content and range of values for the added fields.

   • Field Standard to Associate: Centrally manages data that has the same meaning but different field names, and defines value ranges, units of measure, and more.

   • Lookup Table to Associate: Defines the available values and value range for a field standard.

3. Set the Redundant Field.

   In the Actions column for the desired field, click Redundant Field to configure its associated fields.

   In a traditional star schema, dimensions are stored in dimension tables and are accessed through foreign keys in the fact table to reduce storage consumption. However, to improve downstream query performance and simplify data access, it is common to denormalize frequently used dimension attributes directly into the fact table. This reduces the number of required table joins. For example, the 'order creation detail table' denormalizes attributes from the 'shipping address dimension table', such as 'shipping address' and 'consignee phone number'.

   In the Redundant field dialog box, select an Associated table/view name from MaxCompute, select the desired Associated fields from the list, and then click Save to complete the configuration.

4. After configuring the fields, click Save in the upper-left corner.

Next steps

After you create the fact table, you still need to configure its fields, associations, and partitions. Then, you can publish the table to the target environment. For more information, see Materialize a logical model.