Skip to Content

Using an Aggregate Window in SAP HANA Smart Data Streaming


Using an Aggregate Window in SAP HANA Smart Data Streaming

By billjiangsap

Part 5 of 9. Add an aggregate window to hold data for pattern observation and trend monitoring.

You will learn

  • Adding aggregate window to hold multiple data entry and observe patterns.
  • Using time-based sliding event window to compute metrics and monitor trends.


Add an Aggregate Window

  1. Click on Aggregate in the Palette and drop onto canvas.

    drop aggregate window

  2. Rename the stream to AVG_TEMP by clicking on Aggregate symbol. Then press the Enter key.


  3. Select the Connector in the Palette. Drag the connector from DEVICE_EVENTS Join to AVG_TEMP.


  4. Click Add Column Expression f(x) by clicking on the icon shown below.

    add column

  5. Click the Copy Columns from Input menu item to execute it. You can also press c.

    copy columns


    select columns to copy

  7. Now we will create a window on the input to this aggregation. Expand the Inputs tab and right click on DEVICE_EVENTS.

    click events

  8. Click the Keep Policy menu item to execute it. You can also press k.

    keep policy

  9. Click Time and enter 30 seconds in the entry box. Click OK.

    policy edit

  10. To define the GROUP BY clause, expand the tab by clicking on +.

    expand group

  11. Double-click on GROUP BY unassigned_group_by.

    group info

  12. Select the entry DEVICE_EVENTS.MACHINEID by clicking on it. Click Add >> and then click OK.

    group criteria

  13. Now we need to add a GROUP filter, since we only want to aggregate temperature readings. Click Add Group Clause { } icon shown below.

    group filter clause

    Note that a GROUP FILTER filters the incoming events before aggregation. In this case, the filter will filter out “DOOR” and “POWER” events so that this element only process “TEMP” events.

  14. Click the Group Filter Clause menu item to execute it. You can also press g.

    click group filter clause

  15. Double-click on Group Filter 1.

    rename group filter

  16. Enter DEVICE_EVENTS.EVENT_NAME='TEMP' as the filter expression in the text box. You can use Ctrl+Space for content assist. Confirm your entry by pressing Enter.

    name group filter

  17. Expand the Column Expressions tab to edit expressions.

    go to column expression

  18. Double-click on DEVICE_EVENTS.EVENT_TIME.

    change event time

  19. Edit the expression for EVENT_TIME. Change it to: last(DEVICE_EVENTS.EVENT_TIME). This will cause the aggregate values for the group to show the event time of the last event received in the group. Confirm your entry by pressing Enter.

    last event time

  20. Double click the name EVENT_VALUE and rename this column to AVG_TEMP by typing in the text field. Confirm your entry by pressing Enter.

    20-change event value name

  21. Double-click on the expression for AVG_TEMP, which is currently set to DEVICE_EVENTS.EVENT_VALUE.

    edit event value

  22. Edit this expression to compute an average. Also, since the value field is a string, before we can compute an average, we need to convert it to a number. Change the expression to: avg(to_decimal(DEVICE_EVENTS.EVENT_VALUE, 4, 2)). Confirm your entry by pressing Enter.

    average event value

Next Steps

Back to top