AGGREGATIONONLY

AGGREGATIONONLY is a table type in dataset definitions, designed to store only aggregated data.

It ensures that the dataset contains a single row of summarized values rather than raw transactional records. Each column must use an aggregation function, such as SUM or AVG, to derive its values.

Key Features
  • No Primary Key Required : Aggregation-only datasets contain a single row of summarized data, eliminating the need for a primary key.
  • Mandatory Aggregation: Columns in these datasets must use aggregation functions (Example SUM(), AVG()).
  • No Group-By Allowed: If GROUPBY is specified, the dataset is treated as a regular dataset instead of aggregation-only.
Example
DEFINE AGGREGATIONONLY DATASET SALES_AGG
 ROWSOURCE SALES WHERE SALES.QUANTITY_SOLD > 10 ; 
 THIS[AVG_SALES_AMT] = AVG(SALES[AMOUNT_SOLD]); 
END

Multiple Aggregated Metrics

An aggregation-only dataset can store multiple metrics, such as Average, Sum, and Min.

Example: 
DEFINE AGGREGATIONONLY DATASET SALES_AGG1 
    ROWSOURCE SALES WHERE SALES.QUANTITY_SOLD > 10; 
    THIS[AVG_SALES_AMT] = AVG(SALES[AMOUNT_SOLD]); 
    THIS[SUM_SALES_AMT] = SUM(SALES[AMOUNT_SOLD]); 
    THIS[MIN_SALES_AMT] = MIN(SALES[AMOUNT_SOLD]); 
    THIS[MAX_SALES_AMT] = MAX(SALES[AMOUNT_SOLD]); 
END

Full and Incremental Loads in Aggregation-Only Datasets

Aggregation-only datasets support code-block-load (both full loads and incremental loads). See Code Block Load - Full and Incremental Load Instructions.

Example:
DEFINE AGGREGATIONONLY DATASET SALES_AGG2
	ON FULL LOAD 
 		ROWSOURCE SALES WHERE SALES.QUANTITY_SOLD > 20; 
 		THIS[AVG_SALES_AMT] = AVG(SALES[AMOUNT_SOLD]); 
 		THIS[SUM_SALES_AMT] = SUM(SALES[AMOUNT_SOLD]); 
 		THIS[MIN_SALES_AMT] = MIN(SALES[AMOUNT_SOLD]); 
 		THIS[MAX_SALES_AMT] = MAX(SALES[AMOUNT_SOLD]); 
 	INCREMENTAL LOAD 
 		ROWSOURCE SALES WHERE SALES.QUANTITY_SOLD > 10; 
 		THIS[AVG_SALES_AMT] = AVG(SALES[AMOUNT_SOLD]); 
		THIS[SUM_SALES_AMT] = SUM(SALES[AMOUNT_SOLD]); 
 		THIS[MIN_SALES_AMT] = MIN(SALES[AMOUNT_SOLD]); 
 		THIS[MAX_SALES_AMT] = MAX(SALES[AMOUNT_SOLD]); 
	 ENDLOAD 
END
In this example:
  • Full Load uses SALES with QUANTITY_SOLD > 20, where all the records are truncated and reloaded.
  • Incremental Load uses SALES, with QUANTITY_SOLD > 10.

Derived Aggregation-Only Datasets

To simplify calculations, you can derive aggregation-only datasets from other aggregation-only datasets.

Example:
DEFINE AGGREGATIONONLY DATASET SALES_AGG3
 ROWSOURCE SALES_AGG2;
 THIS = SALES_AGG2.AVG_SALES_AMT;
END

Note:

  • You must mark a dataset that;s created exclusively from an aggregation-only source as AGGREGATIONONLY.
  • CROSS JOIN is the only join type allowed with aggregation-only inputs.
  • The Refresh On Changes directive is not allowed on aggregation-only dataset.

Joining Aggregation-Only Datasets with Transactional Data

You can combine aggregation-only datasets with transactional data using CROSS JOIN.

Example: 
DEFINE DATASET SALES_AGG4 
 ROWSOURCE SALES CROSS JOIN SALES_AGG_DERIVED WHERE SALES.QUANTITY_SOLD > 10; 
 THIS = SALES_AGG_DERIVED; 
 THIS = SALES; 
 PRIMARYKEY[PROD_ID,CHANNEL_ID,CUST_ID,TIME_ID,PROMO_ID]; 
 REFRESH ON CHANGES IN[SALES]; 
END

Note:

  • CROSS JOIN is the only join type allowed with aggregation-only inputs.
  • The Refresh On Changes directive is not allowed on aggregation-only dataset.