Create an aggregate table

Procedure

1. Go to the Data Modeling page.
2. Create an aggregate table.
   1. On the Dimension Modeling page, move the pointer over the icon and click Create Aggregate Table.
   2. Configure the basic information of the aggregate table.
      

      Parameter	Description	Example
      Data Layer	The data layer that stores the aggregate table. DWS that belongs to the common layer category is selected by default. You can also store the aggregate table at another data layer based on your business requirements. For more information about how to create a data layer, see Create a data layer.	DWS layer
      Business Category	The business category that you create. For more information, see Business category.	Sales
      Data Domain	The data domain to which the aggregate table belongs. The data domain determines the subject of statistical data in the aggregate table after data aggregation. For more information about how to create a data domain, see Data domain. Note Each aggregate table can belong to only one data domain.	Transaction
      Period	The time range of statistical data that you want to aggregate. Examples: last day and last week. You can select a period from existing periods. If the existing periods do not meet your business requirements, you can create a period. For more information about how to create a period, see Period.	Last week
      Modifier	The business scope of the statistical data that you want to aggregate. You can select one or more modifiers from existing modifiers. If existing modifiers do not meet your business requirements, you can create a modifier. For more information about how to create a modifier, see Modifier.	Online shop
      Granularity	The granularity of the statistical data that you want to aggregate.	None
      Naming Rule	The checker that is used to check whether the name of the aggregate table conforms to the configured naming conventions. You can select the checker that you created for each data layer during data warehouse planning. For more information, see Configure a data layer checker.	None
      Name	The name of the aggregate table. We recommend that you specify the name in the following format: dws_{Data domain}_{Statistical dimension}_{Custom table name tag}_{Period}. This way, you can easily understand the statistical content of the aggregate table. The name can contain lowercase letters, digits, and underscores (_), and must start with a lowercase letter.	Example: dws_trade_buyer_subpay_1d. You can obtain the following information from the name: The data domain is trade. The statistical dimension is buyer. The custom table name tag is subpay. The period is 1d.
      Display Name	The display name of the aggregate table. The display name can contain letters, digits, underscores (_), ampersands (&), and parentheses (). It must start with a letter or a digit. The display name can be a maximum of 256 characters in length.	Phased payments for transactions
      Lifecycle	The lifecycle of the aggregate table. Unit: day.	90
      Description	The description of the aggregate table.	None
      Table Type	The type of the aggregate table. Valid values: Single-granularity Aggregate Table: This type of aggregate table can be used to aggregate data only at a single granularity. For example, you can execute the following statement to create a single-granularity aggregate table named dws_demo_2: create table dws_demo_2 ( shop_code comment 'Dimension: shop code', pay_amt comment 'Metric: sales amount of the shop', pay_cnt comment 'Metric: number of orders in the shop' ) comment 'Single-granularity Aggregate Table'; To collect the metric data at a specific granularity, such as the shop granularity, you need only to use the SELECT syntax to query the desired metric field. You cannot use a single-granularity aggregate table to query data of multiple statistic granularities by using combinatorial logic. Multi-granularity Aggregate Table: This type of aggregate table can be used to aggregate data at multiple granularities and aggregate data at different granularities. For example, you can execute the following statement to create a multi-granularity aggregate table named dws_demo_1: create table dws_demo_1 ( merchant_code comment 'Dimension: merchant code', company_code comment 'Dimension: subsidiary company code', shop_code comment 'Dimension: shop code', sku_code comment 'Dimension: SKU code', pay_type comment 'Dimension: payment type', pay_amt comment 'Metric: sales amount', pay_cnt comment 'Metric: number of orders' ) comment 'Multi-granularity Aggregate Table'; You can use the GROUP BY syntax to generate combinations of derived metrics at different granularities for statistical aggregation. Execute the group by merchant_code statement to collect the metric data at the merchant granularity. Execute the group by merchant_code,company_code,shop_code statement to collect the metric data at the shop granularity.	Single-granularity Aggregate Table

3. Click Save in the upper part of the configuration tab to save the basic information of the aggregate table.