Bell-Connector Interface

The Bell–Connector software was developed to solve the problem of interfacing a wide variety of different accounting, ERP and CRM systems, as well as custom databases, to the BellHawk tracking software. The basic problem with developing these interfaces was not the complexity but the number of lines of code and SQL statements that need to be developed and especially tested for such an interface.

A BellHawk System can import and export over 50 different types of status data object. It also can export 12 different types of transactions. Importing and exporting these data objects is made easier by the BellHawk DEX data exchange API (applications programming interface). This ODBC interface enables users to interface with a structured abstraction of the BellHawk data objects. But writing and debugging all the SQL statements is still a lot of work due to the number of data objects.

DEX works by exchanging data in a keyword–in–context (KWIC) format where each data object has a keyword followed by parameters in a specific order. An external program wishing to transfer data into BellHawk writes its data into an import table in KWIC format or generates a comma delimited file in Excel .CSV format and then calls a stored procedure to import the data. A similar process is used by BellHawk to place KWIC format records into an export table where they can be picked up by an external application or exported as comma delimited files.

The Bell–Connector extends this principle by essentially using two DEX descriptions back to back, one for a source database and one for a target database. This enables the Bell–Connector to automate the transfer of data from one database table to another and avoid writing a lot of ODBC/SQL statements to read and write data through the DEX API.

A data transfer relationship is setup by entering a description of the source and target databases, the source and target tables and the relationships between the fields. These descriptions are entered into the Bell–Connector database. The data transfer relationships also include such information as to which field to monitor in a table to determine whether a record has changed. This enables the Connector to only transfer records that have changed since the last time each specific type of data object was transferred.

The Bell–Connector interface includes information as to which data is referenced directly and which data is referenced indirectly, though a look–up into other tables in the source or target database. This enables the Connector to automatically lookup references in the source database when needed. It also enables the connector to lookup references in the target database. This saves writing a lot of logic and SQL statements to handle all the indirect lookups.

The Bell–Connector can also automatically handle a wide variety of data transformations, including substituting default values when needed, as well as performing leading and trailing character trimming on source fields and padding on destination fields.

We refer to the set of definitions to transfer a single type of object, such as an employee record, as a Data Transfer Object (DTO). An interface to BellHawk many can consist of many DTOs. The running of these DTOs can be scheduled on a periodic basis through the Bell–Connector user interface. The times of day that they run can also be set through the user interface. Typically changes are relayed every few minutes from one system to another but it may be preferable to only transfer certain data in off hours to avoid loading down the accounting or ERP system with frequent update requests.

The BellHawk database contains data that is dependent on other data. A vendor purchase order (PO) data object will reference a vendor data object. For this reason, it is important that updates to the vendor records take place before the updates to the purchase order records. As part of the DTO setup, a chaining order can be specified. Thus the vendor PO DTO may also cause the vendor PO DTO to run as well as the PO line DTO. Once setup, the user scheduling these transfers through the user interface does not have to be concerned with chaining, as it takes place automatically.

The set of interface definitions for a specific database is refereed to as a Bell–Adaptor. The Bell–Connector comes as standard with an Adaptor for BellHawk. Standard adaptors for certain accounting and ERP systems are also being developed.

An important factor to recognize when planning interfacing projects is that, even when standard Adaptor definitions exist, they will inevitably need customizing for the specific version of the accounting, ERP or CRM system in use, the customizations made to that system, and the way the client is using their front–office systems.

These customizations can mostly be accommodated by changing the definitions in the Bell–Connector database. But, where needed, VB.Net code can be added to the Bell–Connector to handle special data transformations.

Currently the Bell–Connector supports ODBC/SQL interfaces. We can also support SOAP/XML interfaces by special request.

While the Bell–Connector was originally designed to support the rapid and cost–effective implementation of interfaces to BellHawk, it was developed to be a general data transfer interface between databases and other ODBC data sources. As such, BellHawk Systems also sells the Bell–Connector as a rapid interface development toolset and provides consulting services to clients in its use.

The Bell–Connector is written in VB.Net and ADO.Net and uses a SQL Server 2005 database. Unlike DEX, it dynamically creates its SQL Statements based on the data in the Connector database and the data being transferred. It takes advantage of the in–memory execution capabilities of SQL Server 2005. This enables changes to the data transfer definitions to be effected immediately without needing to go through a separate code generation step.

At present, the Bell–Connector interface software is made available at no cost to BellHawk Systems’ clients provided that our clients pay for all the professional services needed in the application of the Bell–Connector to specific interfacing situations. In exchange, BellHawk Systems retains the intellectual property rights to all improvements made to the Bell–Connector as part of these projects.

For more information, please contact BellHawk Systems at 508–865–8070 and press “1” for Client Services or click on the link below.