Integration Models


Integration is a process that updates Product name - ev itsm.png databases using third-party data (CSV file, database or directory). It is used to synchronize data between the third-party application and Product name - ev itsm.png, e.g. employee directory.

Integration:

  • Is based on a model that maps third-party data and Product name - ev itsm.png entry points, e.g. fields in a connector associated with a given Product name - ev itsm.png object.
  • Indicates the type of actions to be performed on the Product name - ev itsm.png database such as the creation of new records and/or the update of existing records. Note: Existing records in the Product name - ev itsm.png database are searched for using a unique ID that is defined using the connector entry points.
  • Can be preceded by different processing:
    • Normalization processing run from the model, to convert third-party data before integrating it. This is used mainly for reference tables to avoid polluting the Product name - ev itsm.png database with values that have different spellings.
    • Preimport processing to run SQL scripts before running the model, e.g. data formatting, deletion, normalization, delta differencing.
  • Is executed using the Product name - ev itsm.png scheduler and can be run immediately or scheduled. Integration will appear in the scheduled tasks.
  • Generates an integration log displaying the records added to or modified in the Product name - ev itsm.png database and the errors, if any.
  • Is associated with a folder used to organize integration processing based on function.

Examples

  • You want to create an integration model using an Employee connector:
    • Connector entry points: name, first name, employee number, location etc.
    • Integration model: mapping between the third-party data, name, and the entry point, Name.

1. Unique ID: defined using the Employee Number or Email entry point. Used to identify each employee in a unique way in the Employee table.

2. Run normalization processing prior to data integration in the Title table. Values Mrs. and Miss are converted to Ms.

  • You want associate photos with employees using an integration model with the Photo Integration for Employees connector. Open url.png See Description

      Open url.png  See Other examples of unique ID and normalization

Notes

  • Connectors:
    • The list of connectors is comprehensive and defined by Logo - EasyVista.png. You cannot create new ones.
    • The contents of each connector is defined by Logo - EasyVista.png. You cannot modify it.
    • Connector fields belong to the main table of the Product name - ev itsm.png object. Fields belonging to related tables can be added.

      Example documentation icon EN.png  Equipment connector: Includes the fields in the Equipment table but also the Equipment Status field belonging to the related table, Status

  • Normalization rules:
    • They are stored in the Product name - ev itsm.png database and can be viewed by selecting Integration > Normalization.
    • They are managed in the connector fields and can be modified in each integration model using one of the normalized fields. Sheet full icon.png indicates that at least one normalization value exists for the third-party source field and enables you to access the definition screen directly.
    • The unique ID is defined using the normalization values of the connector entry points.
  • Errors that may occur when saving an integration model:
    • Association of one connector entry point with several third-party source fields.

      Example documentation icon EN.png  The connector entry point, Email is associated with two third-party source fields, login and e-mail

    • Association of one third-party field with several connector entry points.

      Example documentation icon EN.png  Third-party source field, Telephone associated with two connector entry points, phone and mobile

    • The unique ID is missing.
  • If integration cannot be run, check that the scheduler is started and correctly configured. Open url.png See the procedure.

   Open url.png  See How to choose the best tool for automating integration.

Caution

  • If you want to integrate a large volume of data requiring several updates in the Product name - ev itsm.png database:
    • First, perform a backup of the database so that you can restore the database if errors occur.
    • Perform the integration in a test database before doing it in the production database.
  • If you want to run an automatic integration using a CSV file, you must ensure that the file always has the same name and is located in the same folder and path. It should be in a predefined folder on the Web server.

