Create an Import Definition

XML (in a Defined Format) and Excel (Microsoft Excel File)

Source type: click the dropdown list to choose an XML file or an MS Excel file as a data source. Each worksheet in an MS Excel file is considered as a single table.

It is possible to use any version of Excel, but the result file should be in one of the following supported workbook formats:

• Excel 5.0/95 Workbook
• Excel 97-2003 Workbook
• Excel Workbook

OLEDB (generic OLE DB data source)

• Connection String: For OLEDB data source you should specify a valid connection string. With this, you can access a wide range of possible databases provided that the respective provider/driver is available on the Matrix42 Workspace Management server.

Refer to this website for information about connection string format and further examples.

Text File (including .csv)

• Delimiter: you can select between several predefined column delimiters or specify a custom one by clicking the Other option. Length of the custom delimiter should not exceed one symbol.
• First row contains column names: specify whether the first row contains column headers or data.
• Show as Quick Import Definition: checkbox is not available for all source types of the import definition. The data that you import usually comes from a file containing a table that should follow the rules of the respective import definition. To simplify import actions, you can use a predefined file for every import procedure of a specific type. You only need to enter your data in the file. Compared with normal data input in the Matrix42 Workspace Management user interface, this is much faster because you only have to enter your data in the predefined file and then import the file.

The following data extensions are supported:

• .txt
• .csv
• .tab
• .asc

The following character sets are supported:

• Unicode
• ANSI

 

MS SQL (Microsoft SQL Server database)

• Server: name of the Microsoft SQL server;
• Database: name of the Microsoft SQL database;
• Integrated Security: select this checkbox to access the server by using Windows authentication;
• Login, Password: user name and password under which you log on to the Microsoft SQL server unless the Integrated Security checkbox is selected. This information is encrypted and saved;
• Other Options: besides general settings, you can specify additional connection options. They are passed on to ".NET Framework Data Provider for SQL Server" without modifications. This information is encrypted and saved.
• Use application database (in DWP v.11.0.2 and newer): allows using the application's SQL database as an import data source. Available options:
  • disabled (default): fill out Server, Database, and other fields as described above to connect to the necessary Microsoft SQL database.
  • enabled: application's SQL database is used as a data source for the configured import. Other fields become disabled and no other extra credentials are required
    
    Example of the MS SQL import source type with enabled Use application database option
     

Source File Location Settings

The location of the text, MS Excel, and XML files are specified by using a separate dialog that has the following options:

• Upload: select this option if the file is located locally on a computer. Drag&drop the necessary file on the "Drop here" area of the page or click the "Drop here" block to browse and specify the path to the file manually;
• LAN: select this option if the file from the Matrix42 Workspace Management server is accessible locally or in a network. Manually enter the File Path in the corresponding field;
• Web: select this option if the file is located on a Web server and specify the File URL location.
• Cloud Drive: file is located on the Cloud Drive option is available if you have pre-configured Service Connections.

Login/Password fields are available if either LAN or Web option is selected as the location type. Enter the user name and password for accessing the network resource (optional).

Specify central table

By default, the system will automatically select the first table from your data source.

• Table/View: select this option to import data that can be retrieved as a table and specify the source for the data:
  • Name: select a table name from the drop-down list. The actual content of the drop-down list depends on the data source type. For instance, each spreadsheet of an Excel file is suggested in the list as a stand-alone table. 
• SQL Expression:  a valid SQL expression that may have particularities depending on the selected for import data source type:
  
  • MS SQL data source: enter a transact-SQL expression that is supported by your version of MS SQL Server. For information about examples and exact syntax, see the Transact-SQL Reference.
  • OLEDB data source: you can enter any valid Jet-SQL expression that is supported by Microsoft.Jet.OLEDB.4.0 provider;

XML data source type has other central table settings and requires only a Row Path which is a valid XPath expression that identifies the data row in the XML file and specifies whether columns are defined as row child nodes or row attributes:

