DataWorks:Create a logical model: aggregate table

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. On the Data Modeling page, click Dimensional Modeling in the top menu bar to open the Dimensional Modeling page.

3. Create an 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 value
      Data Layer	The data layer where the aggregate table is placed. By default, the Common Layer of the DWS is selected. You can select another data layer based on business needs. For information about creating a data layer, see Define data warehouse layers.	Summary data layer
      Business Category	Select a business category that has been created. For more information, see Business category.	Sales
      Data Domain	Select the data domain for the aggregate table. This determines the statistical subject that the table integrates and presents. For information about creating a data domain, see Data domain. Note Each aggregate table can belong to only one data domain.	Transactions domain
      Granularity	Select a dimension that has been created. For information about creating a dimension, see Create a conceptual model: dimension.	Order type
      Period	Specify the time range of the statistics integrated into the aggregate table, such as the last day or week. Select from existing time periods. If none meets your business needs, see Period to create one.	1w (last 7 days)
      Modifier	Specify the business scope of the statistics. Select from existing modifiers. If none meets your business needs, see Modifiers to create one.	Online store
      Naming Rule	Select a checker for table naming rules. You can select a checker created for a data layer during data warehouse planning. For instructions, see Configure data warehouse layer checkers, Use checkers.	-
      Table Name	Name of the aggregate table. If a table naming rule is configured, the name must follow that rule.	dws_trade_buyer_subpay_1d
      Table Display Name	Chinese display name of the table.	Buyer transaction staged payment aggregate table
      Lifecycle	Table retention period, in days.	90 days
      Owner	Owner of the aggregate table. The user who creates the table is selected by default.	-
      Description	Description of the table.	-

4. Click Save in the upper-left corner to save the aggregate table settings.

Add table fields

You can add fields to the table by using Shortcut Mode or Script Mode. The Shortcut Mode option supports the following import methods:

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

  Note

  Currently, fields can be imported only from tables or views in MaxCompute, Hologres, or EMR Hive.

• Import from Metrics: Select derived metrics from Data Metrics as model fields.

Set table field information

The fields required by the model have now been added. Set their Associated Field, Redundant Field, Associated Granularity/Metric.

1. Set field attributes.

   By default, basic attributes such as Field Name, Type, Field Display Name, Description, Primary Key, Not Null, Measurement Unit, Actions are displayed. Click Field Display Settings in the upper-right corner of the added fields to choose which field attributes to display and modify them as needed.

2. Set the field's Field Standard to Associate.

   to associate an added field with a field standard and standardize its allowed values and range.

   Field Standard to Associate: Standardize data with the same meaning but different field names, including its allowed values and units of measurement.

3. Set the field's Redundant Field.

   In a traditional star schema, dimensions are stored in dimension tables and accessed through foreign keys in fact tables to reduce storage. In DataWorks Intelligent Data Modeling, frequently used fields, such as user IDs and common analysis dimensions, can instead be stored redundantly to improve downstream query efficiency, simplify data access, and reduce joins.

   In the Actions column of an added field, click Redundant Field to set its associated field.

   

4. Set the field's Association Type.

   For each field in an aggregate or application table, set its Association Type to specify the statistical type of its value. The options are Statistical Granularity, Derived/Composite Metric and Atomic Metric three types.

   • Statistical Granularity: Associate the field with a dimension table and one of its fields, such as product or merchant dimension.

   • Derived/Composite Metric: Specify a derived or composite metric whose statistical value the aggregate table field integrates, such as the order payment amount in Hema App over the last seven days.

   • Atomic Metric: Specify an atomic metric whose statistical value the aggregate table field integrates, such as order payment amount.

   Note

   Fields imported from a table or added in code mode have no default association type. You can set one manually.

   After setting the type, use Field Association in the upper-right corner of the field list to specify the associated object.

5. After configuring the settings, click Save.