Using aggregate functions in a map expression in Compose
Hello,
I am trying to use a simple sum() function in one of my Compose maps as an expression. When I go to the Functions tab in the "Create Expressions" menu in the map, I found the following:
Hovering over this, it says it can be used as an aggregate or an analytic function. How do I use this as an aggregate function? Where do I put my group by clause? Where do I put the field name I want to return the sum of?
The aggregate functions presented in Compose expression editors (for standard mappings to the DW model) are know as Window-ed analytic functions / windowed aggregates. Windowed analytical functions do not leverage a GROUP BY as they allow you to provide a set of data lower than the aggregate grain (the group by).
A simple example from a query perspective would be a requirement to display all sales, with a sum of the total revenue for the entire day..
e.g. - Select Date, Product, Acct, Qty, SUM(Quantity) OVER (Partition by Date) as DailyTotal from X
In this instance the PARTITION BY clause provides the WINDOW (acts similar to a group by) - but it is over the entire set of data. The data is not provided at the [DATE] grain, but the aggregate is.
I recommend looking in the documentation for your data warehouse platform for Windowed Aggregates to understand what features are available in the dw platform as Compose will adhere / support what the platform provides.
If you're goal is to have a "simple" aggregate, Compose provides aggregated fact table support. (It is always a best practice in a DW to store data at the lowest granularity in the modeled layer and aggregate in the data marts). Having said that - there are commonly requirements to calculate and store an aggregate. If you are processing in batch, and wish to store / calculate an aggregate it is recommended to use query as the source for the mapping.
