- 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
|