Data Layer |
The data layer that stores the aggregate table. DWS is selected by default. You can
also store aggregate tables at other data layers based on your business requirements.
For more information about how to create a data layer, see Create a data layer.
|
DWS layer |
Business Category |
Set the parameter to the created business category. For more information, see Business category.
|
Sales |
Data Domain |
Set the parameter to the data domain of the aggregate table. The data domain determines
the theme of the aggregate table after data aggregation.
Note Each aggregate table can belong to only one data domain.
|
Transaction |
Period |
The period during which the statistical data that is to be aggregated is generated.
Examples: last day and last week.
Select a period from the 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 |
Naming Rule |
Set the parameter to the checker that is used to check whether the names of tables
conform to the 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. Naming rules:
The display name can be up to 256 characters in length, and can contain letters, digits,
underscores (_), ampersands (&), and parentheses (). It must start with a letter or
a digit.
|
Phased payments for transactions |
Lifecycle |
The lifecycle of the aggregate table. Unit: day. |
90 days |
Description |
The description of the aggregate table. |
None |
Table Type |
Select a table type from the following options based on your business requirements:
- Single-granularity Aggregate Table: You can collect data only at a single granularity, and cannot aggregate data at
multiple granularities again.
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 single-granularity aggregate
tables to query data of multiple statistical granularities by using combinatorial
logic.
- Multi-granularity Aggregate Table: You can collect data at multiple granularities and aggregate data at different granularities
again.
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 |