Best Practice big icon.pngBest practice

  • To avoid running an integration simultaneously using the Product name - ev itsm.png scheduler and the smoIntegration tool, you should not use the same model. Duplicate the initial model and rename it clearly to indicate that this model is exclusively reserved for the smoIntegration tool.
  • To simplify mapping between the connector entry points and the CSV file data, ensure that this file contains a header row. Otherwise, the fields will be identified by the values read in the first row.

    Example documentation icon EN.png  

    • CSV file with a header row: Name, First Name
    • CSV file without a header row: Durand, Peter
  • Define the unique ID correctly so that it can be mapped with a unique record in the main table of the connector.
  • By default, integration is performed in the language of the logged-in user.
    • You can translate names into different languages manually if integration was unable to update them. Note: Consult the integration log to identify the relevant reference tables.
    • You can run an integration in each of the languages you want using translated files and the same unique ID unrelated to the language.
  • To reduce the number of manual corrections on Product name - ev itsm.png data after integration, you should make as many corrections as possible on data in the third-party source application. Follow the recommendations below before running the integration.
    • Assign a default value to fields without a value in the third-party source application:
      • Specify this value for all mandatory fields in Product name - ev itsm.png to avoid generating integration errors.
      • Specify a value that is easily identifiable to see the fields to be completed manually after integration if this information is not available before integration processing.

        Example documentation icon EN.png  Identify employees without an arrival date -->  enter the default value, 99/99/9999 in the Arrival field

    • For values related to the path (e.g. location, department, type) and checkboxes:
      • Use a unique ID defined using IDs instead of a string of characters. This ensures that the existing record in the table will be modified and that a new record will not be created.

        Example documentation icon EN.png  Location 07 = Office 1 is modified to Office 001

        • The location is searched for using ID 07. It is found ==> the string is modified.
        • The location is searched for using string Office 001. It is not found ==> a new record Office 001 is created and the existing string Office 1 is not modified.
      • Separate levels in the path using the slash /.

        Example documentation icon EN.png  Country/City/Level/Office --> values in the third-party source application: France/Paris/2/205, Canada/Quebec/5/513, etc.

      • Enter 1 for a selected checkbox and 0 for a non-selected checkbox.

        Example documentation icon EN.png  Employee whose function is Financial Approval Manager --> values in the third-party source application: 1 = Manager for Approvals box selected in Product name - ev itsm.png

  • View the list of values that have already been converted before defining a new normalization rule in the Integration > Normalization screen.
  • Check the integration log at the end of the processing. If there are too many errors, restore the Product name - ev itsm.png database using the backup. Correct the third-party source data and run the integration again.
  • If the running of integration B depends on the completion of integration A whose processing time is unknown (due to large data volumes, access time, etc.), we recommend that you schedule the tasks in a sequence instead of defining a specific time for integration B. You can do this by clicking Schedule icon.png in Integration > Integration Models and by selecting the Depends on option.

Screens description

Integration Models

Menu access: Integration > Integration Models

Step 1: Definition of the model

       Integration model - step 1.png

Model Name: Name of the integration model.

Folder Name: Name of the integration folder where the model is saved.

Update: Type of update to be performed on the main table of the connector.

  • Insertion & Update: New records will be created in the Product name - ev itsm.png database. Existing records will be updated.
  • Only Update: Existing records will be updated in the Product name - ev itsm.png database. New records will not be created.
  • Only Insertion: New records will be created in the Product name - ev itsm.png database. Existing records will not be updated.

Data Type: Source of the third-party data:

  • CSV: Text file in CSV format. Click [ UPLOAD ] to select it. Note: If you want to upload an Excel file, you must save it as a CSV file.
  • Data Source: Database. Click [ DEFINE ] to configure the elements for connecting to the database and extracting data. Note: The connection string depends on the server hosting the database. Open url.png See http://www.connectionstrings.com/:
    • Provider = sqloledb (if importing from an SQL Server database)
    • Data Source = name of the database server\name of the instance
    • Initial Catalog = database name
    • User ID = database connection identifier
    • Password = password for the database connection

