Machine learning service

Using the Terrasoft.Configuration.QueryExtensions utility class 

The Terrasoft.Configuration.QueryExtensions utility class provides several extending methods for the Terrasoft.Core.DB.Select. This enables to build more compact queries.

As the object sourceColumn argument you can use following types (they will be transformed to the Terrasoft.Core.DB.QueryColumnExpression) for all extending methods:

• System.String – the name of the column in the TableAlias.ColumnName as ColumnAlias format (where the TableAlias and ColumnAlias are optional) or “*” – all columns.
• Terrasoft.Core.DB.QueryColumnExpression – will be added without changes.
• Terrasoft.Core.DB.IQueryColumnExpressionConvertible – will be converted.
• Terrasoft.Core.DB.Select – will be considered as subquery.

static Select Cols(this Select select, params object[] sourceColumns) 

Adds specified columns or subexpressions to the query.

Using the Cols() extension method, instead of the following expression:

new Select(userConnection)
        .Column("L", "Id")
        .Column("L", "QualifyStatusId")
        .Column("L", "LeadTypeId")
        .Column("L", "LeadSourceId")        
        .Column("L", "LeadMediumId").As("LeadChannel")
        .Column("L", "BusinesPhone").As("KnownBusinessPhone")    
    .From("Lead").As("L");

you can write:

new Select(userConnection).Cols(
        "L.Id",
        "L.QualifyStatusId",
        "L.LeadTypeId",
        "L.LeadSourceId",
        "L.LeadMediumId AS LeadChannel",
        "L.BusinesPhone AS KnownBusinessPhone")
    .From("Lead").As("L");

static Select Count(this Select select, object sourceColumn) 

Adds an aggregation column to calculate the number of non-empty values to the query.

For example, instead:

var activitiesCount = new Select(userConnection)
    .Column(Func.Count(Column.Asterisk()))
    .From("Activity")

you can write:

var activitiesCount = new Select(userConnection)
    .Count("*") // You can also specify the column name.
    .From("Activity")

static Select Coalesce(this Select select, params object[] sourceColumns)  

Adds a column with the function of determining the first value not equal to NULL to the query.

For example, instead:

