Objective
The main objective of this article is the step-by-step realization of the extension of the functionality that D365FO offers us regarding IoT and to be able to add a new scenario totally created by us. For this, it will be necessary to make modifications both at the D365FO and Azure level.
As you know, D365FO provides us with 5 available scenarios to be able to treat IoT devices; the intent of this article is to learn how to programmatically add a new scenario.
Main principles
The new scenario that we are going to implement responds to the need of those devices subject to constant changes in their properties and that these changes must be within certain margins to avoid their deterioration.
A clear example known to the vast majority is diving. When someone submerges to great depths, they cannot return to the surface suddenly, but the depressurization must be gradual so as not to suffer brain damage.
Another example at a more industrial level are the temperature differences that certain materials must undergo in order for them to "transform" into others (tempering glass, or the creation of steel).
So, we are going to implement a scenario that measures these differences between consecutive measurements and compares them with the reference values to determine whether or not it is within the range accepted by the product.
Difference = (Current value - last value) / (Time lapse)Scenario
Following the principle mentioned above, we are going to create a scenario that allows us to evaluate if the differences of the connected sensors are within the parameterized threshold for that measurement. That is, the variation of its measurement per unit of time.
We are going to use the product quality scenario as a base since the one we intend to create is very similar.
Elements to modify in D365FO
Form IoTIntCoreScenarioManagement
This is the main form where the sensors are configured. This modification is intended to add a new button to be able to configure the new scenario.
It is necessary to add a new group to be able to configure the new scenario
BaseEnum IoTIntCoreScenarioType
Let's also add a new enum value to identify our new scenario.
Notification form IoTIntCoreNotification
We are going to extend the INIT of the Datasource to add our scenario in the notification filter:
[ExtensionOf(formDataSourceStr(IoTIntCoreNotification, IoTIntCoreNotification))]
public final class IoTIntCoreNotification_IoTIntelligenceManufacturing_Variation_Extension
{
public void init()
{
next init();
var form = this.formRun();
if (form && form.args() &&
form.args().menuItemName() == menuItemDisplayStr(IoTIntCoreNotification))
{
var range = this.queryBuildDataSource().addRange(fieldNum(IoTIntCoreNotification, Type));
range.value(strFmt('((%1.%2 == "%3") || (%1.%2 == "%4") || (%1.%2 == "%5") || (%1.%2 == "%6"))',
tableStr(IoTIntCoreNotification),
fieldStr(IoTIntCoreNotification, Type),
SysQuery::value(enum2Symbol(enumNum(IoTIntCoreScenarioType), enum2int(IoTIntCoreScenarioType::ProductQualityValidation))),
SysQuery::value(enum2Symbol(enumNum(IoTIntCoreScenarioType), enum2int(IoTIntCoreScenarioType::MachineReportingStatus))),
SysQuery::value(enum2Symbol(enumNum(IoTIntCoreScenarioType), enum2int(IoTIntCoreScenarioType::ProductVariation))),
SysQuery::value(enum2Symbol(enumNum(IoTIntCoreScenarioType), enum2int(IoTIntCoreScenarioType::ProductionJobDelayed)))));
}
}
}Class IoTIntMfgNotificationProductVariationDeviationTemplate
In relation to D365FO, what we want is for the new scenario to be exactly the same as that of Product Quality. The substantive change will take place in Azure. So let's make a copy of the IoTIntMfgNotificationQualityAttributeDeviationTemplate class and change its references to point to our scenario as well as the labels. This class is in charge of configuring the scenario and the notifications that will be seen in case of not complying with the parameterized ranges.
/// <summary>
/// The <c>IoTIntMfgNotificationProductVariationDeviationTemplate</c> class contains notification template
/// for quality attribute deviation notification.
/// </summary>
[IoTIntCoreNotificationType('ProductVariation')]
public class IoTIntMfgNotificationProductVariationDeviationTemplate extends IoTIntCoreNotificationTemplate
{
.
.
.
/// <summary>
/// Creates missing <c>IoTIntCoreSensorScenarioMapping</c> records for each <c>IoTIntCoreSensorBusinessRecordMapping</c> records mapped to a
/// resource.
/// </summary>
public void createSensorScenarioMappings()
{
IoTIntCoreSensorBusinessRecordMapping sensorBusinessRecordMappingResource;
IoTIntCoreSensorBusinessRecordMapping sensorBusinessRecordMappingBatchAttribute;
IoTIntCoreSensorScenarioMapping sensorScenarioMappingInsert;
IoTIntCoreSensorScenarioMapping sensorScenarioMappingNotExist;
const IoTIntCoreScenarioType ScenarioType = IoTIntCoreScenarioType::ProductQualityValidation;
const IoTIntCoreSensorScenarioMappingActiveNoYesId NotActive = NoYes::No;
insert_recordset sensorScenarioMappingInsert(SensorId, Scenario, Active)
select SensorId, ScenarioType, NotActive from sensorBusinessRecordMappingResource
where sensorBusinessRecordMappingResource.RefTableId == tableNum(wrkCtrTable)
exists join sensorBusinessRecordMappingBatchAttribute
where sensorBusinessRecordMappingBatchAttribute.RefTableId == tableNum(PdsBatchAttrib)
&& sensorBusinessRecordMappingBatchAttribute.SensorId == sensorBusinessRecordMappingResource.SensorId
notexists join sensorScenarioMappingNotExist
where sensorScenarioMappingNotExist.SensorId == sensorBusinessRecordMappingResource.SensorId
&& sensorScenarioMappingNotExist.Scenario == ScenarioType;
}
public boolean validateScenarioActivation(IoTIntCoreSensorId _sensorId)
{
IoTIntCoreSensorBusinessRecordMapping sensorBusinessRecordMappingResource;
IoTIntCoreSensorBusinessRecordMapping sensorBusinessRecordMappingBatchAttribute;
select firstonly RecId from sensorBusinessRecordMappingResource
where sensorBusinessRecordMappingResource.SensorId == _sensorId
&& sensorBusinessRecordMappingResource.RefTableId == tableNum(wrkCtrTable)
exists join sensorBusinessRecordMappingBatchAttribute
where sensorBusinessRecordMappingBatchAttribute.RefTableId == tableNum(PdsBatchAttrib)
&& sensorBusinessRecordMappingBatchAttribute.SensorId == sensorBusinessRecordMappingResource.SensorId;
if (!sensorBusinessRecordMappingResource.RecId)
{
return checkFailed(strFmt("@IoTIntelligenceCore:ErrorMessage_SensorScenarioMappingActivationValidationFailed",
enum2Str(IoTIntCoreScenarioType::ProductVariation),
_sensorId,
strFmt('%1,%2', tableId2PName(tableNum(WrkCtrTable)), tableId2PName(tableNum(PdsBatchAttrib)))));
}
return true;
}
public boolean validateDeleteSensorBusinessRecordMappingWithActiveScenario(IoTIntCoreSensorBusinessRecordMapping _sensorBusinessRecordMapping)
{
if (_sensorBusinessRecordMapping.RefTableId == tableNum(WrkCtrTable)
|| _sensorBusinessRecordMapping.RefTableId == tableNum(PdsBatchAttrib))
{
return checkFailed(strFmt("@IoTIntelligenceCore:ErrorMessage_SensorBusinessRecordMappingDeletionValidationFailed",
_sensorBusinessRecordMapping.SensorId,
tableId2PName(_sensorBusinessRecordMapping.RefTableId),
enum2Str(IoTIntCoreScenarioType::ProductVariation)));
}
return true;
}
.
.
.
}
Elements to modify in Azure
New consumer group on IoTHub
We created a new group to centralize IoT signals. This creation is mandatory since when we create an Azure Stream Analytics it must point to a different consumption group.
New resource Stream Analytics
We create a new Stream Analytics resource
We go to the INPUT tab and create a new IoT Hub type element with the following characteristics (special attention to the group that we created in the previous step):
This input is the one that comes from our sensor and will capture the values that then have to be compared.
You must also add another REFERENCE type BLOB with the following characteristics:
This input will collect the values stored in the Blob Storage about the tolerances and will allow the resource to perform the comparisons.
In the OUTPUTS tab we create an Azure Functions type output with the following characteristics:
And a Service Bus Queue to manage the notifications in case it is necessary to show them:
In the QUERY tab we have to modify the statements inside. This is the most complex part and in which you have to pay more attention:
//Creación del INPUT de IoT
CREATE TABLE IotInput(
eventEnqueuedUtcTime datetime,
sensorId nvarchar(max),
value float
);
//Creación tabla de tolerancias
CREATE TABLE SensorJobItemBatchAttributeVariationReferenceInput(
sensorId nvarchar(max),
jobId nvarchar(max),
orderId nvarchar(max),
itemNumber nvarchar(max),
attributeName nvarchar(max),
jobDataAreaId nvarchar(max),
jobRegistrationStartDateTime datetime,
jobRegistrationStopDateTime datetime,
isJobCompleted nvarchar(max),
maximumAttributeTolerance float,
minimumAttributeTolerance float,
optimalAttributeValue float
);
//Evaluación de valores
WITH SensorJobItemBatchAttributeValues AS
(
SELECT
I.sensorId,
I.eventEnqueuedUtcTime,
I.value,
R.jobId,
R.orderId,
R.itemNumber,
R.attributeName,
R.jobDataAreaId,
R.jobRegistrationStartDateTime,
R.jobRegistrationStopDateTime,
R.isJobCompleted,
R.maximumAttributeTolerance,
R.minimumAttributeTolerance,
R.optimalAttributeValue,
CASE
WHEN I.value >= R.minimumAttributeTolerance AND I.value <= R.maximumAttributeTolerance THEN 1
ELSE 0
END AS attributeValueInRange
FROM IotInput I
TIMESTAMP BY I.eventEnqueuedUtcTime
JOIN SensorJobItemBatchAttributeVariationReferenceInput R
ON I.sensorId = R.sensorId
-- Only consider jobs which are in progress and signals which came after the start of the job.
WHERE DATEDIFF(year, R.jobRegistrationStopDateTime, CAST('1900-01-01' as datetime)) = 0
AND I.eventEnqueuedUtcTime >= R.jobRegistrationStartDateTime
),
SensorJobItemBatchAttributeValuesState AS
(
SELECT
*,
/** Determine value for last signal was in range or not having same partition values as current signal.
previousSignalValueInRange will be null if there was no previous signal */
LAG(attributeValueInRange) OVER
(PARTITION BY
sensorId,
jobId,
orderId,
itemNumber,
attributeName,
jobDataAreaId
LIMIT DURATION(minute, 15)
) AS previousSignalValueInRange,
LAG(value) OVER
(PARTITION BY
sensorId,
jobId,
orderId,
itemNumber,
attributeName,
jobDataAreaId
LIMIT DURATION(minute, 15)
) AS previousValue,
CASE
WHEN value-LAG(value) OVER
(PARTITION BY
sensorId,
jobId,
orderId,
itemNumber,
attributeName,
jobDataAreaId
LIMIT DURATION(minute, 15)
) >= minimumAttributeTolerance AND value-LAG(value) OVER
(PARTITION BY
sensorId,
jobId,
orderId,
itemNumber,
attributeName,
jobDataAreaId
LIMIT DURATION(minute, 15)
) <= maximumAttributeTolerance THEN 1
ELSE 0
END AS attributeValueInRange
FROM SensorJobItemBatchAttributeValues
)
SELECT
CONCAT('ProductVariation:', jobId, ':', attributeName) AS metricKey,
DATEDIFF(millisecond, CAST('1970-01-01' as datetime), eventEnqueuedUtcTime) AS uts,
value AS val
INTO MetricOutput
from SensorJobItemBatchAttributeValues
SELECT
jobDataAreaId AS dataAreaId,
sensorId AS machineId,
jobId AS jobId,
orderId AS orderId,
itemNumber AS itemId,
minimumAttributeTolerance AS minValue,
maximumAttributeTolerance AS maxValue,
optimalAttributeValue AS targetValue,
attributeName AS batchAttribId,
sensorId AS sensorId,
value AS sensorReading,
eventEnqueuedUtcTime AS timestamp,
eventEnqueuedUtcTime AS sensorTimestamp,
System.Timestamp AS processingTimestamp,
CASE
WHEN attributeValueInRange = 1 THEN 'TRUE'
ELSE 'FALSE'
END AS validAttributeSignal,
'ProductVariation' AS notificationType,
CONCAT('ProductVariation:', jobId, ':', attributeName) AS publishedMetric,
'Product Variation' AS publishedMetricDisplayName
INTO NotificationOutput
FROM SensorJobItemBatchAttributeValuesState
-- This ensures that we are not sending the notification twice.
WHERE
(
(
attributeValueInRange = 0 AND
(previousSignalValueInRange IS NULL OR previousSignalValueInRange = 1)
)
OR
(
attributeValueInRange = 1 AND
previousSignalValueInRange = 0
)
)And last and most importantly, you have to modify the Logic App and add a whole new branch that manages the new scenario. As it is very complex to explain each step here is a link to GitHub where the template with the added branch is posted. Remember that we are making a modified copy of the current product quality.
Once we have reached this point we can test our scenario and check how the system notifies us when the signals exceed the parameterized tolerances.
To carry out the tests, an IoT Emulator made on purpose has been used to be able to play with the values and have a friendlier interface. I share the GitHub URL with the application:
I hope you liked this article. In the following publication we will see the tool in operation and the different options that it offers us at the user level.
MERRY CHRISTMAS!