Merging Batches, Inspections and Process Data

One of the most powerful uses of Capture is in integrating your batch, inspection and process data into a single table.

To do this, we're going to require three things.

* A source of batch event data (ie. a batch database)
* A source of inspection results (ie. a databases table)
* Real-world sensor values (ie. speeds or temperatures)

We then create an outline of our Capture…

{
   "name": "Batch Quality",
   "uniqueid": "{id}",
    "keys": ["id","flavour","size","mixin"],
    "steps": [
         {
         }
     ]
}

In this case, we know that batches - from the ARDI Bakery demo server - contain several properties, including “flavour”, “size” and “mixin”.

We begin with the first step - loading our batches.

On our Bakery demo server, batches are available as a type of event.

So we use a get_events layer to load the events.

{
   "type": "get_events",
   "source": "Batches",
   "comment": "Load Batches"
}

This loads the events from the 'batches' source.

Next, we need to lookup the inspection details. There's a specific layer designed for this to make it a one-step solution.

{
    "type": "lookup_event",
     "key": "id",
     "rkey": "name",
     "choose": "nearest",
     "source": "Inspections",
     "comment": "Find Inspection",
     "global": true,
     "lead": 3600,
     "lag": 3600
 }

To understand this, it's helpful to know some of the data that is available in the inspections and batches events.

Inspections have a column called 'id', which is the batch number. Batches instead have a column called 'name'.

The layer above uses the attribute from the batch and matches it with the corresponding inspection by checking that the name and id are equal. It then merges the attributes of the two together so that the capture contains all of the information from both the batch and the inspection.

The ''lead' and 'lag' options are used to handle timing issues. Because inspections aren't perfectly synchronised with the actual job being produced (inspections are manually entered by human operators), this amount of seconds (3600 - one hour) of padding are being added to the range of events that are being searched, allowing inspections to happen up to one hour after the job is produced.

The 'global' option tells the system to load the inspections once, reducing the time it takes to process.

Next, we want to remove batches we don't want to record or work with.

{
     "type": "reject",
      "comment": "Remove entries with no inspection",
      "required": ["comment"],
      "action": "ignore"
},
{
       "type": "reject",
       "comment": "Hide failed runs",
       "action": "hide",
       "if": "{success}==0"
},

The first layer outright rejects any batch that doesn't have an inspection. The 'comment' attribute only exists in the inspection event - so batches with no inspection simply won't be recorded.

The next layer hides any batch that didn't pass quality inspection. The batch is still recorded in the database, but it won't be used in any analytics unless you specifically request it.

The final step is to capture the values we really used to produce this particular batch. For example, if we were heat-treating or cooking, we'd record how long and how hot the oven was.

You can do this with a normal AQL query.

 {
       "type": "get_query",
       "points": ["Oven.Temperature","Oven.Speed"],
        "samples": 50,
        "comment": "Get temperatures and speeds"
},
{
        "type": "flatten",
        "method": "middle",
        "comment": "Sample values mid-run"
}

In this case, we choose to flatten at the middle of the sample data to avoid disruptions caused by the system starting up or shutting down at the end of the batch.

The final capture file looks like the one below…

{
	"name": "Batch Quality",
	"uniqueid": "{id}",
	"keys": ["id","flavour","size","mixin"],
	"strings": ["flavour","size","mixin","name"],
	"date": "StartTime",
	"steps": [
		{
			"type": "get_events",
			"source": "Batches",
			"comment": "Get Batch Information"
		},
		{
			"type": "lookup_event",
			"key": "id",
			"rkey": "name",
			"choose": "nearest",
			"source": "Inspections",
			"comment": "Find Inspection",
			"global": true,
			"lead": 3600,
			"lag": 3600
		},
		{
			"type": "reject",
			"comment": "Remove entries with no inspection",
			"required": ["comment"],
			"action": "ignore"
		},
		{
			"type": "reject",
			"comment": "Hide failed runs",
			"action": "hide",
			"if": "{success}==0"
		},
{
			"type": "get_query",
			"points": ["Browning Zone.Temperature - Oven","Rising Zone.Temperature - Oven","Setting Zone.Temperature - Oven","Baking Roller 1.Speed - Conveyor"],
			"samples": 100,
			"comment": "Get temperatures and speeds"
		},
		{
			"type": "flatten",
			"method": "middle",
			"comment": "Sample values mid-run"
		}
	]
}