DataWorks:Logical model: Aggregate table

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 to go to the Dimensional Modeling page.

3. Create the aggregate table.

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

   2. Configure the basic information for the aggregate table.

      

      Parameter	Description	Example
      Data Layer	The data layer of the aggregate table. By default, the DWS layer in the Common Layer is selected. You can also place the aggregate table in other data layers based on your business needs. For more information about how to create data layers, see Define data warehouse layers.	DWS
      Business Category	Select a business category. For more information, see Business Category.	Sales
      Data Domain	The data domain of the aggregate table, which determines the subject of the consolidated statistical data. For more information, see Data Domain. Note Each aggregate table can belong to only one data domain.	Trade
      Granularity	Select an existing dimension. For information about how to create a dimension, see Create a conceptual model: Dimension.	Order Type
      Period	The time range for the consolidated statistical data, such as the last day or the last week. You must select from existing periods. If no existing period meets your needs, you can create one. For more information, see Period.	1w (Last 7 Days)
      Modifiers	The business scope of the statistical data. You must select from existing modifiers. If no existing modifier meets your needs, you can create one. For more information, see Modifiers.	Online Store
      Naming Rule	Select a checker to enforce table naming conventions. You can select a checker that you created in a data layer 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 table name must comply with the rule.	dws_trade_buyer_subpay_1d
      Display Name	The display name of the table.	Buyer Trade Installment Payment Aggregate Table
      Lifecycle	The lifecycle of the table, in days.	90 days
      Owner	The owner of the aggregate table. By default, this is the user who created the table.	-
      Description	The description of the table.	-

4. In the upper-left corner, click Save.

Add fields to the table

You can add fields to the table using Shortcut Mode or Script Mode. In Shortcut Mode, you can use the following import methods:

• Import from Table/View: Import fields from an existing physical table or view in the compute engine. From the Search For Existing Table/View drop-down list, search for and select a table or view to import its fields.

  Note

  Only tables or views from MaxCompute, Hologres, or E-MapReduce (EMR) Hive engines are supported.

• Import from Metrics: Select metrics from Data Metrics to add them as model fields.

Configure field information

After adding fields to the model, you can configure their properties, such as Associated Field, Redundant Field, and Associated Granularity/Metric.

1. Configure field properties.

   By default, the field list displays basic properties such as Field Name, Type, Field Display Name, Description, Primary Key, Not Empty, Measurement Unit, and Actions. In the upper-right corner of the field list, click Field Display Settings to select which field properties to display and modify them as needed.

2. Associate fields with a field standard.

   You can associate a field with a field standard to standardize its content and value range.

   A Field Standard uniformly manages data that has the same meaning but different field names, and defines value ranges, measurement units, and more.

3. Configure Redundant Field.

   In a traditional star schema for dimensional modeling, dimensions are stored in dimension tables and accessed through foreign keys in the fact table to reduce storage consumption. In DataWorks smart data modeling, you can add frequently used fields (such as user IDs or common analytical dimensions) as redundant fields. This approach improves query performance, simplifies data retrieval, and reduces table joins.

   In the Actions column of a field, click Redundant Field to set the field's associated fields.

   

4. Configure the Association Type.

   You can set the Association Type for each field to specify the statistical type for its values in aggregate and application tables. The available types are Statistical Granularity, Derived/Composite Metric, and Atomic Metric.

   • Statistical Granularity: Associates the field with a dimension table and fields within that dimension table, such as a product dimension or a seller dimension.

   • Derived/Composite Metric: Specifies the metric for the statistical value to be consolidated in the field. For example, the total payment amount for orders placed on the Freshippo app in the last 7 days.

   • Atomic Metric: Specifies the atomic metric for the statistical value to be consolidated in the field. For example, the payment amount for an order.

   Note

   Fields imported from tables or added in Script Mode do not have a default association type. You can manually set the association type for these fields.

   After you set the association type, you can specify the associated object in the Field Association section in the upper-right corner of the field list.

5. After you finish the configuration, click Save in the upper-left corner.