Build a Dimension Table for DIM Layer Structuring - DataWorks

How it works

In dimensional modeling, measures are called facts, and the context describing those facts is called a dimension. A Dimension Table holds the detailed attributes that a Fact Table references. For example, an e-commerce data warehouse might have:

An order Dimension Table with fields like order ID, order creation time, buyer ID, and seller ID
A user Dimension Table with fields like gender and birthdate
A commodity Dimension Table with fields like commodity ID, commodity name, and shelf date

After you create a Dimension Table and configure its fields, you can:

Materialize it to a storage engine for querying in a compute engine
Reference its fields directly when designing derived metrics and summary tables

The diagram below shows how Dimension Tables fit into the broader modeling workflow.

Data warehouse layer placement: Dimension Tables are typically stored in the Common Layer (DIM), which processes and integrates shared data into unified, enterprise-wide tables. You can also store Dimension Tables in the Application Layer to serve specific business applications.

The Application Layer only allows application tables by default. To create a Dimension Table there, create a new application layer and set its model type to Dimension or Dimension Table, Dimension. For details, see Define data warehouse layers.

Prerequisites

Before you begin, ensure that you have:

For the Common Layer:

A data warehouse layer created in the Common Layer (determines which layer the table belongs to). See Define data warehouse layers
A Data Domain created (determines the business data perspective for the table). See Data Domain

For the Application Layer:

A data warehouse layer created in the Application Layer (determines which layer the table belongs to). See Define data warehouse layers
A Data Mart created (determines the data category the table serves). See Data Mart

Create a Dimension Table

Go to the Data Modeling page. Log on to the DataWorks console. In the top navigation bar, select your region. In the left-side navigation pane, choose Data Development and O&M > Data Modeling. Select your workspace from the drop-down list and click Go to Data Modeling.
On the Data Modeling page, click Dimensional Modeling.
On the Dimensional Modeling page, hover over the icon and choose Logical Model > Create Dimension Table.

Configure the basic information for the Dimension Table. The following table describes the key parameters. This topic uses the Common Layer as an example.

Parameter	Description
Data Layer	The data warehouse layer for the Dimension Table. Select Common Layer to create a unified, enterprise-wide table (requires a Data Domain). Select Application Layer to create a business-specific table for a particular application (requires a Data Mart).
Business Category	Available when Common Layer is selected. The Business Category to associate with the table.
Data Domain	Available when Common Layer is selected. The Data Domain to associate with the table.
Category/Mart/Subject	Required when Application Layer is selected. The Data Mart or Subject Area under a Business Category.
Storage Policy	The data retention period and volume policy for the table.
Dimension	The dimension associated with the table, defining the perspective for data analysis. For details on creating a dimension, see Conceptual model: Dimension.
Naming Rule	A checker that enforces naming conventions. If selected, the Table Name must comply with its rules. For details, see Configure data warehouse layer checkers.
Table Name	The name of the Dimension Table. Must follow the selected Naming Rule if one is configured.
Table Display Name	The display name shown in the interface.
Lifecycle	The data retention period. Maximum: 36,000 days.
Owner	The person responsible for the table. Defaults to the creator.
Description	A description of the Dimension Table.

创建维度表

Click Save. The Dimension Table appears in the left-side navigation tree of the Dimensional Modeling page, under the corresponding Data Domain or Business Category.

Add fields to the table

After saving the Dimension Table, add fields to it using either Shortcut Mode or Script Mode.

Shortcut mode

Import from Table/View is supported only for MaxCompute, Hologres, and E-MapReduce (EMR) Hive engines.

In Shortcut Mode, click Expand next to Import from Table/View.
In the Search For Existing Table/View search box, enter a name to find the target table or view.

- Fuzzy search is supported: enter a keyword to match all tables or views whose names contain it. - Only tables in the production environment are searchable. Development environment tables are not supported. - The icon indicates all fields will be imported. - The icon indicates specific fields will be imported.
To import specific fields, select the fields from the dialog box that appears and click Import.
If any imported fields have an empty Field Display Name, follow the on-screen prompts to populate it from the field description.

Script mode

Script Mode uses FML (Functional Modeling Language) statements to create fields, associations, and partitions. For details, see Script Mode modeling.

After you fill in the basic information for the Dimension Table and save it, click Script Mode to open the code editor. A dialog box appears with auto-generated modeling code based on your current model configuration. Edit the code as needed and click OK.

Configure field properties

After adding fields, configure their properties including Associated Field, Redundant Field, and Associated Granularity/Metric.

Set field attributes. The field list displays these columns by default: Field Name, Type, Field Display Name, Description, Primary Key, Not Null, Measurement Unit, and Actions. Click Field Display Settings in the upper-right corner to show or hide additional columns.
Associate fields with a Field Standard and Lookup Table. For each field, set a Field Standard to Associate and a Lookup Table to Associate to standardize field values and their allowed ranges: To configure associations for all fields at once, click the association button in the upper-right corner and set Atomic Metric, Lookup Table, and Field Standard in bulk.
- Field Standard to Associate: Standardizes data with the same meaning but different field names. Defines the value range, unit of measurement, and other properties.
- Lookup Table to Associate: Defines the selectable values for a specific Field Standard.
Set redundant fields. In a traditional star schema, Dimension Tables connect to Fact Tables through foreign keys—this keeps storage lean but requires joins at query time. DataWorks Dimensional Modeling instead supports Redundant Fields: pre-joined dimension attributes stored directly in a table to improve query performance and reduce joins. For example: To configure redundant fields, click Redundant Field in the Actions column for the target field.
- The order_creation_details table can include shipping_address and recipient_phone_number from the shipping_address_dimension table.
- The product_dimension table can include procurement_information and brand_information from the product_information table.
Click Save in the upper-left corner.

What's next

After creating the Dimension Table, you must also configure field management, associations, and partition settings. Publish it and configure its downstream usage: