DataWorks:Logical model: Aggregate table

How it works

An aggregate table is defined by three elements:

• Data domain: the business subject to model (for example, Trade or Sales)

• Period: the time window for aggregation (for example, last 7 days)

• Dimensions: the granularity at which metrics are consolidated (for example, buyer, region, or order type)

Together, these elements—along with the derived metrics—generate the statistical fields in the aggregate table. Each aggregate table belongs to exactly one data domain.

Aggregate tables are typically placed in the Data Warehouse Summary (DWS) layer—the layer designed to output pre-aggregated metrics for downstream consumption. You can also place them in other data layers based on your needs.

Prerequisites

Before you begin, ensure that you have:

• Created data layers. For more information, see Define data warehouse layers

• Created a data domain that defines the business process to model

• Created a period to define the time range for statistical data

Create an aggregate table

1. Go to the Data Modeling page. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and O\&M \> Data Modeling. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Modeling.

2. In the top menu bar of the Data Modeling page, click Dimensional Modeling.

3. On the Dimensional Modeling page, hover over the icon and click Logical Model \> Create Aggregate Table.

4. Configure the basic information for the aggregate table.

   Parameter	Description	Example
   Data layer	The data layer where the aggregate table is stored. Defaults to the DWS layer in the Common Layer—the layer designed for pre-aggregated outputs consumed by downstream applications. For other options, see Define data warehouse layers.	DWS
   Business category	The business category the table belongs to. For more information, see Business category.	Sales
   Data domain	The business subject of the consolidated data. Each aggregate table belongs to exactly one data domain. For more information, see Data domain.	Trade
   Granularity	The dimension that defines the analysis grain—the level at which metrics are computed and stored. Select from existing dimensions. For more information, see Create a conceptual model: Dimension.	Order Type
   Period	The time window for aggregation (for example, last day or last week). Determines which time range is used when computing metrics. Select from existing periods, or create one. For more information, see Period.	1w (Last 7 Days)
   Modifiers	The business scope filter applied to the statistical data. Select from existing modifiers, or create one. For more information, see Modifiers.	Online Store
   Naming rule	A checker that enforces table naming conventions. Select a checker created during data warehouse planning. For more information, see Configure a data layer checker and Use a checker.	—
   Table name	The name of the aggregate table. If a naming rule is configured, the name must comply with it.	dws_trade_buyer_subpay_1d
   Display name	The human-readable name shown in the UI.	Buyer Trade Installment Payment Aggregate Table
   Lifecycle	The retention period of the table, in days.	90 days
   Owner	The owner of the table. Defaults to the user who created it.	—
   Description	A description of the table.	—

   

5. Click Save in the upper-left corner.

Add fields to the table

Add fields using Shortcut Mode or Script Mode.

Shortcut mode: Import from table/view

Import fields from an existing physical table or view in the compute engine. Only MaxCompute, Hologres, and E-MapReduce (EMR) Hive engines are supported.

1. In the Shortcut Mode section, click Expand next to Import from Table/View.

2. In the Search For Existing Table/View search box, enter a name to find and select a table or view.

   Fuzzy matching is supported—enter a keyword to find all matching tables or views. Only tables in the production environment are searchable; development environment tables are not supported.

3. Import the fields:

   • Click the icon to import all fields.

   • Click the icon to select specific fields. In the dialog box that opens, select the fields to add and click Import.

4. If any imported fields have an empty Field Display Name, follow the prompts to populate it from the field description.

Shortcut mode: Import from metrics

1. In the Shortcut Mode section, click Quick Import next to Import from Metrics.

2. In the dialog box, select from all submitted derived metrics and composite metrics to add as fields.

3. Click Import.

Script mode

Script Mode lets you define the model using FML (Field Modeling Language) statements. When you click Script Mode, a dialog box opens with auto-generated modeling language based on your current configuration. Edit as needed, then click OK.

For more information, see Model with Script Mode.

Configure field information

After adding fields, configure their properties and association types.

1. Configure field properties. The field list displays basic properties by default: Field Name, Type, Field Display Name, Description, Primary Key, Not Empty, Measurement Unit, and Actions. Click Field Display Settings in the upper-right corner to show or hide additional properties.

2. Associate fields with a field standard. A Field Standard uniformly manages fields that share the same meaning but have different names, and defines their value ranges and measurement units. Associate a field with a field standard to standardize its content.

3. Configure redundant fields. In a traditional star schema, dimensions are stored in dimension tables and referenced via foreign keys—a design that reduces storage but requires joins at query time. In DataWorks, you can add frequently used fields (such as user IDs or common analytical dimensions) directly to the aggregate table as redundant fields, improving query performance, simplifying data retrieval, and reducing joins. In the Actions column, click Redundant Field to configure the associated fields.

   

4. Set the association type for each field. The association type specifies the statistical role of a field's values. The available types are: After setting the association type, specify the associated object in the Field Association section in the upper-right corner.

   • Statistical Granularity: links the field to a dimension table (for example, a product dimension or seller dimension)

   • Derived/Composite Metric: specifies the metric consolidated in this field (for example, total payment amount for orders in the last 7 days)

   • Atomic Metric: specifies the base metric for the field (for example, payment amount for a single order)

   Fields imported from tables or added in Script Mode have no default association type—set it manually.

5. Click Save in the upper-left corner.

What's next

After creating and configuring the aggregate table, publish it to the target environment.

• Materialize a logical model

• Generate ETL code from a model

• Model management and application