For the following example files, the Row Path would be the same:/NewDataSet/Computers/Computer

Example file for child nodes containing data:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Computers>
<Computer>
<Name>LCM-NB01</Name>
<PrincipalUser>Vincent</ PrincipalUser >
<Status>710</Status>
</Computer>
<Computer>
<Name>LCM-NB02</Name>
<PrincipalUser>Mike</ PrincipalUser >
<Status>710</Status>
</Computer>
</Computers>
</NewDataSet>

Example file for row attributes containing data:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
    <Computers>
        <Computer Name="LCM-NB01" PrincipalUser="Vincent" Status="710"/>
        <Computer Name="LCM-NB02" PrincipalUser="Mike" Status="710"/>
    </Computers>
</NewDataSet>

 

Specify Additional Settings: the following fields are common to all data sources:

• Alias: here you can specify an optional alias for your source table or query. It will be shown during the mapping rules definition and is useful for queries or when a table name is too long.
• Foreign Key: used only when specifying several source tables. Enter a list of column names, separated by a comma, that compose the primary key for the central table or foreign key for child tables. Alternatively, you can just click the appropriate column header on the preview grid.
• Preview: The data preview grid displays the first 100 rows from your source object

Сentral table type

The central table of a data source can belong to one of two types.

Child tables (optional)

• Specify child tables (if required): if you choose the Simple table option, then an additional grid becomes available. Use this grid to specify any number of child tables. When you click the Add button, the source object details dialog for a child table opens. Set the foreign key for the child table and make sure that the primary key is specified for the central table. These fields link the central and child tables together.

Example: The structure that is described above is represented by two separate files or we connect directly to a relational database. The import definition allows you to specify several source tables.

It requires that you should have one "central" table that contains one row per object and any number of "child" tables that are related to the primary key of the central table:

Specify parameters

At this step, you can introduce certain parameters for this import definition. Parameters allow you to modify values of fields (columns) of the imported objects.

When parameters are added, you will be able to use them for establishing mapping rules for this import definition:

• If an import definition contains parameters, each time when you run the Execute Import Definition action the system will prompt you to enter the actual values for these parameters.
• If an import definition is run within a workflow by the Execute GDI Sequence workflow activity, the import definition should receive the required parameters from this workflow.

When an import is executed, it uses the entered values of parameters to transform the initial values that have been imported.

Use the Specify parameters grid to add a new parameter:

• Name: enter the arbitrary name without spaces;
• Type: select the type of value this parameter can take.

Mapping Rule Details

The mapping rule details dialog changes its appearance and a set of configurable fields depending on the source column type. In general, the mapping rule dialog allows you to specify the following information:

1. Define the source column;
2. Define the destination attribute;
3. Specify the relation settings (optional, for multi-fragment data definition import only);
3. Define if these columns are used as matching criteria for the imported data;

Mapping Rules for Table Columns

The following properties are available for the Table Column column type:

	Define source column	Example
Source Table	choose the source table from the drop-down list. The list of tables was defined at the previous step. If Alias was specified, it will be shown.
Column Type	select the Table Column option
Source Column	select the source column from the drop-down list
	Define destination attribute
Destination Data Definition	choose the data definition from the drop-down list, which contains all data definitions of the specified configuration item. The [Multi] postfix indicates a multi-fragment data definition.
Destination Attribute	choose the attribute from the drop-down list, which contains all attributes of the data definition selected at the previous step. If an attribute is a reference to another data definition, you will see the [Relation] postfix and cardinality of the corresponding relation. Reference attributes have  additional relational settings.
	Define matching options
	Additionally, modify the check-box values for specifying columns matching options. There are two types of matching criteria: Use this column to match criteria to the destination type: select the checkbox to include the rule in the matching criteria. It is available only if the source table is the central table and the destination data definition is not a multi-fragment. Use this column to match criteria to the multifragment data definition: This checkbox is used to match multi-fragments within the main destination object. It is available only if the destination data definition is multi-fragment. Initially, all mapping rules with the selected matching option will be added to the resulting matching query. However, you can customize the query in the query builder dialog.

