Codeunit 2000 Time Series Management

Base Application, System.AI

src/Layers/W1/BaseApp/System/AI/TimeSeriesManagement.Codeunit.al480 lines, Copyright (c) Microsoft Corporation. MIT

namespace System.AI;using Microsoft.CashFlow.Setup;using Microsoft.Foundation.Period;using System;using System.Globalization;using System.Reflection;using System.Utilities;codeunit 2000 "Time Series Management"{    trigger OnRun()    begin    end;    var        TempTimeSeriesBuffer: Record "Time Series Buffer" temporary;        TempTimeSeriesForecast: Record "Time Series Forecast" temporary;        AzureMLConnector: Codeunit "Azure ML Connector";        ApiUri: Text;        ApiKey: SecretText;        NotInitializedErr: Label 'The connection has not been initialized. Initialize the connection before using the time series functionality.';        InitializationErr: Label 'Oops, something went wrong when connecting to the Azure Machine Learning endpoint. Please contact your system administrator.';        TimeOutSec: Integer;        TimeSeriesPeriodType: Option;        TimeSeriesForecastingStartDate: Date;        TimeSeriesObservationPeriods: Integer;        TimeSeriesFrequency: Integer;        TimeSeriesCalculationState: Option Uninitialized,Initialized,"Data Prepared",Done;        DataNotPreparedErr: Label 'The data was not prepared for forecasting. Prepare data before using the forecasting functionality.';        DataNotProcessedErr: Label 'The data for forecasting has not been processed yet. Results cannot be retrieved.';        ForecastingPeriodsErr: Label 'The number of forecasting periods must be greater than 0.';        MinimumHistoricalPeriods: Integer;        NotADateErr: Label 'PeriodFieldNo must point to a Date field.';        NotARecordErr: Label 'SourceRecord must point to Record or a RecordRef object.';        NegativeNumberOfPeriodsErr: Label 'NumberOfPeriods Must be strictly positive.';        MaximumHistoricalPeriods: Integer;        TimeSeriesModelOption: Option ARIMA,ETS,STL,"ETS+ARIMA","ETS+STL",ALL;        UseStandardCredentials: Boolean;        ForecastSecretNameTxt: Label 'ml-forecast', Locked = true;    [TryFunction]    procedure Initialize(Uri: Text; "Key": SecretText; TimeOutSeconds: Integer; UseStdCredentials: Boolean)    var        LimitType: Option;        Limit: Decimal;        ApiUriSized: Text[250];    begin        ApiUri := Uri;        ApiKey := Key;        TimeOutSec := TimeOutSeconds;        UseStandardCredentials := UseStdCredentials;        if ((ApiUri = '') or ApiKey.IsEmpty()) and UseStdCredentials then begin            GetMLForecastCredentials(ApiUriSized, ApiKey, LimitType, Limit);            ApiUri := ApiUriSized;        end;        if not AzureMLConnector.Initialize(ApiKey, ApiUri, TimeOutSec) then            Error(InitializationErr);        MinimumHistoricalPeriods := 5;        MaximumHistoricalPeriods := 24;        TimeSeriesCalculationState := TimeSeriesCalculationState::Initialized;    end;    procedure InitializeFromCashFlowSetup(TimeSeriesLibState: Option Uninitialized,Initialized,"Data Prepared",Done): Boolean    var        CashFlowSetup: Record "Cash Flow Setup";        ApiUrl: Text[250];        MLApiKey: SecretText;        UsingStandardCredentials: Boolean;        LimitValue: Decimal;    begin        if not CashFlowSetup.Get() then            exit(false);        CashFlowSetup.GetMLCredentials(ApiUrl, MLApiKey, LimitValue, UsingStandardCredentials);        Initialize(ApiUrl, MLApiKey, CashFlowSetup.TimeOut, UsingStandardCredentials);        SetMaximumHistoricalPeriods(CashFlowSetup."Historical Periods");        GetState(TimeSeriesLibState);        if not (TimeSeriesLibState = TimeSeriesLibState::Initialized) then            exit(false);        exit(true);    end;    [Scope('OnPrem')]    procedure SetMessageHandler(MessageHandler: DotNet HttpMessageHandler)    begin        AzureMLConnector.SetMessageHandler(MessageHandler);    end;    procedure PrepareData(RecordVariant: Variant; GroupIDFieldNo: Integer; DateFieldNo: Integer; ValueFieldNo: Integer; PeriodType: Option; ForecastingStartDate: Date; ObservationPeriods: Integer)    begin        if TimeSeriesCalculationState < TimeSeriesCalculationState::Initialized then            Error(NotInitializedErr);        TempTimeSeriesBuffer.Reset();        TempTimeSeriesBuffer.DeleteAll();        TimeSeriesPeriodType := PeriodType;        TimeSeriesForecastingStartDate := ForecastingStartDate;        TimeSeriesObservationPeriods := ObservationPeriods;        TimeSeriesFrequency := GetFrequency(PeriodType);        FillTimeSeriesBuffer(RecordVariant, GroupIDFieldNo, ValueFieldNo, DateFieldNo);        TimeSeriesCalculationState := TimeSeriesCalculationState::"Data Prepared";    end;    procedure SetPreparedData(var TempTimeSeriesBufferIn: Record "Time Series Buffer" temporary; PeriodType: Option; ForecastingStartDate: Date; ObservationPeriods: Integer)    begin        if TimeSeriesCalculationState < TimeSeriesCalculationState::Initialized then            Error(NotInitializedErr);        TempTimeSeriesBuffer.Copy(TempTimeSeriesBufferIn, true);        TimeSeriesPeriodType := PeriodType;        TimeSeriesForecastingStartDate := ForecastingStartDate;        TimeSeriesObservationPeriods := ObservationPeriods;        TimeSeriesFrequency := GetFrequency(PeriodType);        TimeSeriesCalculationState := TimeSeriesCalculationState::"Data Prepared";    end;    procedure GetPreparedData(var TempTimeSeriesBufferOut: Record "Time Series Buffer" temporary)    begin        if TimeSeriesCalculationState < TimeSeriesCalculationState::"Data Prepared" then            Error(DataNotPreparedErr);        TempTimeSeriesBufferOut.Copy(TempTimeSeriesBuffer, true);    end;    procedure Forecast(ForecastingPeriods: Integer; ConfidenceLevel: Integer; TimeSeriesModel: Option)    begin        if ConfidenceLevel = 0 then            ConfidenceLevel := 80;        if ForecastingPeriods < 1 then            Error(ForecastingPeriodsErr);        if TimeSeriesCalculationState < TimeSeriesCalculationState::"Data Prepared" then            Error(DataNotPreparedErr);        TempTimeSeriesForecast.Reset();        TempTimeSeriesForecast.DeleteAll();        if TempTimeSeriesBuffer.IsEmpty() then begin            TimeSeriesCalculationState := TimeSeriesCalculationState::Done;            exit;        end;        CreateTimeSeriesInput();        CreateTimeSeriesParameters(ForecastingPeriods, ConfidenceLevel, TimeSeriesModel);        if not AzureMLConnector.SendToAzureMLInternal(UseStandardCredentials) then            Error(GetLastErrorText);        LoadTimeSeriesForecast();        TimeSeriesCalculationState := TimeSeriesCalculationState::Done;    end;    procedure GetForecast(var TempTimeSeriesForecastOut: Record "Time Series Forecast" temporary)    begin        if TimeSeriesCalculationState < TimeSeriesCalculationState::Done then            Error(DataNotProcessedErr);        TempTimeSeriesForecastOut.Copy(TempTimeSeriesForecast, true);    end;    procedure GetState(var State: Option Uninitialized,Initialized,"Data Prepared",Done)    begin        State := TimeSeriesCalculationState;    end;    local procedure GetFrequency(PeriodType: Option): Integer    var        Date: Record Date;    begin        case PeriodType of            Date."Period Type"::Date:                exit(365);            Date."Period Type"::Week:                exit(52);            Date."Period Type"::Month:                exit(12);            Date."Period Type"::Quarter:                exit(4);            Date."Period Type"::Year:                exit(1);        end;    end;    procedure GetOutput(LineNo: Integer; ColumnNo: Integer): Text    var        OutputValue: Text;    begin        AzureMLConnector.GetOutput(LineNo, ColumnNo, OutputValue);        exit(OutputValue);    end;    procedure GetOutputLength(): Integer    var        Length: Integer;    begin        AzureMLConnector.GetOutputLength(Length);        exit(Length);    end;    procedure GetInput(LineNo: Integer; ColumnNo: Integer): Text    var        InputValue: Text;    begin        AzureMLConnector.GetInput(LineNo, ColumnNo, InputValue);        exit(InputValue);    end;    procedure GetInputLength(): Integer    var        Length: Integer;    begin        AzureMLConnector.GetInputLength(Length);        exit(Length);    end;    procedure GetParameter(Name: Text): Text    var        ParameterValue: Text;    begin        AzureMLConnector.GetParameter(Name, ParameterValue);        exit(ParameterValue);    end;    local procedure FillTimeSeriesBuffer(RecordVariant: Variant; GroupIDFieldNo: Integer; ValueFieldNo: Integer; DateFieldNo: Integer)    var        TempTimeSeriesBufferDistinct: Record "Time Series Buffer" temporary;        DataTypeManagement: Codeunit "Data Type Management";        PeriodPageManagement: Codeunit PeriodPageManagement;        RecRef: RecordRef;        GroupIDFieldRef: FieldRef;        ValueFieldRef: FieldRef;        DateFieldRef: FieldRef;        CurrentPeriod: Integer;        PeriodStartDate: Date;        PeriodEndDate: Date;        Value: Decimal;    begin        DataTypeManagement.GetRecordRef(RecordVariant, RecRef);        if RecRef.IsEmpty() then            exit;        GroupIDFieldRef := RecRef.Field(GroupIDFieldNo);        DateFieldRef := RecRef.Field(DateFieldNo);        ValueFieldRef := RecRef.Field(ValueFieldNo);        GetDistinctRecords(RecRef, GroupIDFieldNo, TempTimeSeriesBufferDistinct);        if TempTimeSeriesBufferDistinct.FindSet() then            repeat                GroupIDFieldRef.SetRange(TempTimeSeriesBufferDistinct."Group ID");                for CurrentPeriod := -TimeSeriesObservationPeriods to -1 do begin                    PeriodStartDate :=                      PeriodPageManagement.MoveDateByPeriod(TimeSeriesForecastingStartDate, TimeSeriesPeriodType, CurrentPeriod);                    PeriodEndDate :=                      PeriodPageManagement.MoveDateByPeriodToEndOfPeriod(TimeSeriesForecastingStartDate, TimeSeriesPeriodType, CurrentPeriod);                    DateFieldRef.SetRange(PeriodStartDate, PeriodEndDate);                    if ValueFieldRef.Class = FieldClass::Normal then                        Value := CalculateValueNormal(ValueFieldRef)                    else                        Value := CalculateValueFlowField(RecRef, ValueFieldRef);                    TempTimeSeriesBuffer.Init();                    TempTimeSeriesBuffer."Group ID" := GroupIDFieldRef.GetFilter;                    TempTimeSeriesBuffer."Period No." := TimeSeriesObservationPeriods + CurrentPeriod + 1;                    TempTimeSeriesBuffer."Period Start Date" := PeriodStartDate;                    TempTimeSeriesBuffer.Value := Value;                    TempTimeSeriesBuffer.Insert();                end;            until TempTimeSeriesBufferDistinct.Next() = 0;    end;    local procedure CalculateValueNormal(var ValueFieldRef: FieldRef) Value: Decimal    begin        if ValueFieldRef.Class <> FieldClass::Normal then            exit(0);        ValueFieldRef.CalcSum();        Value := ValueFieldRef.Value();    end;    local procedure CalculateValueFlowField(var RecRef: RecordRef; var ValueFieldRef: FieldRef) Value: Decimal    var        CurrentValue: Decimal;    begin        if ValueFieldRef.Class <> FieldClass::FlowField then            exit(0);        if RecRef.FindSet() then            repeat                ValueFieldRef.CalcField();                CurrentValue := ValueFieldRef.Value();                Value += CurrentValue;            until RecRef.Next() = 0;    end;    local procedure CreateTimeSeriesInput()    begin        AzureMLConnector.AddInputColumnName('GranularityAttribute');        AzureMLConnector.AddInputColumnName('DateKey');        AzureMLConnector.AddInputColumnName('TransactionQty');        if TempTimeSeriesBuffer.FindSet() then            repeat                AzureMLConnector.AddInputRow();                AzureMLConnector.AddInputValue(Format(TempTimeSeriesBuffer."Group ID"));                AzureMLConnector.AddInputValue(Format(TempTimeSeriesBuffer."Period No."));                AzureMLConnector.AddInputValue(Format(TempTimeSeriesBuffer.Value, 0, 9));            until TempTimeSeriesBuffer.Next() = 0;    end;    local procedure CreateTimeSeriesParameters(ForecastingPeriods: Integer; ConfidenceLevel: Integer; TimeSeriesModel: Option ARIMA,ETS,STL,"ETS+ARIMA","ETS+STL",ALL,TBATS)    begin        AzureMLConnector.AddParameter('horizon', Format(ForecastingPeriods));        AzureMLConnector.AddParameter('seasonality', Format(TimeSeriesFrequency));        AzureMLConnector.AddParameter('forecast_start_datekey', Format(TimeSeriesObservationPeriods + 1));        AzureMLConnector.AddParameter('time_series_model', Format(TimeSeriesModel));        AzureMLConnector.AddParameter('confidence_level', Format(ConfidenceLevel));    end;    local procedure LoadTimeSeriesForecast()    var        TypeHelper: Codeunit "Type Helper";        PeriodPageManagement: Codeunit PeriodPageManagement;        DotNet_CultureInfo: Codeunit DotNet_CultureInfo;        Value: Variant;        LineNo: Integer;        GroupID: Code[50];        PeriodNo: Integer;    begin        for LineNo := 1 to GetOutputLength() do begin            TempTimeSeriesForecast.Init();            Evaluate(GroupID, GetOutput(LineNo, 1));            TempTimeSeriesForecast."Group ID" := GroupID;            Evaluate(PeriodNo, GetOutput(LineNo, 2));            TempTimeSeriesForecast."Period No." := PeriodNo;            TempTimeSeriesForecast."Period Start Date" :=              PeriodPageManagement.MoveDateByPeriod(                TimeSeriesForecastingStartDate, TimeSeriesPeriodType, PeriodNo - TimeSeriesObservationPeriods - 1);            Value := TempTimeSeriesForecast.Value;            TypeHelper.Evaluate(Value, GetOutput(LineNo, 3), '', DotNet_CultureInfo.CurrentCultureName());            TempTimeSeriesForecast.Value := Value;            Value := TempTimeSeriesForecast.Delta;            TypeHelper.Evaluate(Value, GetOutput(LineNo, 4), '', DotNet_CultureInfo.CurrentCultureName());            TempTimeSeriesForecast.Delta := Value;            if TempTimeSeriesForecast.Value <> 0 then                TempTimeSeriesForecast."Delta %" := Abs(TempTimeSeriesForecast.Delta / TempTimeSeriesForecast.Value) * 100;            TempTimeSeriesForecast.Insert();        end;    end;    local procedure GetDistinctRecords(RecRef: RecordRef; FieldNo: Integer; var TempTimeSeriesBufferDistinct: Record "Time Series Buffer" temporary)    var        FieldRef: FieldRef;        OptionValue: Integer;    begin        FieldRef := RecRef.Field(FieldNo);        if RecRef.FindSet() then            repeat                TempTimeSeriesBufferDistinct.Init();                if FieldRef.Type = FieldType::Option then begin                    OptionValue := FieldRef.Value();                    TempTimeSeriesBufferDistinct."Group ID" := Format(OptionValue);                end else                    TempTimeSeriesBufferDistinct."Group ID" := FieldRef.Value();                if not TempTimeSeriesBufferDistinct.Insert() then;            until RecRef.Next() = 0;    end;    procedure SetMinimumHistoricalPeriods(NumberOfPeriods: Integer)    begin        if not (NumberOfPeriods > 0) then            Error(NegativeNumberOfPeriodsErr);        MinimumHistoricalPeriods := NumberOfPeriods;    end;    procedure SetMaximumHistoricalPeriods(NumberOfPeriods: Integer)    begin        if not (NumberOfPeriods > 0) then            Error(NegativeNumberOfPeriodsErr);        MaximumHistoricalPeriods := NumberOfPeriods;    end;    procedure HasMinimumHistoricalData(var NumberOfPeriodsWithHistory: Integer; SourceRecord: Variant; PeriodFieldNo: Integer; PeriodType: Option Day,Week,Month,Quarter,Year; ForecastStartDate: Date): Boolean    var        DataTypeManagement: Codeunit "Data Type Management";        SourceRecordRef: RecordRef;        PeriodFieldRef: FieldRef;        HistoryStartDate: Variant;        HistoryEndDate: Date;    begin        // SourceRecord Should already contain all the necessary filters        // and should be sorted by the desired date field        if SourceRecord.IsRecord then            DataTypeManagement.GetRecordRef(SourceRecord, SourceRecordRef)        else            if SourceRecord.IsRecordRef then                SourceRecordRef := SourceRecord            else                Error(NotARecordErr);        if not SourceRecordRef.FindFirst() then            exit(false);        // last date of transaction history that will be used for forecast        HistoryEndDate := CalcDate('<-1D>', ForecastStartDate);        PeriodFieldRef := SourceRecordRef.Field(PeriodFieldNo);        PeriodFieldRef.SetFilter('%1..', CalculateMaxStartDate(HistoryEndDate, PeriodType));        if SourceRecordRef.FindSet() then;        PeriodFieldRef := SourceRecordRef.Field(PeriodFieldNo);        HistoryStartDate := PeriodFieldRef.Value();        if not (HistoryStartDate.IsDate or HistoryStartDate.IsDateTime) then            Error(NotADateErr);        NumberOfPeriodsWithHistory :=          CalculatePeriodsWithHistory(HistoryStartDate, HistoryEndDate, PeriodType);        if NumberOfPeriodsWithHistory < MinimumHistoricalPeriods then            exit(false);        exit(true);    end;    local procedure CalculatePeriodsWithHistory(HistoryStartDate: Date; HistoryEndDate: Date; PeriodType: Option) NumberOfPeriodsWithHistory: Integer    var        PeriodPageManagement: Codeunit PeriodPageManagement;    begin        while HistoryStartDate <= HistoryEndDate do begin            NumberOfPeriodsWithHistory += 1;            HistoryStartDate := PeriodPageManagement.MoveDateByPeriod(HistoryStartDate, PeriodType, 1);        end;    end;    local procedure CalculateMaxStartDate(HistoryEndDate: Date; PeriodType: Option Day,Week,Month,Quarter,Year): Date    begin        case PeriodType of            PeriodType::Day:                exit(CalcDate(StrSubstNo('<-%1D>', Format(MaximumHistoricalPeriods - 1)), HistoryEndDate));            PeriodType::Week:                exit(CalcDate(StrSubstNo('<-%1W+1D>', Format(MaximumHistoricalPeriods)), HistoryEndDate));            PeriodType::Month:                exit(CalcDate(StrSubstNo('<-%1M+1D>', Format(MaximumHistoricalPeriods)), HistoryEndDate));            PeriodType::Quarter:                exit(CalcDate(StrSubstNo('<-%1Q+1D>', Format(MaximumHistoricalPeriods)), HistoryEndDate));            PeriodType::Year:                exit(CalcDate(StrSubstNo('<-%1Y+1D>', Format(MaximumHistoricalPeriods)), HistoryEndDate));        end;    end;    procedure GetTimeSeriesModelOption(TimeSeriesModel: Text): Integer    begin        Evaluate(TimeSeriesModelOption, TimeSeriesModel);        exit(TimeSeriesModelOption);    end;    [NonDebuggable]    [TryFunction]    [Scope('OnPrem')]    procedure GetMLForecastCredentials(var LocalApiUri: Text[250]; var "Key": SecretText; var LimitType: Option; var Limit: Decimal)    var        MachineLearningKeyVaultMgmt: Codeunit "Machine Learning KeyVaultMgmt.";    begin        MachineLearningKeyVaultMgmt.GetMachineLearningCredentials(ForecastSecretNameTxt, LocalApiUri, "Key", LimitType, Limit);        LocalApiUri += '/execute?api-version=2.0&details=true';    end;}