Skip to Content

Using an Aggregate Window in SAP HANA Smart Data Streaming

Part 5 of 9. Add an aggregate window to hold data for pattern observation and trend monitoring.
You will learn
  • How to add aggregate window to hold multiple data entry and observe patterns
  • How to use time-based sliding event window to compute metrics and monitor trends
  • Step 1
    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

    How many columns does your aggregate window have?

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

      click events
    2. Click the Keep Policy menu item to execute it. You can also press k.

      keep policy
    3. Click Time and enter 30 seconds in the entry box. Click OK.

      policy edit
    4. To define the GROUP BY clause, expand the tab by clicking on +.

      expand group
    5. Double-click on GROUP BY unassigned_group_by.

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

      group criteria
    7. 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.

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

      click group filter clause
    9. Double-click on Group Filter 1.

      rename group filter
    10. 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
  • Step 3
    1. Expand the Column Expressions tab to edit expressions.

      go to column expression
    2. Double-click on DEVICE_EVENTS.EVENT_TIME.

      change event time
    3. 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
    4. 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
    5. Double-click on the expression for AVG_TEMP, which is currently set to DEVICE_EVENTS.EVENT_VALUE.

      edit event value
    6. 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
Back to top