Mapping Rules for Static Column

Define source column section has the following configurable properties:

• Source Table: choose the source table from the drop-down list. The list of tables was defined at the previous step. If Alias was specified, it will be shown.
• Column Type: choose the Static Column option.
• Column Name: enter the column name that will be displayed in the mapping rule definition dialog.
• Column Value: enter the static value that will be passed to the destination attribute. This value will be the same for all imported objects.

Define destination attribute properties are the same as  described above.

Mapping Rules for Source

This option of a mapping rule is used in conjunction with the transformation option. Matrix42 Workspace Management cannot transform values directly in a source table. Therefore, it creates a staging table and imports all source data into this table. By adding a mapping rule for the Source column type, you will only add a corresponding column to the staging table. The column will not be mapped to any destination attribute, but it can be further used in a transformation mapping rule.

• Define source column section settings:
  • Source Table: select the source table from the drop-down list. The list of tables was defined at the previous step. If Alias was specified, it will be shown;
  • Column Type: select the Source option;
  • Source Column: select the source column from the drop-down list.
• Define destination attribute:
  • Target Column Name: manually enter a name for the target column;
  • Column Type: select the column data type;
  • Column Length: the field is editable for certain column types. Enter the integer number to set the column length;
  • Column Scale: if it is a decimal number column, you can set the number of digits after the decimal point;
  • Column Precision: if it is a decimal number column, you can set the total quantity of digits.

Mapping Rules for Transformations

The transformation mapping rule allows you to modify values that are being imported into the system and extract data from an attribute that houses numerous properties.

• Define source column section settings:
  • Source Table: select the source table from the drop-down list. The list of tables was defined at the previous step. If Alias was specified, it will be shown;
  • Column Type: select the Transformation option.
• Define destination attribute:
  • Transform Expression: specify an ASQL expression. It can use those source columns for which a mapping rule of the Source type has been specified. You can also reference parameters if the import definition has any.

Example: the transformation expression uses the user Account Control column. Initially, this column is an attribute from Active Directory. It has been previously mapped to a column in the staging table by using the Source mapping rule. The ASQL expression also contains parameters that have been added for the import definition. When a user executes the import definition, they enter values for the parameters. The expression then uses these values and the value of the user Account Control attribute to insert a certain resulting value into the destination attribute in Matrix42 Workspace Management.

• Define matching options

Limitations of Mapping Rules

• One source table can be mapped to several destination data definitions, but you cannot map several source tables to one destination data definition.
• One source column can be mapped to several destination attributes, but you cannot map several source columns to one destination attribute.

Matching query and Query builder

In the mapping rule definition dialog, when all source columns have been mapped to destination attributes, you can customize the resulting query that will be used to match source objects with destination objects. Click Query Builder to open the query builder dialog.

As usual, when the dialog opens, it already contains some matching criteria. For a newly created import definition, they are combined by the AND operator. These are mapping rules that have either Use this column to match criteria to the destination type or Use this column to match criteria to the multifragment data definition checkbox selected. However, you can remove all criteria from the query builder and specify a more complex and precise query.

• AND/OR: select the logical operator if you have two or more matching criteria;
• Add Expression: click the button to add a new field where you can specify an ASQL expression as matching criteria;
• Add Condition: click the button to add a drop-down field where you will be able to choose from the added mapping rules. The field will contain only those mapping rules that have the matching criteria checkbox selected;
• Add Group: click the Group button to add a new group of conditions and a logical operator that will connect them. This group will be added into the upper-level group of conditions;
• Remove Group: click the button to delete the specific group of conditions and expressions that are connected by the operator;
• OK/Cancel: click OK to save the matching criteria or Cancel to leave the criteria unchanged.

