Rolling Up Data with the Aggregate Transform
Do you have a large amount of data that you want to roll up to a different granularity? The Aggregate Transform enables you to essentially apply a GROUP BY statement on data that are entering it. Aggregate Transforms are one of the more expensive operations you can perform against data, much like a GROUP BY statement is in T-SQL, and they can be very memory intensive.
Once you drag the transform over, simply check the columns in the Aggregations tab that you want to aggregate or sum. The Operation drop-down box enables you to select what type of aggregation function you want to apply to the data. The most important operation is a Group By operation, which enables you to roll the data up to that grain. For example, if you have a dozen sales of three products, and you grouped them by the ProductID, you’d have only three rows come out of the transform. You can see a list of all the operations allowed in the following table.
|Data Type||Operations Allowed|
|String||Group by, Count, Count distinct|
|Numeric||Group by, Count, Count distinct, Minimum, Maximum|
|Date||Group by, ...|