In Data Import objects or Data Import Component Objects (can be delivered with a software component) is configured how single records or fields of a data source are converted to Fabasoft Folio objects and their properties.
Apply the following principles:
Note: In a Linux system environment the Fabasoft web service user (:<hostname>:\fscsrv) needs writing properties (“Change Properties“) for the Data Import object and the Data Import Log object. So for the Data Import object an appropriate ACL has to be selected. Additionally in the Log Object field or the Data Import, a Data Import Log has to be created manually before the first import and an appropriate ACL has to be selected.
Example of a Data Import:
In the Data Source property, select the type of data source (ODBC, OLE DB, Report Converter, Script, LDAP, ADE-DB, Spreadsheet Document or XML) and enter a reference to the data source. The available types of data sources are described in chapter “Definition of Data Sources”.
Examples of references:
Transport:e.g. “OCI” for Oracle, “PGSI” for PostgreSQL
Provider:e.g. “SQLOLEDB” for Microsoft SQL Server
Location: For defining the location
Datasource:For defining the data source
Catalog:For defining the name of the database catalogue
Username:Name of the database user
Password:Password of the database user
Connectstring:Additional settings (e.g. “PORT=5432” for PostgreSQL)
This property can be used to reference a Fabasoft object as data source.
Using “Spreadsheet Document”, “XML” or “File System”, data of a Content (Unknown Type) can be imported, which needs the value “xlsx”, “xls”, “sxc”, “ods”, “csv“, “xml” or “zip” in the property File Extension (COOSYSTEM@1.1:contextension).
In this property the name of the table (or the view) is defined, that contains the data to be imported.
This property is used to map external data to Fabasoft Folio properties.
In this field, imported objects can be assigned to object pointer properties.
This field is used to control the behavior of creating and updating objects of certain object classes. To search for objects different methods are available.
This section describes the various configuration options in property Avoid Duplicate Objects.
No Check of Existing Objects
This setting is recommended if the import process does not create objects that already exist in the Fabasoft product environment. A key property is not required. Objects are always created (even if Create Objects is set to "No"), except when key properties (one or more) are defined, but these are all empty.
Check by Collecting Keys of All Objects
In an initialization, a local search tree (Cache) is set up in which the key values and the object address of all objects of the object class are stored.
During the import, these keys are searched for a matching object. If no matching object is found, a new object is created and the key also is added to the list of existing objects. This method is useful if many changes are carried out, references are placed on objects or new objects are created and the number of existing objects is not too extensive.
Check by Query for Each Object
For each imported object, a search is performed, which checks whether the object already exists. The keys of found objects are stored locally and thus quickly found again. This method is much slower in finding than the method described above. It is particularly well suited when only few objects of a class are accessed. Moreover, less memory is needed.
Check by Query for Each Object (No Cache)
This option corresponds to the option "Check by Query for Each Object", but keys of found objects are not stored. It makes sense when objects are not used more than once or the memory consumption would be too great.
Check by Hash Table Query
The object address is calculated from the key value using a hash algorithm.
Check by Local Query
The keys of the objects that are created by the current import are stored. Not the server is searched for objects but the stored keys. This method is suitable if the keys of the imported data are not already in use for existing objects.
Use Same Object as Lower Object ID
This method can only be used in combination with another search method.
Using this option it is possible to make several list entries (based on one record) within one object, e.g. several phone numbers of a person.
This requires defining the keys and the search method for the object class. Further list entries can be defined using a higher object ID and selecting the search method “Use Same Object as Lower Object ID“.
The following graphic may serve as a decision aid for selecting the search method, but for each loader the right method must be verified.
Search via object address
If the object address of an object is known, it can be used as a key. Through that the performance is very high.
To do so, before the import static object classes should be exported and it should be possible to get the object address in a JOIN on the database using the key properties.
Search for the reference
The reference name of component objects can be used for searching. Thereby two possibilities are available: It is possible to use the reference including the software component or, if the reference is unique, it can also be used without software component. To use this option, for the Mapping the property Reference (COOSYSTEM@1.1:reference) has to be selected.
Get methods for key properties
Get methods for key properties can have the consequence that the value in the database does not match the value in the object property. So the query for each object will have another result than the check by collecting keys of all objects.
Set methods for key properties
If the value of a key property is changed by a set method, this change is not considered in the local search tree, whereby the search can get inconsistent.
It is advantageous to use only one key property per object class. Using search methods that search per object, in the database this property should be provided with an index on the value.
Particularly suitable as an index are External Key (COOSYSTEM@1.1:objexternalkey) and Subject (COOSYSTEM@1.1:objsubject). The property External Key has been defined for this purpose, but has the disadvantage that it is not efficiently indexed. The property Subject is displayed in many forms and is only suitable if the key values contain “readable” information. This property is after definition of an index very efficient in finding.
Use this property to determine the maximum number (and using the group change also the minimum number) of records to be processed in one transaction.
Some actions when loading objects can be performed on multiple objects (for example, the "commit", which writes the data to the database), whereby the number of communication steps is greatly reduced. However, very large transactions involve the risk that they collide with other transactions, and thus take longer than several small transactions. In many cases the default value of 150 is a value that leads to a good result.
Use this field to define the number of threads that are used by the client to generate objects. By multiple threads, it is possible to parallelize the processing. Depending on the type of import (e.g. creating or modifying objects) and, depending on the performance of individual components, a large increase in performance can be achieved. On the other hand, a certain synchronization effort is connected with the use of parallel transactions, so that an optimum value mostly is found in a small number (less than 5). The optimum value should be determined experimentally for the particular application.
The use of numerator properties prevents the use of multiple threads, because numerators remain locked until the end of the "Commit" and therefore allow no more parallelism. The restriction applies only when new objects of that class may be created (hence if Create Objects is set to "Yes" or "Undefined").
In this property, specify the record number that is applied in the database to the record from which starts the loading process.
Use this property to determine the number of records to be loaded. Otherwise all records are loaded.
Use this property to determine whether a log is created or not and which information of each import process is logged.
The created logs are written to a Data Import Log object which is assigned to this field.
Use this property to determine the maximum number of logs in the log object (Data Import Log).
Note: If the maximum number of logs is exceeded the eldest log is deleted.
Use this property to determine whether a review action is called, which determines whether object classes and attributes match the specified object classes and attributes.
Use this property to determine whether the properties Last Change on/at and Last Change by are updated for the objects that are changed during the data import.
Use this property to determine whether the data import is aborted if an error occurs.
Use this property to determine whether objects are updated during import or not. If it is ensured that problems such as inconsistent caches cannot occur, the data import can be accelerated using this option (e.g. for the initial data import).
On the “Filter” tab of the data import object, filters can be configured. The available properties are described in the following chapters.
This property defines an expression, which is called for each record of the database. The expression can be used to modify raw data. More information about the parameters contained in the global scope can be found in the reference documentation.
For modifying raw data, a Fabasoft app.ducx expression can be used.
The modification of raw data can include
This property defines an expression, which is called for each handled record after all properties from the current record have been set and before the transaction is committed. More information about the parameters contained in the global scope can be found in the reference documentation.
This property defines an expression, which is called for each data record after the transaction has been committed. More information about the parameters contained in the global scope can be found in the reference documentation.
The value of numerators is calculated by default from the property constructor. This is done as follows: The numerator is locked in a separate transaction, subsequently the value is increased and finally the process is finished by a "commit".
Using Fabasoft Folio/COLD the following possibilities are available for handling numerators:
Note: The import can be done using only one thread.