Extending IoT

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.

/// 
/// The IoTIntMfgNotificationProductVariationDeviationTemplate class contains notification template
/// for quality attribute deviation notification.
/// 
[IoTIntCoreNotificationType('ProductVariation')]
public class IoTIntMfgNotificationProductVariationDeviationTemplate extends IoTIntCoreNotificationTemplate
{
    .
    .
    .   

    /// 
    /// Creates missing IoTIntCoreSensorScenarioMapping records for each IoTIntCoreSensorBusinessRecordMapping records mapped to a
    /// resource.
    /// 
    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.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)
    ) 

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.

https://github.com/iD365FOnt/D365FO_IoT_LogicApp

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:

https://github.com/iD365FOnt/IoTEmulator

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!