You can use an aggregate table to store statistical data of multiple derived metrics that have the same period and dimension in a data domain. An aggregate table can be used for subsequent business query, online analytical processing (OLAP) analysis, and data distribution. This topic describes how to create an aggregate table.

Prerequisites

  • A data layer is created. You can store tables that are used for the same purpose at the same data layer. This helps you easily find and use tables. In most cases, an aggregate table is stored at the data warehouse summary (DWS) layer and contains statistical data of multiple derived metrics in a dimension or a set of dimensions. The statistical data can be used for subsequent business query and data distribution. You can also store aggregate tables at another data layer based on your business requirements. For more information about how to create a data layer, see Create a data layer.
  • A data domain is created. An aggregate table is created based on a data domain. The data domain determines the business category based on which you want to build a model and is used to bear business processes. For more information about how to create a data domain, see Data domain.
  • A period is created to determine the time range of which you want to aggregate statistical data. For more information, see Period.

Background information

An aggregate table integrates multiple derived metrics in a data domain based on the period and associated dimension that you specified. The associated dimension, period, and derived metrics are used to generate statistical fields in the aggregate table to help you analyze and generate reports. You can use an aggregate table to display the status of your business based on the statistics of multiple derived metrics that have the same period and same dimension.

Procedure

  1. Go to the Dimensional Modeling page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region in which the desired workspace resides. On the Workspaces page, find the workspace and click DataStudio in the Actions column.
    4. In the upper-left corner of the DataStudio page, click the Icon icon and choose All Products > Data Modeling > Dimensional Modeling.
  2. Create an aggregate table.
    1. In the Dimension Modeling pane, move the pointer over the Plus sign icon and click Create Aggregate Table.
    2. Configure the basic information of the aggregate table.
      Basic Information
      ParameterDescriptionExample
      Data LayerThe 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 CategoryThe business category that you create. For more information, see Business category. Sales
      Data DomainThe 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
      PeriodThe 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
      ModifierThe 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
      Naming RuleThe 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 at each data layer during data warehouse planning. For more information, see Configure and use a checker at a data layer. None
      NameThe name of the aggregate table. If you configure a naming convention, the name that you specify must confirm to the naming convention. dws_trade_buyer_subpay_1d
      Display NameThe display name of the aggregate table. Phased payments for transactions
      LifecycleThe lifecycle of the aggregate table. Unit: day. 90
      OwnerThe owner of the aggregate table. The default owner is the creator of the aggregate table. None
      DescriptionThe description of the aggregate table. None
      Table TypeThe type of the aggregate table. You can select one of the following table types:
      • 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 placed in the shop'
        ) comment 'Single-granularity Aggregate Table';
        To collect the metric data at a specific granularity, such as the shop granularity, you need to only 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: commodity 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.

What to do next

After the aggregate table is created, you must configure fields, associations, and partitions for the table, and publish the table to the required environment. For more information, see Publish and manage a table.