SAP Data Services 4.x Cookbook

Using aggregate functions

Aggregate functions are used in dataflow Query transforms to perform aggregation on the grouped dataset.

You should be familiar with these functions as they are the same ones used in the SQL language: avg(), min(), max(), count(), count_distinct(), and sum().

How to do it…

To demonstrate the use of aggregate functions, we will perform a simple analysis of one of our tables. Import the DimGeography table into the DWH datastore and create a new job with a single dataflow inside it using these steps:

1. Your dataflow should include the DimGeography source table and the DimGeography target template table in a STAGE database to send the output to:
2. Open the Query transform and create the following output structure:

   The COUNTRYREGIONCODE ...