Creating Import and Export Schemas

Import and export schemas are sets of parameters that enable the user to load and unload data.

NOTE. Creating import/export schemas is available only in the desktop application.

Import

An import schema of an MDM dictionary is a collection of parameters, based on which data is loaded from table to MDM dictionary.

To create a list of import schemas, use the Import tab on the Description tab in the MDM dictionary window opened for edit:

Adding Import Schema

Specify import schema name and identifier in the corresponding boxes.

NOTE. Only English letters, numbers and underscores are allowed; spaces are not allowed.

Table. After the radio button is selected, specify physical name of the table, from which data is loaded, to the field located below. Table name can also be selected in the drop-down list. The drop-down list displays repository tables located in the same database as MDM dictionary.
Query. After the radio button is selected, enter SQL query to the field located below:

Loaded data can be filtered. To do this, specify filtering condition in the Filter box by using field identifiers of the table, from which data is loaded.

A unique key is a basic identifier, which guarantees uniqueness of imported records.

Select the unique key created on the Unique Keys tab in the drop-down list. If the [None] option is selected, the system attribute with the KEY identifier is used as a default unique key.

Select element import mode in the drop-down list:

Replace All Elements. It is used by default. All elements are imported. In this case all elements contained in the dictionary will be replaced.
Add New (Missing) Elements. The elements missing in the current dictionary will be imported.
Update Existing Elements. Only existing dictionary elements will be refreshed on import.
Add New Elements and Update Existing Ones. The elements missing in the current dictionary will be imported, values of existing elements are refreshed.

Loaded data will be checked for correspondence to all user unique keys. The checkbox is selected by default. The checkbox is available for outdated MDM dictionary types.

The Attribute Links list consists of two columns:

Attribute. The column shows identifiers of all existing dictionary attributes.
Field. Specify field identifiers of the table, from which data is loaded, next to attribute identifiers. SQL functions can also be specified next to attribute identifiers in the Field column, for example:

SQL functions can be used for every source type.

NOTE. When linking a table field to the KEY dictionary attribute remember that MDM dictionaries support element keys within the range 0-2 billions.

Operations with Import Schemas

To edit the properties of an already selected import schema:

Click the Edit button.
Select the Edit item in the import schema's context menu.
Double-click the import schema name.

Make necessary changes in the Data Import Parameters dialog box.

To delete the selected import schema:

Click the Delete button.
Select the Delete item in the import schema's context menu.
Press CTRL+DELETE.

A confirmation to execute the operation is requested.

A created import schema can be applied to load data from table to dictionary.

A detailed description of the operation is given in the Filling Dictionary with Data section.

Import Features

On import of a version dictionary a new version of elements is created, dates of their modification are added to appropriate fields.

The following options are used to import a version dictionary:

Full import. All element versions that exist in the table are imported from the table containing dates of adding and removing the elements. This option is used if the Replace All Elements import mode is selected.
Incremental import. The versions of elements that are not removed at the moment or are modified from the previous import are imported from the table. This option is used if the Add New Elements and Update Existing Ones import mode is selected.

To set up import parameters for multiple attribute values, use a drop-down attribute group in the Attribute Link list. The group name matches the identifier of the attribute that can have multiple values.

Each element of the multiple attribute is imported from two tables: the main table, which physical name is specified in the Source group, and the child table specified in the Attribute Links group. Attribute parameters are imported from the main group, multiple values are imported from the child table.

The example of the Data Import Parameters dialog box for setting up import to dictionary containing the PARTNERS multiple attribute:

Set import parameters for multiple attribute values:

Field. Specify identifier of the main table field; the main and the child tables are linked by this field.
Import Table. Specify physical name of the child table.
Key, Value, Value Number. Specify identifiers of child table fields containing attributes with key, value and value number.

To set up import parameters for linked attribute values, use a drop-down attribute group in the Attribute Link list. The group name matches the identifier of the linked attribute.

The example of the Data Import Parameters dialog box for setting up import to dictionary containing the LINK_ATTR linked attribute:

Set import parameters for linked attribute values:

Field. Specify table field identifier.
Expandable attribute group. Specify field identifiers of the table, from which data will be loaded. The group name matches the identifier of the link established for the attribute. The group contains identifiers of the dictionary attributes, with which a link is established.

Export

An export schema of an MDM dictionary is a collection of parameters, based on which data is loaded from the MDM dictionary to a table.

To create a list of export schemas, use the Export tab on the Description tab in the MDM dictionary window opened for edit:

Adding an Export Schema

Specify export schema name and identifier in the corresponding boxes.

NOTE. Only Latin letters, numbers and underscores are allowed; spaces are not allowed.

In the drop-down list select physical name of the table to export data in. Do not specify the name of the table, which does not exist.

Select this checkbox to export data automatically. On automatic export only the data is exported, which was not in the consumer table before export, that is, the data of the dictionary, which is created or changed.

The list of attribute links consists of two columns. The Attribute column displays identifiers of all existing dictionary attributes. In the Field column specify identifiers of fields of the table, to which the data will be unloaded, next to the identifiers of attributes.

Operations with Export Schemas

To edit properties of the selected export schema:

Click the Edit button.
Select the Edit item in the export schema's context menu.
Double-click the export schema name.

Make necessary changes in the Data Export Parameters dialog box.

To delete the selected export schema:

Click the Delete button.
Select the Delete item in the export schema's context menu.
Press CTRL+DELETE.

A confirmation to execute the operation is requested.

A created export schema can be applied to unload data from dictionary to table.

A detailed description of the operation is given in the Filling Dictionary with Data section.

Export Features

To set up export parameters for multiple attribute values, use a drop-down attribute group in the Attribute Links list. The group name matches the identifier of the attribute that can have multiple values.

Each element of the multiple attribute is exported to two tables: the main table, which physical name is specified in the Table Name field, and the child table specified in the Attribute Links list. Attribute parameters are exported to the main table, multiple element values are exported to the child table.

The Export Data Parameters dialog box for setting up export of the dictionary containing the ATTRIBUTE multiple attribute:

Set export parameters for multiple attribute values:

Field. Specify identifier of the main table field; the main and the child tables are linked by this field.
Export Table. Specify physical name of the child table.
Key, Value, Value Number. Specify identifiers of child table fields containing attributes with key, value and value number.
Export Mode. Specify the mode of updating records in child table. Available options:

Recreate Records. The option is selected by default. First, all the records in the child table are deleted, next values of multiple attribute are exported.
Update Records. Values in the child table and values of the multiple attribute are compared during the export. If the child table does not contain a record corresponding to a value of the multiple attribute, this record is added to the table. If the child table contains a record, which does not correspond to any of the multiple attribute values, this record is deleted. If the child table contains a record corresponding to a value of the multiple attribute, this record is refreshed.