Example documentation icon EN.png 

  • SQLSERVER: (Provider=sqloledb;Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=USERNAME;Password=PWDemoticon_wink
  • ORACLE: (username/pwd@servername)

Language: Language of the third-party data. By default, the language of the logged-in user is selected.

Connector Name: Connector used for defining the integration model.

Take First Row as Header (Note: Only for CSV files): Used to indicate if the first row in the file contains the field headers (box is checked) or if it contains values (box is not checked).

Create Log (Note: Only for CSV files): Used to indicate if a log file should be generated at the end of the integration processing. This file contains the automatically created SQL query for integrating file data in the Product name - ev itsm.png database.

Step 2: Mapping between the third-party source data and the Product name - ev itsm.png database

       Integration model - step 2.png

Source: List of fields in the third-party source application.

Destination: List of fields in the connector. Map each field in the Source column with the corresponding Product name - ev itsm.png field in the Destination column.

Default Value (Note: Only if the Source - Destination mapping is specified): Value to save in the Product name - ev itsm.png database if no value is defined in the third-party source application.

Step 3:

       Integration model - step 3.png

Unique Key Field (Note: Only for the connector's main table fields that are not referenced in related tables): Connector fields defining the unique ID of records in the Product name - ev itsm.png database. The number of unique key fields is indicated in the column header.

Automatic Insertion (Note: Only for related table fields): When third-party source application values do not exist in the Product name - ev itsm.png related table (generally reference tables), this is used to indicate if new records should be created (box is checked) or not (box is not checked).

Example documentation icon EN.png  In the Equipment model, the Manufacturer field belongs to the Manufacturers related table (Product name - ev itsm.png reference table). The third-party source application contains the ABC manufacturer that does not exist in this table:

  • Box is checked: The ABC manufacturer is created during integration.
  • Box is not checked: The ABC manufacturer is not created.

Normalization: Used to define the rules for converting third-party source values before integrating them in the Product name - ev itsm.png database. 

  • Click one of the icons below to open the Normalization window. Existing rules for the connector will be displayed.
    • Sheet full icon.png : At least one normalization value has been entered for the field.
    • Sheet empty icon.png : No normalization value has been entered for the field.
               Integration model - step 3 - Transcription.png
    • Value (Source): Value of the field in the third-party source application, e.g. Mrs.
    • Reference Value (Destination): Converted value to be integrated in the Product name - ev itsm.png database, e.g. Ms.
  • To view existing normalization values: Integration > Normalization.
             Integration model - Transcodage.png

Integration logs

       Integration model - Logs.png

Menu access: Integration > Integration Log

Integration Model: Name of the integration model.

Status: Current status of the integration, automatically updated at the end of the integration processing.

Start Date: Start date/time of the integration.

Duration (H:M:S): Duration of the integration in hours/minutes/seconds/milliseconds.

Connector: Name of the connector used for integrating data.

Unique Key Field: Field name used as a unique ID in the integration model, separated by semi-colons (;).

Data Type: Source of the third-party data:

  • CSV: Text file in CSV format.
  • Data Source: Database.

Qty Records: Number of total records processed during integration.

Support Person: Name of the person who ran the integration.

Qty Inserted Records: Number of new records added to the Product name - ev itsm.png database during integration. To see the details of records inserted or modified in the table, select the Records tab.

Qty Updated Records: Number of records modified in the Product name - ev itsm.png database during integration. To see the details of records inserted or modified in the table, select the Records tab.

List of tabs

Records: This displays the number of inserted and modified records for each table updated during integration. To see the details of records, click Display popup icon.png.

Errors: List of errors occurring during integration. The row number refers to the row in the data file used for integration.

Procedures and Wizards

How to create an integration model

1. Select Integration > Integration Models in the menu and click Add icon.png.

2. Specify the information for the new model in the different steps by clicking [ NEXT ] and then click [ FINISH ]. Note: If the integration model is incorrect, the relevant step will be displayed and errors will appear in red.

  • Indicate the source of the third-party data:
    • File data: Select CSV, click [ UPLOAD ] and select the file you want.
    • Database: Select Data Source and click [ DEFINE ]. Product name - ev itsm.png the database connection string and the SQL query for returning the data to be integrated.
  • Perform the mapping between third-party source fields and Product name - ev itsm.png connector fields:
    • Map each third-party source field in the Source column with the corresponding connector field in the Destination column.
    • To ignore certain third-party source fields, select the corresponding checkbox next to the field and click the Minus icon.png symbol (Note: The field will not be deleted, it will simply not be included in the mapping).
    • If you want to include a third-party field you previously ignored in the mapping, go to the row at the bottom of the table. If required, you can click Plus icon.png to add a row. Select the field you want from the Source drop-down list and map it with a field in the Destination column.
  • Specify the normalization values:
    • Check the list of existing normalization values by selecting Integration > Normalization.
    • Click Sheet full icon.png or Sheet empty icon.png for the Source field you want to normalize.
    • Indicate the third-party source value in the Value (Source) column.
    • Enter the value to which it should be converted in the Product name - ev itsm.png database in the Reference Value (Destination) column.
    • Click [ SAVE ] and close the window. The Sheet full icon.png icon is now displayed for the normalized Source field.
    • To define several normalization values for the field, click Plus icon.png to add a new row.
    • To delete a normalization value, select the checkbox next to the field and click Minus icon.png.

3. If you want to modify the integration model subsequently, select Integration > Integration Models and click Edit icon.png next to the model.

How to run an integration

Best Practice icon.png  First, perform a backup of the database so that you can restore the database if errors occur. This is strongly recommended when integrating a large volume of data.

1. Run the integration model you want:

  • Select Administration > Integration Models in the menu of a module and click Execute icon.png next to the model you want.
  • If you want to run an integration:
    • Using a CSV file: Specify the third-party source and select the Create Log box to generate an integration log. Click [ UPLOAD ].
    • Using data from a database: The processing is directly run.
  • Once the processing is completed, click [ CLOSE ]. The integration log screen will automatically appear.

Note: If you want to check the configuration of the integration model before running it, select Integration in the menu and click Edit icon.png next to the model you want. Click [ NEXT ] to see each of the different steps and click [ EXECUTE ] in the last step.

2. Click Display popup icon.png next to the integration log generated to display the integration report and errors, if any.

Best Practice icon.png  If there are too many errors, restore the Product name - ev itsm.png database using the backup. Correct the third-party source data and run the integration again.

3. Enter the translation for values updated in the reference tables:

  • View the integration log to identify related tables in which records were updated.
  • Open the related table by selecting References in the relevant menu.
  • Enter the translation of the new values. The names will be updated in the language of the user who ran the integration. In other languages, the names will appear between square brackets, [ ].

How to automate an integration

1. Select Administration > Integration Models in the menu and click Schedule icon.png next to the model you want.

2. Specify the settings in the Calendar window and click [ FINISH ].

  • Select the Enabled box to run the integration automatically. 
  • You can define two or more integration models to be run in a sequence using the Depends on option. Select the model that should be run once the earlier integration processing is completed.

How to consult an integration log

1. Display the list of log files and click Display popup icon.png next to the one you want:

  • By selecting Integration > Integration Log in the menu.
  • From another module by selecting Administration > Integration Log in the menu.

2. To see the details of records that have been inserted or modified in a table:

  • Select the Details tab.
  • Click Display popup icon.png in the Added or Updated column. You can also click the link indicating the number of records processed.

3. To view errors generated during integration, select the Errors tab.

Wizards

Integration Models

Move: Used to modify the folder associated with the integration model.

Edit icon.png (in the Integration > Integration Models menu): Used to modify the integration model.

Delete icon.png (in the Integration > Integration Models menu): Used to delete the integration model.

Schedule icon.png (in the Integration > Integration Models menu): Used to schedule an integration in the model. The settings will be taken into account from the next compute date and will depend on the type of frequency selected:

  • Every [N] Months/Days/Hours: Enter the frequency you want in universal time.
  • Depends on: This runs the integration automatically once another integration processing is completed (task sequencing). The list displays all existing integration models.
  • Next Compute Date (Note: Only if the frequency is not Depends on): Start date/time of the next integration processing refreshed each time an integration is run. To run an integration processing immediately, enter a date that is earlier than the current date.

        Best Practice icon.png  You can check if an integration is scheduled by selecting Administration > Scheduled Tasks > Integrations in the menu.

Execute icon.png (in the Administration > Integration Models menu of a module): Used to run the integration model.

Integration Log

Empty All Integration Logs: Used to delete all log files.

The tool for running integrations (smoIntegration)

smoIntegration is a tool for running integration models whenever required autonomously, without using the Product name - ev itsm.png scheduler. It requires the definition of command lines that can be added to a Batch or PowerShell script and run at a scheduled time using an external scheduler or third-party application.

Notes

  • smoIntegration is shipped as an executable in the <Easyvista>\tools\ folder (Note: <Easyvista> Identifies the EasyVista folder on your application server). 
  • Integration models must first be defined in Product name - ev itsm.png.

   Open url.png  See How to choose the best tool for automating integration.

Caution

  • To avoid running an integration simultaneously using the Product name - ev itsm.png scheduler and the smoIntegration tool, you should disable its execution in the scheduled tasks.

Best Practice big icon.pngBest Practice

  • To ensure optimal performance of the Product name - ev itsm.png platform:
    • You should schedule integration tasks to be run whenever possible outside platform production periods, i.e. at night, during weekends, or during periods when 24/7 platforms are accessed by the least number of users.
    • You should reduce the number of tasks run simultaneously by smoIntegration.
  • You can define a preimport task to run immediately using smoBackOfficeClient followed by immediate data integration using smoIntegration. To do this, add the two command lines to a Batch or PowerShell script.

Command line syntax

smoIntegration -account=CompanyAccount [-modelname="IntegrationModel"] [-ip=smoServerIpAddress] [-port=smoServerPort] [-test] [-list] [-listall] [-help]

List of parameters

Mandatory parameters

-account: Product name - ev itsm.png account of the company in which data must be integrated.

Example documentation icon EN.png  40000: Test database; 50004: Production database; 50005: Sandbox database

Optional parameters

-modelname: English name of the integration model. Note: Only indicates the English name of the model. Remember to add the double quote marks.
-ip: IP address of the smoServer service. Note: If it is specified in the command line, its value will overwrite the one found in the smoServer.ini file (IP_ADDRESS_USED variable).
-port: Port of the smoServer service. Note: If it is specified in the command line, its value will overwrite the one found in the smoServer.ini file (Port_ID variable).
-test: Used to test the command line to check the parameters without running the integration.
-list: Used to display the English name of existing integration models.
-listall: Used to display the names of existing integration models.
–help or /?: Used to display help resources for the smoIntegration tool.

Error management (list of return codes)

0: Processing completed without any error (an integration log is generated)
1: The smoServer service is not found.
2: The integration model is not found.
3: The Product name - ev itsm.png account is invalid.
4: A mandatory parameter is missing.
9: Integration failed. Note: This error code is never returned with the -test parameter.

Examples

  • You want to run integration model MD on test database Test 40000, using the smoServer service with the following IP address, 192.16.0.100:22451:
smointegration -port=22451 -ip=192.168.0.100 -account=40000 -modelname="MD"
  • You want to display all integration models existing in test database Test 40000, using the smoServer service with the following IP address, 192.16.0.100:22451:
smointegration -port=22451 -ip=192.168.0.100 -account=40000 -list'
Tags:
Last modified by Unknown User on 2017/06/26 16:48
Created by Administrator XWiki on 2013/03/14 16:22

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

Quick Call
SCCM Distribution
Actions
Reporting Tools
Purge of archives

Powered by XWiki ©, EasyVista 2017