Contents of the Sheet 1 sheet in the C:\Import.xlsx fileExpression: IExpression;
Expression: Prognoz.Platform.Interop.ForeSystem.IExpression;
The Expression property determines expression to calculate value of calculated field.
The correct work of calculated filed is possible if the repository is determined for consumer or source, that is the IDtObject.Metabase property is set.
To execute the example the repository requires time series data base with the TSDB_IMPORT identifier. The time series data base must contain attributes of time series with the CITY and INDICATOR identifiers. The attributes must be links to dictionaries.
The file system is considered to have the C:\Import.xlsx file containing a data sheet named Sheet1.
Add links to the Cubes, Dal, Db, Dimensions, Dt, Metabase, Rds system assemblies.
Contents of the Sheet 1 sheet in the C:\Import.xlsx file
Sub UserProc;
Var
desc: IMetabaseObjectDescriptor;
importObject: IImportRequestDefinition;
createInfo: IMetabaseObjectCreateInfo;
params: IImportRequestProviderParams;
importBinding: ICubeMetaLoaderBinding;
rdsDictionary: IRdsDictionary;
excel: IDtExcelProviderEx;
instance: IImportRequestInstance;
importResult: IImportRequestResult;
mes: String;
fin: String;
count, i: Integer;
entry: ICubeMetaLoaderLogEntry;
rub: IRubricator;
mb: IMetabase;
Atts: IMetaAttributes;
Fields: IDtFieldDefinitions;
FieldDef: IDtFieldDefinition;
Begin
// Get repository
mb := MetabaseClass.Active;
// Get time series database
desc := mb.ItemById("TSDB_IMPORT");
rub := desc.Bind As IRubricator;
// Get attributes of time series
Atts := rub.Facts.Attributes;
// Create import object
createInfo := mb.CreateCreateInfo;
createInfo.ClassId := MetabaseObjectClass.KE_CLASS_IMPORTREQUEST;
createInfo.Name := "Import with calculated field";
createInfo.KeepEdit := True;
createInfo.Permanent := False;
createInfo.Parent := desc;
// Create object to import time series
importObject := mb.CreateObject(createInfo) As IImportRequestDefinition;
// Set source type
importObject.SourceType := ImportRequestSourceType.Provider;
// Set database of time series where data will be imported
importObject.DestinationRubricator := desc.Bind As IRubricator;
// Set import parameters
params := importObject.ProviderParams;
// Determine that errors on import are invalid
(params As ICubeMetaLoader).Log.MaxErrors := -1;
// Set import parameters of the CITY attribute
desc := Atts.FindById("CITY").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
importBinding := params.Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
importBinding.Attribute := "CITY";
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item(0).Id;
// Attribute value will be set by constant value
importBinding.FieldType := CubeMetaLoaderFieldType.ConstValue;
// The value will be taken from dictionary to which refers the attribute
importBinding.FieldValue := (rdsDictionary.Open(Null) As IDimInstance).Elements.Id(0);
// Set import parameters of the INDICATOR attribute
desc := Atts.FindById("INDICATOR").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
importBinding := params.Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
importBinding.Attribute := "INDICATOR";
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item(0).Id;
// The value will be taken from data source field
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Determine index of the field from which data are taken
importBinding.Field := "0";
// Determine import parameters of the UNIT attribute
desc := Atts.FindById("UNIT").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
importBinding := params.Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Unit;
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item(0).Id;
// Attribute value will be determined by constant value
importBinding.FieldType := CubeMetaLoaderFieldType.ConstValue;
// The value will be taken from dictionary to which refers the attribute
importBinding.FieldValue := (rdsDictionary.Open(Null) As IDimInstance).Elements.Id(0);
// Set parameters of the calendar export
importBinding := params.Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Calendar;
importBinding.ByColumns := False;
// Set format of calendar data
importBinding.CalendarDateFormatEx(DimCalendarLevel.Year) := "$Year$";
importBinding.CalendarDateFormatEx(DimCalendarLevel.HalfYear) := "$Year$A$Halfyear$";
importBinding.CalendarDateFormatEx(DimCalendarLevel.Quarter) := "$Year$Q$Quarter$";
importBinding.CalendarOptions.Levels := DimCalendarLevelSet.Year Or DimCalendarLevelSet.HalfYear Or DimCalendarLevelSet.Quarter;
// The value will be taken from the data source field
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Determine index of the row from which data are taken
importBinding.Field := "1";
// Set export parameters of time series values
importBinding := params.Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Value;
// The value will be taken from data source field
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Set index of the row from which data are taken
importBinding.Field := "4";
// Create object for import from Excel
excel := New DtExcelProviderEx.Create;
// Set parameters of the source file
excel.File := "C:\Import.xlsx";
excel.Sheet := "Sheet1";
excel.Format := "XLSX";
excel.HasHeader := True;
excel.Metabase := MB;
// Load fields from source file
excel.FieldsFromFile;
// Get loaded fields
Fields := excel.Fields;
// Set data types for fields with the 2 and 3 indexes
FieldDef := Fields.Item(2);
FieldDef.DataType := DbDataType.Float;
FieldDef := Fields.Item(3);
FieldDef.DataType := DbDataType.Float;
// Add new calculated field
// from which time line values are loaded
FieldDef := Fields.Add;
// Set expression to calculate field value
FieldDef.Expression.AsString := Fields.Item(2).Name + '*' + Fields.Item(3).Name;
// Set created object for import from Excel as a data source for import
importObject.ProviderParams.Provider := excel As IDatasetDataProvider;
// Data import
instance := (importObject As IMetabaseObject).Open(Null) As IImportRequestInstance;
instance.LoadData;
End Sub UserProc;
After executing the example data from the C:\Import.xlsx file is loaded in the time series data base. Values of time series will be calculated as product of the Value1 and Value2 fields of the file.
The requirements and result of the Fore.NET example execution match with those in the Fore example.
Imports Prognoz.Platform.Interop.Cubes;
Imports Prognoz.Platform.Interop.Dal;
Imports Prognoz.Platform.Interop.Db;
Imports Prognoz.Platform.Interop.Dimensions;
Imports Prognoz.Platform.Interop.Dt;
Imports Prognoz.Platform.Interop.Rds;
…
Public Shared Sub Main(Params: StartParams);
Var
desc: IMetabaseObjectDescriptor;
importObject: IImportRequestDefinition;
createInfo: IMetabaseObjectCreateInfo;
provParams: IImportRequestProviderParams;
importBinding: ICubeMetaLoaderBinding;
rdsDictionary: IRdsDictionary;
excel: IDtExcelProviderEx;
instance: IImportRequestInstance;
importResult: IImportRequestResult;
mes: String;
fin: String;
count, i: Integer;
entry: ICubeMetaLoaderLogEntry;
rub: IRubricator;
mb: IMetabase;
Atts: IMetaAttributes;
Fields: IDtFieldDefinitions;
FieldDef: IDtFieldDefinition;
Begin
// Get repository
mb := Params.Metabase;
// Get time series database
desc := mb.ItemById["TSDB_IMPORT"];
rub := desc.Bind() As IRubricator;
// Get time series attributes
Atts := rub.Facts.Attributes;
// Create import object
createInfo := mb.CreateCreateInfo();
createInfo.ClassId := MetabaseObjectClass.KE_CLASS_IMPORTREQUEST As integer;
createInfo.Name := "Import with calculated field";
createInfo.KeepEdit := True;
createInfo.Permanent := False;
createInfo.Parent := desc;
// Create object for time series import
importObject := mb.CreateObject(createInfo) As IImportRequestDefinition;
// Set source type
importObject.SourceType := ImportRequestSourceType.irstProvider;
// Set time series database to which data are imported
importObject.DestinationRubricator := desc.Bind() As IRubricator;
// Determine import parameters
provParams := importObject.ProviderParams;
// Determine that errors are not acceptable on import
(provParams As ICubeMetaLoader).Log.MaxErrors := -1;
// Set import parameters of the CITY attribute
desc := Atts.FindById("CITY").ValuesObject;
rdsDictionary := desc.Bind() As IRdsDictionary;
importBinding := provParams.Bindings.Add();
importBinding.BindingType := CubeMetaLoaderBindingType.cmlbtAttribute;
importBinding.Attribute := "CITY";
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item[0].Id;
// The attribute value will be set by constant value
importBinding.FieldType := CubeMetaLoaderFieldType.cmlftConstValue;
// Value will be taken from dictionary to which refers the attribute
importBinding.FieldValue := (rdsDictionary.Open(Null) As IDimInstance).Elements.Id[0];
// Determine import parameters of the INDICATOR attribute
desc := Atts.FindById("INDICATOR").ValuesObject;
rdsDictionary := desc.Bind() As IRdsDictionary;
importBinding := provParams.Bindings.Add();
importBinding.BindingType := CubeMetaLoaderBindingType.cmlbtAttribute;
importBinding.Attribute := "INDICATOR";
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item[0].Id;
// The value will be taken from data source field
importBinding.FieldType := CubeMetaLoaderFieldType.cmlftIndex;
// Set index of the field from which data are taken
importBinding.Field := "0";
// Set import parameters of the UNIT attribute
desc := Atts.FindById("UNIT").ValuesObject;
rdsDictionary := desc.Bind() As IRdsDictionary;
importBinding := provParams.Bindings.Add();
importBinding.BindingType := CubeMetaLoaderBindingType.cmlbtUnit;
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item[0].Id;
// Attribute value will be set by constant value
importBinding.FieldType := CubeMetaLoaderFieldType.cmlftConstValue;
// The value will be taken from dictionary to which refers the attribute
importBinding.FieldValue := (rdsDictionary.Open(Null) As IDimInstance).Elements.Id[0];
// Set calendar export parameters
importBinding := provParams.Bindings.Add();
importBinding.BindingType := CubeMetaLoaderBindingType.cmlbtCalendar;
importBinding.ByColumns := False;
// Set calendar data format
importBinding.CalendarDateFormatEx[DimCalendarLevel.dclYear] := "$Year$";
importBinding.CalendarDateFormatEx[DimCalendarLevel.dclQuarter] := "$Year$Q$Quarter$";
importBinding.CalendarOptions.Levels := DimCalendarLevelSet.dclsYear Or DimCalendarLevelSet.dclsQuarter;
// The value will be taken from data source field
importBinding.FieldType := CubeMetaLoaderFieldType.cmlftIndex;
// Determine index of the field from which data are taken
importBinding.Field := "1";
// Set export parameters of time series values
importBinding := provParams.Bindings.Add();
importBinding.BindingType := CubeMetaLoaderBindingType.cmlbtValue; // The value will be taken from data source field
importBinding.FieldType := CubeMetaLoaderFieldType.cmlftIndex;
// Set index of the field from which data are taken
importBinding.Field := "4";
// Create object for import from Excel
excel := New DtExcelProviderEx.Create();
// Determine parameters of the source file
excel.File := "C:\Import.xlsx";
excel.Sheet := "Sheet1";
excel.Format := "XLSX";
excel.HasHeader := True;
excel.Metabase := MB;
// Load fields from source file
excel.FieldsFromFile();
// Get loaded fields
Fields := excel.Fields;
// Determine data types for fields with the 2 and 3 indexes
FieldDef := Fields.Item[2];
FieldDef.DataType := DbDataType.ddtFloat;
FieldDef := Fields.Item[3];
FieldDef.DataType := DbDataType.ddtFloat;
// Ad new calculated fields
// from which time series values are loaded
FieldDef := Fields.Add();
// Set expression to calculate field value
FieldDef.Expression.AsString := Fields.Item[2].Name + '*' + Fields.Item[3].Name;
// Set created object for import from Excel as data source for import
importObject.ProviderParams.Provider := excel As IDatasetDataProvider;
// Import data
instance := (importObject As IMetabaseObject).Open(Null) As IImportRequestInstance;
instance.LoadData();
End Sub;
See also:
