Aggregation Type (Term Mapping)

Aggregation Type sets an aggregate function, or Group By, on the term mapping of a target term. Use it when a dataflow set must deliver one row per group, for example sales per supplier.

Where to set it

Set Aggregation Type when you edit a term mapping.

Values and default

• - (default): no aggregation.
• Group By: the term is part of the GROUP BY clause.
• Sum, Avg, Min, Max, Count, Distinct Count: the mapped expression is wrapped in that function.
• Expression: the mapped expression contains the aggregate functions itself and is used as written. See Expression.

Rules

• When one term mapping of a dataflow set has an Aggregation Type, every mapped term of that set needs one. See Dataflow Sets Aggregation Amount (Business Rule).
• A dataflow with a multi-version load cannot aggregate. See Aggregation with multi-version load (Business Rule).
• Mart Dimension and Mart Hierarchy Flatten accept no aggregation.

Expression

Use Expression when a target term needs a calculation across several aggregates, or an aggregate function that the list does not offer:

• Ratios and weighted averages, for example sum(s1.SALES_AMOUNT) / NULLIF(sum(s1.QUANTITY), 0).
• Conditional totals, for example sum(CASE WHEN s1.QUANTITY > 0 THEN 1 ELSE 0 END).
• Aggregate functions of the platform, for example STRING_AGG on Microsoft SQL Server.

The generator writes the expression into the generated statement unchanged. It adds no aggregate function and leaves the term out of the GROUP BY clause. For the Dataflow Sets Aggregation Amount rule, the term counts as aggregated. The generator does not check whether the expression is valid in an aggregated query.

Example

A Mart Fact on Microsoft SQL Server delivers sales per supplier. Its dataflow set maps these terms; s1 is the alias of the sales source, and FK_..._ITEM_ID stands for the full name of the foreign key term:

• FK_..._ITEM_ID, filled from SUPPLIER_ID: Group By
• SALES_AMOUNT, mapped to s1.SALES_AMOUNT: Sum
• AVG_UNIT_AMOUNT, mapped to sum(s1.SALES_AMOUNT) / NULLIF(sum(s1.QUANTITY), 0): Expression

The generated source view, shortened:

SELECT
     CAST([s3].[SUPPLIER_ID] AS DECIMAL(38)) AS [FK_..._ITEM_ID]
    ,SUM([s1].[SALES_AMOUNT]) AS [SALES_AMOUNT]
    ,sum([s1].[SALES_AMOUNT]) / NULLIF(sum([s1].[QUANTITY]), 0)
        AS [AVG_UNIT_AMOUNT]
FROM ...
GROUP BY
     CAST([s3].[SUPPLIER_ID] AS DECIMAL(38))

Sum wraps SALES_AMOUNT in SUM(). The expression of AVG_UNIT_AMOUNT appears as written, with its lower-case sum. Only the term with Group By is in the GROUP BY clause.

Related articles

• Reference: Dataflow Sets Aggregation Amount (Business Rule), Aggregation with multi-version load (Business Rule)
• See also: Edit a Term Mapping, Mart Fact