Skip to Content

Using an Aggregate Window in SAP HANA Smart Data Streaming

Previous

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.

Details


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.

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

    connector
  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
  6. Click Select All or you can press Alt+s. Uncheck DEVICE_EVENTS.EVENT_NAME, DEVICE_EVENTS.EVENT_DESCRIPTION and DEVICE_EVENTS.MACHINETYPE. Click OK.

    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

Next
Back to top