new Select(userConnection)
    .Cols("L.Id")
    .Column(Func.Coalesce(
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId")))
        .As("CountryId")
    .From("Lead").As("L")
        .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id")
         .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");

you can write:

new Select(userConnection)
        .Cols("L.Id")
        .Coalesce("L.CountryId", "C.CountryId", "A.CountryId").As("CountryId")
    .From("Lead").As("L")
        .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id")
        .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");

static Select DateDiff(this Select select, DateDiffQueryFunctionInterval interval, object startDateExpression, object endDateExpression)  

Adds a column that specifies the date difference to the query.

For example, instead:

new Select(_userConnection)
        .Cols("Id")
        .Column(Func.DateDiff(DateDiffQueryFunctionInterval.Day,
            Column.SourceColumn("L", "CreatedOn"), Func.CurrentDateTime())).As("LeadAge")
    .From("Lead").As("L");

you can write:

var day = DateDiffQueryFunctionInterval.Day;
new Select(userConnection)
        .Cols("L.Id")
        .DateDiff(day, "L.CreatedOn", Func.CurrentDateTime()).As("LeadAge")
    .From("Lead").As("L");

static Select IsNull(this Select select, object checkExpression, object replacementValue)  

Adds a column with the function replacing NULL value with a replacement expression.

For example, instead:

new Select(userConnection).Cols("Id")
        .Column(Func.IsNull(
               Column.SourceColumn("L", "CreatedOn"), 
               Column.SourceColumn("L", "ModifiedOn")))
    .From("Lead").As("L");

you can write:

new Select(userConnection).Cols("L.Id")
        .IsNull("L.CreatedOn", "L.ModifiedOn")
    .From("Lead").As("L");

1. Implement custom predictive scoring 

Download and unpack the *.zip archive that includes the web service. The service implements custom predictive scoring in a Microsoft Visual Studio Code project.

For .NET 6.

For .NET Core 3.1.

To develop a custom web service using a Microsoft Visual Studio Code project, follow the instructions in a separate article: Develop C# code in a configuration project.

The MLService web service implements the following endpoints:

• session/start
• data/upload
• session/info/get
• fakeScorer/beginTraining
• fakeScorer/predict

2. Expand the problem list of the ML service 

1. Click to open the System Designer.
2. Go to the System setup block → Lookups.
3. Open the ML problem types lookup.

4. Add a problem type.

   1. Click New on the lookup toolbar.

   2. Fill out the problem type properties.

      • Set Name to "Fake scoring."
      • Set Service endpoint Url to "http://localhost:5000/."
      • Set Training endpoint to "/fakeScorer/beginTraining."

5. Find an ID of the problem type. To do this, display the corresponding column in the lookup list.

   1. Click View → Select fields to display on the lookup toolbar.
   2. Add the column to the lookup list. To do this, click and select the Id column.
   3. Click Select → Save.
   

The ID of the Fake scoring problem type is 19fcfff1-98b9-4933-8f26-457ca45c35ed.

3. Implement a ML model 

1. Go to the Configuration section and select a custom package to add the schema.

2. Click Add → Page view model on the section list toolbar.

   

3. Fill out the schema properties.

   • Set Code to "UsrMLModelPage."
   • Set Title to "MLModelPage."
   • Select "MLModelPage" in the Parent object property.
   

4. Implement the style of the new mini page similar to the mini page for creating a predictive scoring model. To do this, overload the getIsScoring() method.

   View the source code of the view model schema of the page below.

   UsrMLModelPage

   define("UsrMLModelPage", [], function() {
       return {
           /* The name of the page object schema. */
           entitySchemaName: "MLModel",
           /* The properties of the page view model. */
           properties: {
               FakeScoringProblemTypeId: "19fcfff1-98b9-4933-8f26-457ca45c35ed"
           },
           /* The methods of the page view model. */
           methods: {
               getIsScoring: function() {
                   const parentResult = this.callParent(arguments);
                   return parentResult || this.getLookupValue("MLProblemType") === this.FakeScoringProblemTypeId;
               }
           }
       };
   });
   

5. Click Save on the Module Designer's toolbar.

4. Implement the handling of the ML model results 

1. Go to the Configuration section and select a custom package to add the schema.

2. Click Add → Source code on the section list toolbar.

   

3. Fill out the schema properties.

   • Set Code to "UsrFakeScoringEntityPredictor."
   • Set Title to "FakeScoringEntityPredictor."
   

   Click Apply to apply the properties.

4. Implement the handling of the ML model results.

   • Implement the Predict() method that receives data exported from Creatio by object and returns the prediction value. The method uses a proxy class that implements the IMLServiceProxy interface to facilitate web service calls.
   • Initialize the ProblemTypeId property using the ID of the Fake scoring problem type from the ML problem types lookup.
   • Implement the SaveEntityPredictedValues() and SavePrediction() methods.

   View the source code of the UsrFakeScoringEntityPredictor class below.

   UsrFakeScoringEntityPredictor

   namespace Terrasoft.Configuration.ML
   {
       using System;
       using System.Collections.Generic;
       using Core;
       using Core.Factories;
       using Terrasoft.Core.Process.Configuration;
       using Terrasoft.ML.Interfaces;
   
       [DefaultBinding(typeof(IMLEntityPredictor), Name = "19fcfff1-98b9-4933-8f26-457ca45c35ed")]
       [DefaultBinding(typeof(IMLPredictor<ScoringOutput>), Name = "19fcfff1-98b9-4933-8f26-457ca45c35ed")]
       public class FakeScoringEntityPredictor: MLBaseEntityPredictor<ScoringOutput>, IMLEntityPredictor, IMLPredictor<ScoringOutput>{
           public FakeScoringEntityPredictor(UserConnection userConnection) : base(userConnection) {
           }
   
           protected override Guid ProblemTypeId => new Guid("19fcfff1-98b9-4933-8f26-457ca45c35ed");
   
           protected override ScoringOutput Predict(IMLServiceProxy proxy, MLModelConfig model, Dictionary<string, object> data, MLDataPredictionUserTask predictionUserTask) {
               return proxy.FakeScore(model, data, true);
           }
   
           protected override List<ScoringOutput> Predict(MLModelConfig model, IList<Dictionary<string, object>> dataList, IMLServiceProxy proxy, MLDataPredictionUserTask predictionUserTask) {
               return proxy.FakeScore(model, dataList, true);
           }
   
           protected override void SaveEntityPredictedValues(MLModelConfig model, Guid entityId, ScoringOutput predictedResult) {
               var predictedValues = new Dictionary<MLModelConfig, double> {
                   { 
                       model, predictedResult.Score
                   }
               };
               PredictionSaver.SaveEntityScoredValues(model.EntitySchemaId, entityId, predictedValues);
           }
   
           protected override void SavePrediction(MLModelConfig model, Guid entityId, ScoringOutput predictedResult) {
               PredictionSaver.SavePrediction(model.Id, model.ModelInstanceUId, entityId, predictedResult.Score);
           }
   
       }
   }
   

5. Click Publish on the Source Code Designer’s toolbar to apply the changes on the database level.

5. Add a new problem type to the prediction method 

1. Go to the Configuration section and select a custom package to add the schema.
2. Click Add → Source code on the section list toolbar.

3. Fill out the schema properties.

   • Set Code to "UsrFakeScoringProxy."
   • Set Title to "FakeScoringProxy."
   

   Click Apply to apply the properties.

4. Add the Fake scoring problem type to the prediction method. To do this, extend the IMLServiceProxy base interface and implementations in the prediction method of the current problem type. The MLServiceProxy class implements the Predict() method that receives contracts for input data and prediction results.

   View the source code of the UsrFakeScoringProxy class below.

   UsrFakeScoringProxy

   namespace Terrasoft.Configuration.ML
   {
       using System;
       using System.Collections.Generic;
       using System.Linq;
       using Terrasoft.ML.Interfaces;
       using Terrasoft.ML.Interfaces.Requests;
       using Terrasoft.ML.Interfaces.Responses;
   
       public partial interface IMLServiceProxy
       {
           ScoringOutput FakeScore(MLModelConfig model, Dictionary<string, object> data, bool predictContributions);
           List<ScoringOutput> FakeScore(MLModelConfig model, IList<Dictionary<string, object>> dataList, bool predictContributions);
       }
   
       public partial class MLServiceProxy : IMLServiceProxy
       {
           public ScoringOutput FakeScore(MLModelConfig model, Dictionary<string, object> data, bool predictContributions) {
               ScoringResponse response = Predict<ExplainedScoringRequest, ScoringResponse>(model.ModelInstanceUId, new List<Dictionary<string, object>> { data }, model.PredictionEndpoint, 100, predictContributions);
               return response.Outputs.FirstOrDefault();
           }
   
           public List<ScoringOutput> FakeScore(MLModelConfig model, IList<Dictionary<string, object>> dataList, bool predictContributions) {
               int count = Math.Min(1, dataList.Count);
               int timeout = Math.Max(ScoreTimeoutSec * count, BatchScoreTimeoutSec);
               ScoringResponse response = Predict<ScoringRequest, ScoringResponse>(model.ModelInstanceUId, dataList, model.PredictionEndpoint, timeout, predictContributions);
               return response.Outputs;
           }
       }
   }
   

5. Click Publish on the Source Code Designer’s toolbar to apply the changes on the database level.

6. Implement the batch prediction functionality 

1. Go to the Configuration section and select a custom package to add the schema.
2. Click Add → Source code on the section list toolbar.

3. Fill out the schema properties.

   • Set Code to "UsrFakeBatchScorer."
   • Set Title to "FakeBatchScorer."
   

   Click Apply to apply the properties.

4. Implement the batch prediction functionality.

   • Implement the IMLBatchPredictor interface.
   • Implement the FormatValueForSaving() and SavePredictionResult() methods.

   View the source code of the UsrFakeBatchScorer class below.

   UsrFakeBatchScorer

   namespace Terrasoft.Configuration.ML
   {
       using System;
       using Core;
       using Terrasoft.Core.Factories;
       using Terrasoft.ML.Interfaces;
   
       [DefaultBinding(typeof(IMLBatchPredictor), Name = "19fcfff1-98b9-4933-8f26-457ca45c35ed")]
       public class FakeBatchScorer : MLBatchPredictor<ScoringOutput>
       {
   
           public FakeBatchScorer(UserConnection userConnection) : base(userConnection) {
           }
   
           protected override object FormatValueForSaving(ScoringOutput scoringOutput) {
               return Convert.ToInt32(scoringOutput.Score * 100);
           }
   
           protected override void SavePredictionResult(MLModelConfig modelConfig, Guid entityId, ScoringOutput value) {
               PredictionSaver.SavePrediction(modelConfig.Id, modelConfig.ModelInstanceUId, entityId, value);
           }
   
       }
   
   }
   
   

5. Click Publish on the Source Code Designer’s toolbar to apply the changes on the database level.

Case description 

Implement automatic prediction for the AccountCategory column by the values of the Country, EmployeesNumber and Industry field while saving the account record. The following conditions should be met:

• Model learning should be created on the base of account records for last 90 days.
• Moodel Retraining should be performed every 30 days.
• Permissible value of prediction accuracy for the model – 0,6.

Case implementation algorithm 

1. Model learning 

To train the model:

1. Add a record to the ML Model lookup. Values of the record fields are given in the Table 1.

{
    "inputs": [
        {
            "name": "CountryId",
            "type": "Lookup",
            "isRequired": true
        },
        {
            "name": "EmployeesNumberId",
            "type": "Lookup",
            "isRequired": true
        },
        {
            "name": "IndustryId",
            "type": "Lookup",
            "isRequired": true
        }
    ],
    "output": {
        "name": "AccountCategoryId",
        "type": "Lookup",
        "displayName": "AccountCategory"
    }
}

new Select(userConnection)
    .Column("a", "Id").As("Id")
    .Column("a", "CountryId")
    .Column("a", "EmployeesNumberId")
    .Column("a", "IndustryId")
    .Column("a", "AccountCategoryId")
    .Column("c", "Name").As("AccountCategory")
.From("Account").As("a")
.InnerJoin("AccountCategory").As("c").On("c", "Id").IsEqual("a", "AccountCategoryId")
.Where("a", "CreatedOn").IsGreater(Column.Parameter(DateTime.Now.AddDays(-90)))

2. Perform the Execute model training job action on the ML Model lookup field.

Wait until the values of the Model processing status field will be changed in following sequence: DataTransfer, QueuedToTrain, Training, Done. The process may take several hours to finish (it depends on the amount of passed data and general workload of the predictive service.

2. Performing the prediction 

To start the predictions:

1. Create a business process in the user package. Select the saving of the Contact object as a start signal for the process. Check if the required fields are populated (Fig. 1).

Fig. 1. Start signal properties.

2. Add the MLModelId lookup parameter that refers to the ML Model entity. Select the record with the Predict account category model as a value.

3. Add the RecordId lookup parameter that refers to the Account entity. Select a reference for theRecordId parameter of the Signal element as a value.

4. Add a Script task element on the business process diagram and add the following code there:

var userConnection = Get<UserConnection>("UserConnection"); 
// Getting the Id of the Account record.
Guid entityId = Get<Guid>("RecordId"); 
// Geeting the id of the model.
var modelId = Get<Guid>("MLModelId");
var connectionArg = new ConstructorArgument("userConnection", userConnection);
// Object for calling prediction. 
var predictor = ClassFactory.Get<MLEntityPredictor>(connectionArg);
// Call of the forecasting service. The Data is saved in MLPrediction and in case of high probability of forecasting the data is saved in the required field of the Account.
predictor.PredictEntityValueAndSaveResult(modelId, entityId);
return true;

After saving and compiling the process, the prediction will be performed for new accounts. The prediction will be displayed on the account edit page.