Striim 3.9.4 / 3.9.5 documentation

Step 4: process and enhance the data
posapp_10.png

The PosData5Minutes window sends each five-minute set of data to the GenerateMerchantTxRateOnly query. As you can see from the following schema diagram, this query is fairly complex:

311GenerateMerchantTxRateOnly.png

The GenerateMerchantTxRateOnly query combines data from the PosData5Minutes event stream with data from the HourlyAveLookup cache. A cache is similar to a source, except that the data is static rather than real-time. In the real world, this data would come from a periodically updated table in the payment processor's system containing historical averages of the number of transactions processed for each merchant for each hour of each day of the week (168 values per merchant). In this sample application, the source is a file, hourlyData.txt, which to simplify the sample data set has only 24 values per merchant, one for each hour in the day.

For each five-minute set of events received from the PosData5Minutes window, the GenerateMerchantTxRateOnly query ouputs one event for each merchantID found in the set to MerchantTxRateOnlyStream, which applies the MerchantTxRate type. The easiest way to summarize what is happening in the above diagram is to describe where each of the fields in the MerchantTxRateOnlySteam comes from:

field

description

TQL

merchantId

the merchantID field from PosData5Minutes

SELECT p.merchantID

zip

the zip field from PosData5Minutes

SELECT ... p.zip

startTime

the dateTime field for the first event for the merchantID in the five-minute set from PosData5Minutes

SELECT ... FIRST(p.dateTime)

count

count of events for the merchantID in the five-minute set from PosData5Minutes

SELECT ... COUNT(p.merchantID)

totalAmount

sum of amount field values for the merchantID in the five-minute set from PosData5Minutes

SELECT ... SUM(p.amount)

hourlyAve

the hourlyAve value for the current hour from HourlyAveLookup, divided by 12 to give the five-minute average

SELECT … l.hourlyAve / 12 ...
  WHERE ...p.hourValue = l.hourValue

upperLimit

the hourlyAve value for the current hour from HourlyAveLookup, divided by 12, then multiplied by 1.15 if the value is 200 or less, 1.2 if the value is between 201 and 800, 1.25 if the value is between 801 and 10,000, or 1.5 if the value is over 10,000

SELECT … l.hourlyAve / 12 * CASE
  WHEN l.hourlyAve / 12 > 10000 THEN 1.15 
  WHEN l.hourlyAve / 12 > 800 THEN 1.2 
  WHEN l.hourlyAve / 12 > 200 THEN 1.25 
  ELSE 1.5 END

lowerLimit

the hourlyAve value for the current hour from HourlyAveLookup, divided by 12, then divided by 1.15 if the value is 200 or less, 1.2 if the value is between 201 and 800, 1.25 if the value is between 801 and 10,000, or 1.5 if the value is over 10,000

SELECT … l.hourlyAve / 12 / CASE
  WHEN l.hourlyAve / 12 > 10000 THEN 1.15 
  WHEN l.hourlyAve / 12 > 800 THEN 1.2 
  WHEN l.hourlyAve / 12 > 200 THEN 1.25
  ELSE 1.5 END

category, status

placeholders for values to be added

SELECT ... '<NOTSET>'

The MerchantTxRateOnlyStream passes this output to the GenerateMerchantTxRateWithStatus query, which populates the category and status fields by evaluating the count, upperLimit, and lowerLimit fields:

SELECT merchantId,
  zip,
  startTime,
  count,
  totalAmount,
  hourlyAve,
  upperLimit,
  lowerLimit,
    CASE
      WHEN count > 10000 THEN 'HOT'
      WHEN count > 800 THEN 'WARM'
      WHEN count > 200 THEN 'COOL'
      ELSE 'COLD' END,
    CASE
      WHEN count > upperLimit THEN 'TOOHIGH'
      WHEN count < lowerLimit THEN 'TOOLOW'
      ELSE 'OK' END
FROM MerchantTxRateOnlyStream

The category values are used by the Dashboard to color-code the map points. The status values are used by the GenerateAlerts query.

The output from the GenerateMerchantTxRateWithStatus query goes to MerchantTxRateWithStatusStream.