The contents of the Sheet1 sheet in the file D:\Indicator_Data.xlsxDestField: Integer;
The DestField property returns the number of the field in the data consumer.
To get the number of the corresponding field in the data source, use the ICubeMetaLoaderLogEntry.SourceField property.
Executing the example requires that the repository contains a time series database with the TSDB_UNPIVOT identifier containing time series attributes with the COUNTRY and INDICATOR identifiers. The COUNTRY attribute must be a link to an MDM dictionary, and the INDICATOR attribute must be a link to a table MDM dictionary.
The file system must contain the file D:\Indicator_Data.xlsx containing a data sheet named Sheet1.
Add links to the Cubes, Db, Dimensions, Dt, Metabase, Rds system assemblies.
The contents of the Sheet1 sheet in the file D:\Indicator_Data.xlsx
Sub UserProc;
Var
mb: IMetabase;
desc: IMetabaseObjectDescriptor;
importObject: IImportRequestDefinition;
createInfo: IMetabaseObjectCreateInfo;
params: IImportRequestProviderParams;
importBinding: ICubeMetaLoaderBinding;
Bindings: ICubeMetaLoaderBindings;
rdsDictionary: IRdsDictionary;
excel: IDtExcelProviderEx;
instance: IImportRequestInstance;
importResult: IImportRequestResult;
Log: ICubeMetaLoaderLog;
LogStr: String;
count, i: Integer;
entry: ICubeMetaLoaderLogEntry;
rub: IRubricator;
Unpivot: IDtUnpivotProvider;
Attributes: IMetaAttributes;
Begin
MB := MetabaseClass.Active;
// Get and clear time series database
desc := mb.ItemById("TSDB_UNPIVOT");
rub := desc.Bind As IRubricator;
rub.Truncate;
// Create an object to import time series
createInfo := mb.CreateCreateInfo;
createInfo.ClassId := MetabaseObjectClass.KE_CLASS_IMPORTREQUEST;
// Set object parameters
createInfo.Name := "Import from file";
createInfo.KeepEdit := True;
createInfo.Permanent := False;
createInfo.Parent := desc;
importObject := mb.CreateObject(createInfo) As IImportRequestDefinition;
// Set type of data source, from which data will be imported
importObject.SourceType := ImportRequestSourceType.Provider;
// Set time series database, to which data will be imported
importObject.DestinationRubricator := desc.Bind As IRubricator;
// Set import options
params := importObject.ProviderParams;
// Remove constraint for maximum number of errors in report about data import
(params As ICubeMetaLoader).Log.MaxErrors := -1;
// Get attribute bindings
Bindings := params.Bindings;
// Get time series attributes;
Attributes := rub.Facts.Attributes;
// Get dictionary, to which the COUNTRY attribute refers
desc := Attributes.FindById("COUNTRY").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
// Create a binding for the COUNTRY attribute
importBinding := Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Attribute;
importBinding.Attribute := "COUNTRY";
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item(0).Id;
// Specify that data for attribute will be taken by number of field in data source
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Specify number of field
importBinding.Field := "0";
// Get dictionary, to which the INDICATOR attribute refers
desc := Attributes.FindById("INDICATOR").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
// Create a binding for the INDICATOR attribute
importBinding := 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(1).Id;
// Specify that data for attribute will be taken from number of field in data source
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Specify number of field
importBinding.Field := "1";
// Get dictionary, to which the UNIT attribute refers
desc := Attributes.FindById("UNIT").ValuesObject;
rdsDictionary := desc.Bind As IRdsDictionary;
// Create a binding for the UNIT attribute
importBinding := Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Unit;
importBinding.Dimension := rdsDictionary As IDimensionModel;
importBinding.KeyAttribute := rdsDictionary.Attributes.Key.Id;
importBinding.Index := rdsDictionary.UniqueKeys.Item(0).Id;
// Specify that specified value is taken as attribute data
importBinding.FieldType := CubeMetaLoaderFieldType.ConstValue;
// Set attribute value
importBinding.FieldValue := 1 As Variant;
// Create a binding for calendar
importBinding := Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Calendar;
importBinding.ByColumns := False;
importBinding.CalendarDateFormatEx(DimCalendarLevel.Year) := "$Year$";
importBinding.CalendarOptions.Levels := DimCalendarLevelSet.Year;
// Specify that data for calendar will be taken by field number in data source
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Specify field number
importBinding.Field := "2";
// Create a binding for time series values
importBinding := Bindings.Add;
importBinding.BindingType := CubeMetaLoaderBindingType.Value;
// Specify that values will be taken by field number in data source
importBinding.FieldType := CubeMetaLoaderFieldType.Index;
// Specify field number
importBinding.Field := "3";
// Set up data source
Try
excel := New DtExcelProviderEx.Create;
// Set parameters of the file, from which data will be imported
excel.File := "D:\Indicator_Data.xlsx";
excel.Sheet := "Sheet1";
excel.Format := "XLSX";
Excel.HasHeader := False;
// Set location of data areas in source file
Unpivot := New DtUnpivotProvider.Create;
Unpivot.Provider := Excel;
Unpivot.LeftEnd := 2;
Unpivot.DataWidth := 2;
Unpivot.DataTop := 3;
Unpivot.DataBottom := 5;
Unpivot.HeadBottom := 2;
Unpivot.DataRight := 8;
Unpivot.HeadColumn := 1;
Unpivot.LeftBegin := 1;
Unpivot.HeadTop := 2;
// Load data
Unpivot.Open;
count := Unpivot.Fields.Count;
importObject.ProviderParams.Provider := Unpivot As IDatasetDataProvider;
instance := (importObject As IMetabaseObject).Open(Null) As IImportRequestInstance;
Instance.LoadData;
// Get report about data loading
importResult := instance.ImportResult;
Log := importResult.Log;
count := Log.Count;
// Display report in the console window
Debug.WriteLine("Number of records = " + Count.ToString);
For i := 0 To Count - 1 Do
Entry := Log.Item(i);
LogStr := i.ToString + ": "
+ (Entry.IsError ? "Error: " :(Entry.IsMessage ? "Message: " : "Warning: ")) +
+ Entry.ErrorMessage;
Debug.WriteLine(LogStr);
Debug.Indent;
Debug.WriteLine("Record number = " + Entry.RecordNumber.ToString);
Debug.WriteLine("Record value = " + Entry.ValueRecordNumber.ToString);
Debug.WriteLine("Data source field = " + Entry.Field);
Debug.WriteLine("Record number in data source = " + Entry.SourceRecord.ToString);
Debug.WriteLine("Field number in data source = " + Entry.SourceField.ToString);
Debug.WriteLine("Record number in data consumer = " + Entry.DestRecord.ToString);
Debug.WriteLine("Field number in data consumer = " + Entry.DestField.ToString);
Debug.Unindent;
End For;
Except On e: Exception Do
Debug.WriteLine(e.Message);
Finally
End Try;
End Sub UserProc;
After executing the example data is imported from the file D:\Indicator_Data.xlsx into the TSDB_UNPIVOT time series database. Import errors are displayed in the console window.
See also: