Integration Models
WarningSaveDatabase
Definition
An integration model is used to import data from external data sources, e.g. CSV files or databases, to a Service Manager database.
EndDefinition
- It is built using a connector that indicates the main table fields and the related table fields that can receive data.
- A mapping is performed between the connector fields and the Service Manager table fields.
- The search for existing records within the Service Manager database is run using unique keys built using connector fields.
- Data is integrated when you run the integration model using the Service Manager scheduler.
- The processing can be run immediately or scheduled.
- It can be run after a preimport processing that will first run SQL scripts for preparing the data import, e.g. tables purged, data filtered, data standardized, etc.
- Normalization processing is used to convert external source values prior to their integration in the Service Manager database, in order to standardize them in line with the existing repository.
- The integration log is used to display all records integrated in the Service Manager database, and errors if any.
- Integration models can be stored in folders that are organized by function.
Notes
- You can map a given source field with two different target fields in the connector.
example Phone field in the external source mapped with two target fields in the connector, Phone and Mobile
- You cannot do the reverse and map two different source fields with a single target field in the connector.
example The two fields in the external source, Login and Email cannot be mapped with a single target field, Email in the connector.
- You can run the integration model via an external scheduler or third-party application using the smoIntegration tool.
See How to automate an integration.
Caution
- You should always perform a backup of the Service Manager database prior to running an integration model. This ensures that you can restore the database if there are too many problems or errors.
- If you want to run an integration model defined using a CSV file automatically, 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 EasyVista) Web server.
- Integration models are unable to perform data integration on an incremental basis. All of the data in the CSV file will be imported into the Service Manager database, regardless of whether or not the data was modified after the last integration was performed.
- When the integration language is English, this will integrate all data in US English. Date formats, however, are different in US English and UK English. The US short date format is mm/dd/yyyy while the UK short date format is dd/mm/yyyy, just like the French short date format. As such, the integration of dates for UK English users will be incorrect because the US date format will be applied.
example Perform an integration for an equipment file containing purchase dates using English as the integration language
- Data file: Oracle server S131, purchased on 12/05/2023
- Integration results in English: Purchase date= 05/12/2023
==> UK English users will see a date that does not correspond to their expectations and that will cause confusion, Purchase date= 12/05/2023.
There are two solutions for integrating dates in the UK date format. See the procedure
- Modify the dates in the data source by applying the UK date format to dates in the data file prior to running the integration using English as the integration language.
- Perform the integration in two stages. First, run an initial integration model using English as the integration language to integrate standard data, without any dates. Second, run another integration model using French as the integration language to integrate only dates according to the UK date format.
Best Practice
- You should first run the integration model in the test database. This way, you can correct any errors displayed in the integration log before running the integration model in the production database.
- By default, integration is performed in the language of the logged-in user.
- You can use a multilingual connector whose name ends with - Multilingual to integrate external data in the six standard Service Manager languages.
- You can also translate labels into different languages manually in the interface if the integration was unable to update them. Consult the integration log to identify the relevant reference tables.
- If you added a non-standard language using EasyVista Translator, you should import the package below which integrates the dedicated columns for additional languages, L1 to L6, and then translate text strings in the additional columns L1 to L6.
. Import the file in Service Manager by selecting Administration > Import/Export > Import in the menu.
example Translation of the location in the dedicated columns for additional languages, L1 to L6, after importing the package
- To simplify mapping between connector fields and fields in the CSV file, ensure that the CSV file contains a header row. Otherwise, the fields will be identified by the values read in the first row.
example
- CSV file with a header row: Name, First Name
- CSV file without a header row: Durand, Peter
- To reduce the number of manual corrections to be made after data integration in Service Manager, you should correct the external source data as much as possible prior to running the integration.
- Run normalization processing on the external source data if the integration is intended to update reference tables and catalogs. This avoids polluting the existing repository with duplicate values corresponding to the same reference.
example
- Replace the values, Hewlett P and Hewlett Packard with the value stored in the Service Manager database, i.e. HP
- Replace the title, Mrs. with the value stored in the titles table, i.e. Ms.
- Assign a default value to fields without any value in the external source if they correspond to mandatory fields in Service Manager.
- We recommend you use unique keys built 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 Location 07 = Office 1 is modified to Office 001
- The location is retrieved using its ID 07 that exists in the database ==> The label will be modified.
- The location is retrieved using the new label Office 001 that does not exist in the database ==> A new record, Office 001 will be created. The existing record whose label is Office 1 will not be modified.
- Separate the levels of Tree fields using the slash /.
example Location: Country/City/Level/Office ==> Values in the external source: USA/Chicago/2/205, Canada/Quebec/5/513
- Enter 1 for a selected checkbox and 0 for a non-selected checkbox.
example Employee who is a financial approval manager ==> Value in the external source: 1, corresponding to the Manager for Approval box selected in Service Manager
- Run normalization processing on the external source data if the integration is intended to update reference tables and catalogs. This avoids polluting the existing repository with duplicate values corresponding to the same reference.
- 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 running integration B.
- To avoid running an integration simultaneously using the smoIntegration tool and the smoScheduler scheduler, 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.
Ensuring optimal network performance
- Schedule the running of integrations during periods where users do not need to access Service Manager, i.e. at night or on weekends.
Menu access
- Administration > Integration > Integration Models
- General > Integration > Integration Models
Note: Provided that the profile is authorized to access the menu Integration > Integration Models.
Screens description
Integration model
Step 1: Define the model
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 in the main connector table.
- Insertion & Update: New records will be created in the Service Manager database and existing records will be updated.
- Only Update: Only records existing in the Service Manager database will be updated. New records will not be created.
- Only Insertion: Only new records will be created in the Service Manager database. Existing records will not be updated.
Data Type: Source of the external data.
- CSV: Text file in CSV format.
- datasource: Database. You must click Define to define the database connection string and the SQL query for retrieving the data to be integrated.
See List of connection strings.
| Information | Example | |
|---|---|---|
| Connection string
List of parameters for SQL Server:
|
Provider=sqloledb;Data Source=EZV_SQL1;Initial Catalog=EVO_BACKOFFICE; User Id=myLogin;Password=myPassword |
|
| Query: Used to select the data. | SELECT * FROM EVO_BACKOFFICE.EZV_ADMIN.myTable |
Language: Language of the external values.
- By default, the language of the logged-in user is selected.
Connector Name: Connector used for building the integration model.
- Specific rules can be defined for each connector. See the description.
You can use a multilingual connector whose name ends with - Multilingual to integrate external data in the six standard Service Manager languages.
Take First Row as Header (Note: Only for CSV files): Used to indicate if the first row in the file contains the field names of the external source (box is checked) or the field values (box is not checked).
Create Log: Used to indicate if a log file should be generated at the end of the integration processing (box is checked) or not (box is not checked).
Create a log file to view integration errors.
Step 2: Map the fields
Source: List of fields in the external source.
Destination: List of target fields in the connector.
- Map each field in the Source column with the corresponding Service Manager database field in the Destination column.
Default Value: Value to be saved in the Service Manager database if no value is defined in the external source.
- Assign a default value to fields without any value in the external source if they correspond to mandatory fields in Service Manager.
- To identify records easily after data integration, you can assign a given default value to each of them in one of the available fields.
example Identify employees integrated from the LDAP directory: Assign the default value, LDAP in AVAILABLE_FIELD_1.
Step 3: Define the properties of fields
Unique Key Field (Note: Only for the connector's main table fields that are not referenced in related tables): Connector fields defining the unique key of records in the Service Manager database.
- The number of unique key fields is indicated in the column header.
- Two unique keys are required for related tables in the connector.
example
- Unique key (1) = Only one unique key is required ==> Key login for the integration of employees
- Unique key (2) = Two unique keys are required ==> Attributes table related to the Equipment table
Automatic Insertion (Note: Only for related table fields): If the external source value does not exist in the related table, this is used to indicate if a new record should be created in the Service Manager database (box is checked) or not (box is not checked).
example The Equipment connector uses the main table, AM_ASSET and the related table, AM_MANUFACTURER. The external source contains manufacturer ABC that does not exist in the related table.
- Box is checked: Manufacturer ABC will be created in the related table, AM_MANUFACTURER.
- Box is not checked: Manufacturer ABC will not be created.
Clear if empty (Note: Only for non-Tree fields in related tables): If the external source value is not specified, this is used to indicate if the existing value in the Service Manager database should be reset (box is checked) or not (box is not checked).
- If the box is checked, only the value of the record will be reset to empty. The record will not be deleted.
example The title is not specified in the external source for employee Adam Smith. In the Service Manager database, it is Mr.
- Box is checked: The value of the title will be reset to empty for employee Adam Smith. The Mr. value will be deleted from the Service Manager database.
- Box is not checked: The current value, Mr. will be kept in the Service Manager database.
Normalization: Used to convert external source values prior to their integration in the Service Manager database, in order to standardize them in line with the existing repository. See the description.
- Unique keys can be normalized.
- Click the icon to open the Normalization window. The existing rules defined for the connector field will appear. See the detailed procedure.
: One or more normalization values have been specified for the field.
: No normalization value has been specified for the field.
Scheduling an integration model
Note: These parameters are not taken into account for scheduling integration models using an external scheduler.
Enabled: Used to indicate if the scheduled integration model is enabled (box is checked) or not (box is not checked).
Do not select this box if you want to schedule the integration model using an external scheduler.
Frequency: Used to define the frequency for running the integration model.
- The frequency will be applicable after the date specified in the Next Compute Date field.
- The parameters depend on the selected frequency.
- Every [N] Months / Day / At (Hour): Define the relevant frequency using Universal Time (UT).
- Depends on: Used to sequence the running of two integration models successively. Select the integration model whose processing precedes the execution of your integration model from the drop-down list.
Next Compute Date (Note: Only if the frequency is not Depends on): Date and time at which the integration model should next be run.
- The date initializes the start of the frequency.
example Frequency: Every seven days at 7 am; the next compute date is a Saturday ==> The model will run every Saturday
Enter a date earlier than the current date in order to run the integration model immediately.
Integration File: Path of the CSV file to be integrated.
Procedures and Wizards
How to create an integration model
CreationProcedure
Step 1: Create the integration model.
1. Select Administration > Integration > Integration Models in the menu.
2. Click + New.
Step 2: Define the general properties of integration model.
1. Enter the name and folder of the integration model.
2. Select the type of update you want.
3. Indicate the source of the external data.
- Integrate a file
- Select the CSV data type.
- Click Upload and select the file.
- Integrate data from a database
- Select the datasource data type.
- Click Define.
- Specify the database connection string. See List of connection strings.
- Enter the SQL query for retrieving the data to be integrated.
4. Select the connector.
5. (optional - Note: Only for a CSV file) Select the Take First Row as Header and Create Log boxes if required.
6. Click Next.
Step 3: Map the external source fields with the connector fields.
1. Select a connector field in the Destination column corresponding to each external source field in the Source column.
- If you want to ignore certain external source fields:
- Select the box at the start of each row.
- Click
.
These fields will not be deleted. They will simply not be included in the mapping.
- To reestablish the mapping for a previously ignored external source field:
- Click
to add a new row. - Select the field in the Source drop-down list.
- Map the external source field with a field in the Destination column.
- Click
2. Click Next.
3. Specify the properties of the fields.
- Select the fields used to build the unique key.
- Select the Automatic Insertion box to create a new record in the Service Manager database if the value of the external source does not exist in the related table.
- Select the Clear if empty box to reset the existing value in the Service Manager database if the value of the external source is not specified.
- Define the normalization values. See the detailed procedure.
Step 4: Save the integration model.
1. Click Finish.
Note: Blocking errors are indicated in red and the relevant step will automatically be displayed.
EndCreationProcedure
How to integrate dates in the UK date format
See the description of the problem
Solution 1: Modify the dates in the data source
1. Open the external data source and apply the UK date format, i.e. dd/mm/yyyy, to all dates.
2. Create a new integration model.
- Select the data file with the modified dates.
- Select the integration language, English.
- Map the fields of the connector with those of the external data source.
3. Run the integration model.
All data will be integrated in English. Dates will be integrated in the UK short date format, i.e. dd/mm/yyyy.
Solution 2: Perform the integration in two stages
1. Run an initial integration model to integrate standard data.
- Select the data file.
- Select the integration language, English.
- Map the fields of the connector with those of the external data source and ignore the Date fields. Click next to the relevant rows.
- Run the integration model.
All standard data will be integrated in English.
2. Run a second integration model to integrate dates.
- Select the data file.
- Select the integration language, French.
- Map the fields of the connector with those of the external data source. Select only the Date fields. Click next to all rows that are not dates.
- Run the integration model.
All dates will be integrated in the UK short date format, i.e. dd/mm/yyyy.
You can schedule the two integration models to run in a sequence, so that the integration of dates is performed immediately after the integration of standard data.
How to normalize external source values
Normalization_Procedure
Step 1: Open the Normalization window.
1. Select Administration > Integration > Integration Models in the menu to open the integration model.
2. Click Next to proceed to step 3.
3. Click or next to the field to be normalized.
- : One or more normalization values have been specified for the field.
- : No normalization value has been specified for the field.
The Normalization window will appear.
Step 2: Define the normalization values.
1. Enter the external source value in the Value (Source) column.
2. Enter the new value to be integrated in the Service Manager database in the Reference Value (Destination) column.
3. Click Save.
4. Close the window.
You will return to the list of properties for the integration model fields. will appear next to the Source field.
Add a normalization value for the field
- Click .
- Repeat the procedure above to enter the value.
Delete a normalization value
- Select the box in the relevant row.
- Click .
How to run an integration model using the Service Manager scheduler
See How to automate an integration to run an integration model using an external scheduler or third-party application.
Run the integration model immediately
ImmediateIntegration_Procedure
1. Select Administration > Integration > Integration Models in the menu.
2. Click 
next to the integration model.
3. Click Next to proceed to the last step of the integration model.
4. Click Execute.
- The integration model will be placed in the smoScheduler scheduler queue. It will run after the tasks currently being processed.
- The list of integration logs will appear at the end of the processing.
- The Succeeded status means that the integration was executed successfully.
5. Check the updated records.
- Click
next to the generated integration log.
- Click the Added or Updated link in the Records tab to display the list of updated records.
- Click the Errors tab to display integration errors and warning messages.
EndImmediateIntegration_Procedure
Schedule the integration
ScheduledIntegration_Procedure
1. Select Administration > Integration > Integration Models in the menu.
2. Click 
next to the integration model.
The window for scheduling the task will appear.
3. Select the Enabled box and specify the scheduling information. See the description.
Note: The CSV file must always be located on the EasyVista Web server in the folder called Resources/<Account>/Integration. <Account> corresponds to the Service Manager database where the data should be integrated.
Enter a date earlier than the current date in order to run the integration model immediately.
4. Click Finish.
The data will be integrated when the integration model is next run.
5. Check the integration log and correct the errors, if any.
EndScheduledIntegration_Procedure
Wizards
Specific use cases
- Integration model based on the Photo Integration for Employees connector. See Implementation.
- Update the Service Manager knowledge base using the integration model built using the dedicated connector called Known errors & Knowledge (Self Help Extractor). See Implementation.