Codeunit 2000 Time Series Management
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;}