Create a logical model: Aggregate 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 an aggregate 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 menu bar of the Data Modeling page, click Dimensional Modeling to go to the Dimensional Modeling page.

3. Create an aggregate table.

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

   2. Configure the basic information for the aggregate table.

      

      Parameter	Description	Example
      Data Layer	The data warehouse layer where the aggregate table resides. By default, the Common Layer in the DWS is selected. You can also place the aggregate table in another data warehouse layer based on your business requirements. For more information, see Define data warehouse layers.	DWS layer
      Business Category	Select an existing business category. For more information, see business category.	Sales
      Data Domain	Select the data domain for the aggregate table. This determines the business subject of the data to be aggregated and presented. For more information, see data domain. Note An aggregate table can belong to only one data domain.	Trade
      Granularity	Select an existing dimension. For more information, see Create a conceptual model: Dimension.	Order type
      Period	Specifies the time range for the statistical values to be aggregated into the table, such as the last day or the last week. You must select an existing period. If no existing period meets your business needs, you can create a new one. For more information, see period.	1w (Last 7 days)
      Modifier	Defines the business scope of the statistical data. You must select an existing modifier. If no existing modifier meets your business needs, you can create a new one. For more information, see modifier.	Online store
      Naming Rule	Select a checker to validate the table naming rule. You can choose from the checkers created for each data warehouse layer during the planning stage. For more information, see Configure data warehouse layer checkers and Use checkers.	-
      Table Name	The name of the aggregate table. If a naming rule is configured, the table name must conform to the specified rule.	dws_trade_buyer_subpay_1d
      Table Display Name	The display name of the table.	Buyer Transaction Phased 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	A description of the table.	-

4. Click Save in the upper-left corner.

Add table fields

You can add fields to the table in Shortcut Mode or Script Mode. Shortcut Mode supports the following import methods:

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

  Note

  Currently, you can import fields only from tables or views in MaxCompute, Hologres, and EMR Hive.

• Import from Metrics: Select the required derived and composite metrics to use them as model fields.

Configure table fields

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

1. Configure field properties.

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

2. Set the Field Standard to Associate.

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

   The Field Standard to Associate defines content such as value ranges and measurement units to uniformly manage data that has the same meaning but different field names.

3. Set a Redundant Field.

   In a traditional dimensional modeling star schema, dimensions are stored in dimension tables and accessed through foreign keys in fact tables to reduce storage consumption. In the Dimensional Modeling feature of DataWorks, you can set frequently used fields, such as user IDs and common analysis dimensions, as redundant fields. This practice improves downstream query efficiency, simplifies data access, and reduces the number of table joins.

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

   

4. Set the Association Type.

   For aggregate tables and application tables, you can specify the statistical type for the value of each field by setting the Association Type. The options are Statistical Granularity, Derived/Composite Metric, and Atomic Metric.

   • Statistical Granularity: Associates the field with a dimension, such as a product or seller dimension.

   • Derived/Composite Metric: Specifies the derived or composite metric for the statistical value to be aggregated into the table field. For example, the total payment amount for orders placed on the Hema app in the last 7 days.

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

   Note

   Fields imported from a table or added in script mode do not have a default association type. You must manually set the association type for these fields.

   After the configuration is complete, you can click Field Association in the upper-right corner of the field list to specify the object that a field is associated with.

5. After you complete the settings, click Save in the upper-left corner.