The C:\Fixed.xlsx file, for which the example is createdTestLoad: ICubeMetaLoaderTestLoadResult;
The TestLoad method implements test loading of time series from data source and returns object that contains results.
During text loading data is checked, loaded data is not saved.
Executing the example requires a time series database with the OBJ_FC identifier and a time series export file C:\Fixed.xlsx. Also, MDM repository with the MDM_REPO identifier must be present in the repository. There should be dictionaries with the DICT_CTR, DICT_IND and UNITS identifiers in this repository. The COUNTRY attribute of the time series database refers to the DICT_CTR dictionary, the INDICATOR attribute is a link to the DICT_IND dictionary, and measurement units refer to the UNITS dictionary.
Add links to the Metabase, Cube, Dt, Db, Dimension system assemblies.
The C:\Fixed.xlsx file, for which the example is created
Sub UserProc;
Var
Mb: IMetabase;
NsiDescr: IMetabaseObjectDescriptor;
CubeMetaLoader: ICubeMetaLoader;
CrInfo: IMetabaseObjectCreateInfo;
Obj: IMetabaseObject;
ObjDesc: IMetabaseObjectDescriptor;
CubeMetaLoaderBindings: ICubeMetaLoaderBindings;
CalendarBinding: ICubeMetaLoaderBinding;
CountryBinding: ICubeMetaLoaderBinding;
IndicatorBinding: ICubeMetaLoaderBinding;
UnitBinding: ICubeMetaLoaderBinding;
ScaleBinding: ICubeMetaLoaderBinding;
LevelBinding: ICubeMetaLoaderBinding;
ValueBinding: ICubeMetaLoaderBinding;
Parser: ICubeMetaLoaderBindingParser;
Excel: IDtExcelProvider;
Provider: IDatasetDataProvider;
Log: ICubeMetaLoaderLog;
Entry: ICubeMetaLoaderLogEntry;
Count, i, key: Integer;
TestResult: ICubeMetaLoaderTestLoadResult;
TestResultEntry: ICubeMetaLoaderTestLoadResultEntry;
Begin
Mb := MetabaseClass.Active;
NsiDescr := Mb.ItemById("RDS_REPO");
// Create a loader
CrInfo := Mb.CreateCreateInfo;
CrInfo.ClassId := MetabaseObjectClass.KE_CLASS_CUBEMETALOADER;
CrInfo.Id := "CUBEMETALOADER";
CrInfo.Permanent := False;
ObjDesc := Mb.CreateObject(CrInfo);
Obj := ObjDesc.Edit;
CubeMetaLoader := Obj As ICubeMetaLoader;
CubeMetaLoader.LoadType := CubeMetaLoaderLoadType.CreateAndUpdate;
CubeMetaLoader.Rubricator := Mb.ItemById("OBJ_FC").Bind As IRubricator;
CubeMetaLoader.ScaleValues := True;
CubeMetaLoaderBindings := CubeMetaLoader.Bindings;
// Edit calendar parameters
CalendarBinding := CubeMetaLoaderBindings.Add;
CalendarBinding.BindingType := CubeMetaLoaderBindingType.Calendar;
CalendarBinding.CalendarOptions.Levels := DimCalendarLevelSet.Year;
CalendarBinding.ByColumns := False;
CalendarBinding.CalendarDateFormat := "$Year$";
CalendarBinding.FieldType := CubeMetaLoaderFieldType.Name;
CalendarBinding.Field := "Year";
Parser := CalendarBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 2;
Parser.StringPositionEnd := -1;
// Edit parameters of the INDICATOR attribute
IndicatorBinding := CubeMetaLoaderBindings.Add;
IndicatorBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
IndicatorBinding.Attribute := "INDICATOR";
IndicatorBinding.Field := "Indicator Code";
IndicatorBinding.FieldType := CubeMetaLoaderFieldType.Name;
IndicatorBinding.Dimension := Mb.ItemByIdNamespace("DICT_IND", NsiDescr.Key).Bind
As IDimensionModel;
// IX_CODE - unique key of the DICT_IND dictionary
IndicatorBinding.Index := "IX_CODE";
IndicatorBinding.KeyAttribute := "KEY";
Parser := IndicatorBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 2;
Parser.StringPositionEnd := 7;
// Edit parameters of the COUNTRY attribute
CountryBinding := CubeMetaLoaderBindings.Add;
CountryBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
CountryBinding.Attribute := "COUNTRY";
CountryBinding.Field := "Country Code";
CountryBinding.FieldType := CubeMetaLoaderFieldType.Name;
CountryBinding.Dimension := Mb.ItemByIdNamespace("DICT_CTR", NsiDescr.Key).Bind As
IDimensionModel;
// IX_WEO - unique key of the DICT_CTR dictionary
CountryBinding.Index := "IX_WEO";
CountryBinding.KeyAttribute := "KEY";
CountryBinding.NameAttribute := "NAME";
Parser := CountryBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 3;
Parser.StringPositionEnd := 5;
// Edit calendar attributes and measurement units
LevelBinding := CubeMetaLoaderBindings.Add;
LevelBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
LevelBinding.Attribute := "DL";
LevelBinding.FieldType := CubeMetaLoaderFieldType.ConstValue;
LevelBinding.FieldValue := DimCalendarLevel.Year As Integer;
UnitBinding := CubeMetaLoaderBindings.Add;
UnitBinding.BindingType := CubeMetaLoaderBindingType.Unit;
UnitBinding.Field := "Units";
UnitBinding.FieldType := CubeMetaLoaderFieldType.Name;
Parser := UnitBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 0;
Parser.StringPositionEnd := -1;
ScaleBinding := CubeMetaLoaderBindings.Add;
ScaleBinding.BindingType := CubeMetaLoaderBindingType.Unit;
ScaleBinding.Field := "Scale";
ScaleBinding.FieldType := CubeMetaLoaderFieldType.Name;
ScaleBinding.Dimension := Mb.ItemByIdNamespace("UNITS", NsiDescr.Key).Bind As IDimensionModel;
// IX_WEO_UNIT_SCALE - unique key of the UNITS dictionary
ScaleBinding.Index := "IX_WEO_UNIT_SCALE";
ScaleBinding.KeyAttribute := "KEY";
Parser := ScaleBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 0;
Parser.StringPositionEnd := -1;
// Edit attributes containing values
ValueBinding := CubeMetaLoaderBindings.Add;
ValueBinding.BindingType := CubeMetaLoaderBindingType.Value;
ValueBinding.Field := "Value";
ValueBinding.FieldType := CubeMetaLoaderFieldType.Name;
Parser := ValueBinding.Parser;
Parser.ParserType := CubeMetaLoaderParserType.FixedPosition;
Parser.StringPositionStart := 0;
Parser.StringPositionEnd := -1;
// Edit loader parameters
Excel := New DtExcelProvider.Create;
Excel.ImexMode := DtExcelImexMode.Import;
Excel.File := "C:\Fixed.xlsx";
Excel.Query := "SELECT * FROM [Sheet1$]";
Excel.HasHeader := True;
Excel.CheckFieldName := True;
Excel.Open;
Provider := Excel As IDatasetDataProvider;
// Execute loading
CubeMetaLoader.Data := Provider;
TestResult := CubeMetaLoader.TestLoad;
Count := TestResult.Count;
Debug.WriteLine("Testing");
Debug.WriteLine("Total errors in log: " + Count.ToString);
If Count > 0 Then
For i := 0 To Count - 1 Do
TestResultEntry := TestResult.Item(i);
Debug.WriteLine(TestResultEntry.Attribute + " : "
+ TestResultEntry.Value + " = "
+ TestResultEntry.Count.ToString);
If i >= 10 - 1 Then
Break;
End If;
End For;
Else
CubeMetaLoader.Data := Provider;
CubeMetaLoader.LoadData;
Log := CubeMetaLoader.Log;
For Each key In CubeMetaLoader.InvalidFactors Do
Debug.WriteLine("Error on loading indicator with key: " + key.ToString);
End For;
Count := Log.Count;
Debug.WriteLine("Data loading");
Debug.WriteLine("Total records in log: " + Count.ToString);
For i := 0 To Count - 1 Do
Entry := Log.Item(i);
Debug.WriteLine(i.ToString + " : "
+ Entry.DateBegin.ToString + ","
+ Entry.RecordNumber.ToString + ","
+ Entry.Field + ","
+ Entry.ErrorMessage);
If i >= 10 - 1 Then
Break;
End If;
End For;
End If;
End Sub UserProc;
After executing the example test data loading from the file C:\Fixed.xlsx is executed, the first ten records from report on loading are displayed in the console window. If the test loading is successful, the factors are exported from file to the time series database.
See also:
