Service Level Agreement

<< Click to Display Table of Contents >>

Navigation:  Analyzing Data in s-Server > Query Patterns >

Service Level Agreement

Previous pageReturn to chapter overviewNext page

A common requirement is to report on the success or failure of business processes to meet service level agreements (SLAs). This section presents a pair of examples.

Finding cases that meet a time-based SLA

Assuming two streams orders (ROWTIME, id, city, ...) and shipments (ROWTIME, order_id, ...) the following query will show all those orders intended for New York which were successfully shipped within an hour:

CREATE VIEW compliant_orders AS

 SELECT STREAM *

  FROM orders OVER sla

  JOIN shipments

  ON orders.id = shipments.orderid

  WHERE city = 'New York'

  WINDOW sla AS (RANGE INTERVAL '1' HOUR PRECEDING)

 

The "triggering" event which forces the emission of the result is the shipment (which is not windowed).

Identifying SLA failures

A simple approach is to report on orders that have been shipped but which have missed the deadline. In this example we find all orders shipped between 1 and 24 hours after order:

CREATE VIEW late_shipments AS

 SELECT STREAM *

  FROM orders OVER sla

  JOIN shipments

  ON orders.id = shipments.orderid

  WHERE city = 'New York'

  AND   shipments.ROWTIME - orders.ROWTIME > INTERVAL '1' HOUR

  WINDOW sla AS (RANGE INTERVAL '1' DAY PRECEDING)

 

This formulation does not capture orders which were shipped in more than one day, or have not been shipped yet. To do that we can create a stream view of "SLA expiry" events:

CREATE VIEW orders_sla AS

 SELECT STREAM o.ROWTIME + INTERVAL '1' HOUR AS ROWTIME

      , o.*

  FROM orders o

 

Now we can find those orders for which no shipment has been received in the hour between the order being logged and the expiry of the SLA period. Note that the "triggering event" is now the orders_sla, and the filter on s.orderid IS NULL restricts the output to only those orders which fail the SLA:

CREATE VIEW non_compliant_orders AS

 SELECT STREAM *

  FROM orders_sla AS os

  OUTER JOIN shipments s over sla

  ON os.id = s.orderid

  WHERE city = 'New York'

  AND   s.orderid IS NULL

  WINDOW sla AS (RANGE INTERVAL '1' HOUR PRECEDING)

CREATE VIEW orders_and_shipments AS

 SELECT STREAM os.id, os.cust_id, 1 as net

  FROM orders_sla AS os

 UNION

 SELECT STREAM s.order_id, s.cust_id, -1 as net

  FROM shipments s

CREATE VIEW non_compliant_orders AS

 SELECT STREAM id, cust_id, SUM(net)

 FROM orders_and_shipments as B

    GROUP BY FLOOR(B.ROWTIME TO HOUR),id, cust_id

    HAVING SUM(net) > 0

 

The condition SUM(net)>0 means the order hasn't shipped.