Data exchange

In this article

    The Customer Service Data Exchange is a module which will synchronize data between Customer Service and other systems. Except for certain special cases, the integration is mainly an asynchronous task, running as a service on the Customer Service server. This is due to the fact that the Customer Service system is not capable of displaying information from other systems real-time in the user interface. Consequently, information must first be copied from the external source into the Customer Service database, and then it will be available inside the Customer Service user interface.
    Normally, the integrations will be one-directional, i.e. information will be copied from an external system (which “owns” the data) into Customer Service, or information from Customer Service will be exported to another system. Allowing data to be modified in two systems, and then synchronizing the changes, is possible but quite complex and also requires solutions for handling collisions. Consequently, we highly recommend integrations to be one-directional.
    Most integrations are focused on making information available in Customer Service so that the support process can be more efficient. 

    Examples of other common integrations are:

    • Integrate users in Customer Service with users from a Windows Active Directory through LDAP.
    • Integrate customers in Customer Service with users from a Windows Active Directory through LDAP.
    • Integrate equipment database from inventory or CMDB solution.
    • Integrate list of invoices for companies from ERP system.
    • Integrate list of purchased products and SLA’s from CRM/ERP system.

    Technical overview

    The Customer Service integrator is built up as follows:

    The Dbi.exe will be executed routinely, and execute various synchronization tasks which may again have various schedules. The logical layout of the dbi is as follows:

    The system can be set up with any number of agents, which again may contain any number of tasks. Typical tasks may be:

  • Import all companies from external system (typically once every night).
  • Import all changed companies from external system (typically every minute).
  • Import all changed customers (dependent of company task).
  • Export all requests to external system.
  • Export all new invoice entries to XML file.
  • Tasks can be executed with different schedules independently, and can also be performed in dependent series (i.e. first companies, then customers, then requests).

    Agents

    Normally, one agent is set up for each external system Customer Service should be integrated with. In Customer Service the agent entry will specify a protocol it will use to interact with one of the agent executables (ODBCAgent.exe etc). Furthermore, the agent entry will contain parameters which are passed to the agent executables, such as connection information for the executable. All parameters for the agent executables are configured inside Customer Service. The parameters vary for the different agent executables and are specified later on in this document.

    An agent entry will also contain a list of the fields which are integrated. This list is only used internally by the system to mark these fields as read-only in the user interface. This is because it is possible to have a data structure with more fields in Customer Service than the ones which are integrated. For example, one may integrate companies from a CRM system, copying the name, phone and address of the company. In Customer Service one may have additional fields, such as SLA-level or primary contact. These fields must be editable in Customer Service, since they do not originate from the CRM system. Consequently, for the agent entry, we specify that this agent integrate certain fields, which are not editable in the eJournal user interface. The remaining fields, which are not specified in the agent entry, will be editable in the Customer Service user interface.

     

    Finally, the agent entry has multiple tasks. These are specified in the following section.

    Tasks

    A DBI task specifies a single query, and what to do with the results. Since information often contains many-to-one relationships, which cannot easily be represented in a two-dimensional query result, it is normal to set up multiple tasks to transfer the information. For instance, if one wants to integrate companies and all the products they have purchased, it would make sense to create two queries (tasks) for this. One which integrates all companies ("select id, name, address from external_company"), and one which integrates all products ("select id, name, company_id from external_product"). The second task should be dependent on the first one (chosen as a particular type of schedule), so that they are executed in that order. Otherwise, one might get product-lines connected to a company not yet integrated.

    A task will have a list of parameters. When the task is executed, the list of parameters for the agent and the list of the parameters for the task will be concatenated, and sent to the agent executable. The parameters may also be altered by the task script. One of the most important parameters for the task is the query to execute towards the external source. Again, parameters are dependent on the type of agent executable.

    Tasks can be executed with different schedules, such as "once a week", "every night at 02:00", "every 5 minutes" or "after another task".

    A task also specifies a single script from the script library. This script will be executed on different events by the DBI engine. A parameter to the script specifies which event, and consequently the script must contain an IF-THEN-ELSE structure to perform various tasks for the various events in the task (this is an area for future improvement).

    Task script

    The task script will be executed several times when the task is executing. The script may access the dbiControl object using getDbiObject(), which again allows for access to DBI parameters. The parameter "dbi.event" specifies which event the script is currently executed for, and is one of the following:

  • "begin": Executed before the query is executed.

  • "recieveObject": Executed for each row returned by the query.

  • "end": Executed when the query is finished.

    Typically, the script may add parameters or flush tables in the begin-section. In the receiveObject-section, inserts are performed into eJournal’s database using various objects available in ejscript. The end-section is used to clean up, such as removing entries which seems to have been deleted in the external database.

    Agent executables


    ODBCAgent.exe

    This is one of the most commonly used agents. It will connect to an ODBC DSN and perform queries. It supports the following parameters:

    Parameters

    odbc.username

    ODBC username

    odbc.password

    ODBC password

    odbc.database

    ODBC DSN

    odbc.dbtype

    ODBC database type (mssql/mysql/oracle/unknown)

    odbc.init

    SQL query to init the db, so result will be returned

    odbc.query

    SQL query, the result will be returned as objects


     

    Objects

    column.size

    Number of columns

    column.X

    Result column data. X is 0,1,2,3,4... given by the number of columns


     

    LDAPAgent.exe

    This is also a commonly used agent, as it is used to access Active Directory users. It supports the following parameters:

    Parameters

    ldap.username

    Username

    ldap.password

    Password

    ldap.server

    Server name or address

    ldap.serverPort

    Server port (optional)

    ldap.disableEncoding

    Disable UTF-8 encoding (optional)

    ldap.searchFilter

    Search filter

    ldap.searchResult

    Search result comma separated list of attributes.

    ldap.searchLimit

    Limit the search

    ldap.searchTimeout

    Search timeout value in seconds

    ldap.searchBases.size

    Number of search bases

    ldap.searchBase.x

    Multiple search bases can be specified (x=0,1,2,3..)


     

    Objects

    column.size

    Number of columns

    column.x

    Result column data. X is 0,1,2,3,4... given by the number of columns