Review validation details

When you leave the Mapping Rule Definition page, all mapping rules are validated. If any warnings appear, they will be shown in the Review validation details text box:

Validation includes the following checks:

• If the class has a mandatory attribute without a default value and this attribute is not mapped, then such class cannot be inserted.
• If the mandatory class cannot be inserted, then the entire object cannot be inserted.

Such warnings do not prevent the definition from execution, but the available actions become limited.

Select destination data definition/relation, which corresponds to one row in the source table

The drop-down list is shown only if you have selected the Multifragment Import option when choosing the source table type.

The complex table contains more than one row per destination object. It means that the information about multifragment class or the 1->N (N->M) relation exists in the table. Thus we can say that each row in this table corresponds to one row in the appropriate class/relation. This class/relation should be selected from the drop-down list.

Example: A table has more than one row per computer because it also contains the hard disk information. Thus each row in the file corresponds to one hard disk, and we select the Hard Disk multi-fragment.

When an import definition processes a complex table, it identifies records by using the resulting matching query. If it finds another row with the same criteria, it considers it to be the same object. In this case, the import definition ignores all data in such row, except fields that are mapped to the selected multifragment class/relation. In our example, it will be two fields, Model and Total Capacity, that are mapped to the Hard Disk multifragment:

There is also the Any Mapped Multifragment Class option in this drop-down list. If you choose it, then all fields that are mapped to a multi-fragment class will be processed in a duplicate row.

Specify import actions, which should occur for the entire CI and all mapped multifragment data definitions

Here you can customize the exact actions that the import definition can perform. The list contains records for the main configuration item and all mapped multi-fragment classes. You can specify the required actions separately for each multi-fragment class (with some limitations).

• Update: specify whether you want to update the matched records;
• Insert: specify whether you want to insert the non-matched records or skip them:
  • You cannot check this option for the main CI if the Object insert is impossible warning appears in validation details;
  • You cannot check this option for the multi-fragment class if the Class cannot be inserted warning appears in validation details;
  • If you select this option for the main CI, you should select this option for all mandatory multi-fragment classes;
• Delete: specify whether you want to delete Matrix42 Workspace Management records that do not exist in the source data. It is prohibited for the main CI. For multi-fragment classes, only records within the matched objects will be deleted.

Attributes that need special processing (optional)

You can also adjust actions on the attribute level. If you want special processing for some attributes, specify them in the appropriate grid. Only the mapped attributes will be shown.

For each attribute, you can select one of the following actions:

• Insert Only: an attribute's value can be only inserted (when a row is inserted) and is never updated;
• Update Only: an attribute's value can be only updated (the source value is ignored when a row is inserted);
• Skip: an attribute's value is neither inserted nor updated (useful if you use it only for matching);
• Any Action: the same as if you did not specify an attribute at all.

Execute import definition completely

• Parameters: if this import definition contains parameters, it is required that you enter parameter values for the definition to be executed. The type of value should match the parameter value type.
• Select Option: select the Execute import definition completely option to run the import definition without intermediate steps;
• Execute: click the button to run the import definition;
• Abort: you can interrupt the execution at any time by clicking Abort;
• Result Preview:
  • State: when the import is completed, you will see the result in the text box, e.g.: "Completed, Total Duration 0:00:04".
  • Progress: detailed information about the import execution and processed data statistics.

Executed import definition example:

Executing the Import Definition Step by Step

If you select the Execute import definition step by step mode, you will be able to execute the import gradually. This option is recommended because it displays the individual steps and any errors that occur.

• Fill: populating the staging tables in the Matrix42 Workspace Management database with the raw data from the data source;
• Match: matching the data in the Matrix42 Workspace Management tables with the new data that is imported to the staging tables;
• Import: actual import of the new data into the productive tables of Matrix42 Workspace Management.