BellTran™ Overview

Introduction

BellTran™ is an interface program that automatically moves data between dissimilar databases. It can detect changes in specified tables/fields in one database and automatically transfer the corresponding changes to another database.

 

.

A BellTran transfer can be initiated through the BellTran user interface, which can request a one time transfer or can request periodic transfers of the latest updates between databases. The user interface can also be used to monitor transfers and to view transfer and error logs. Transfers can also be requested by external programs writing into a command table in the BellTran database.

The BellTran runtime server can directly transfer data between databases. Alternately a program can present data for transfer by placing it in the BellTran import table and then writing a transfer request to the command table. Similarly a program can request data be transferred from a database and placed in BellTran's export table, where it can be picked up by the requesting program.

The key feature of BellTran is that you enter a definition of the structure of the two databases and the rules that relate the data elements, then you press the Generate button and it automatically generates a program that knows how to transfer data between the two databases. The resultant program detects changes in one database and automatically moves the data to the other database.

Data Transfers

BellTran solves many of the complex problems in implementing an interface to move data between databases. These include:

1.   Data dependency issues: An employee record may depend on a department record, which may depend on a facility record. The interface program has to check whether the facility is defined in the target database and move the data record from the source to the target database if not, then it has to check and potentially move the department and only then can it move/update the employee record. This is what we call chaining.

2.   Handling of indirections: When the data is put–away in the target database table, the department field in an employee record, for example, may not be the department number but an indirect reference to an entry in the department table.

3.   Handling of defaults. Data records coming from the source database or program may be incomplete and may have to have defaults for certain fields supplied by the interface program.

4.   Handling of different formats for dates and numbers, as well as removal or adding of blank space padding on fields. Also removal or "escaping" of special characters that could interfere with SQL operations.

5.   Handling of multiple key lookups, whereby records in the source database are not uniquely referenced by a single key. Also translation from multiple key tables to single key tables.

6.   Handling of incomplete updates. A previous record may have been transferred and then edited on the target program/database. For example, an employee record may have been exported from an HR system but their ability to approve shipments may have been entered in the target system. If the record is updated, for example to change the employees department, we want to allow this but could want to retain their approval status. So we have to do a field by field update.

7.   Efficiency of transfers. It is very inefficient and often very undesirable to transfer all the records of an object type. When we transfer an employee record, we only want to transfer the corresponding department record if the department entry on the source database is newer than that on the target database or is undefined in the target database. We do not want to transfer all departments or records for all employees that have ever worked for the corporation. We only want to transfer relevant data for active employees. This is why chaining is so important.

8.   Multiplicity of data sources. Data for the target database record may come from multiple sources. For example, a new employee record may come from an HR database but their user name and password, to be stored in the target system, may come from an LDAP database.

Writing an interface program by hand to transfer data is a non–trivial process that can take weeks or even months to develop. To overcome this problem, BellTran uses a mixture of code generation, compiler and expert systems technologies to minimize the development time. With BellTran, developing the interface server program consists of the following steps:

1.   Define all the appropriate tables and fields in the source database and the indirect relationships of their fields.

2.   Define all the appropriate tables and fields in the target database and the indirect relationships of their fields.

3.   Select all those fields in records that have a 1:1 relationship. Many fields such as first name and last name and address for an employee may have a 1:1 relationship that can be selected using a point and click interface.

4.   Specify the rules that relate more complex fields. For example, the name field in the target may consist of the concatenation of the first name and last name.

5.   Specify the rules for data transfer objects (DTOs) which are named entities invoked to do the actual transfers. For something like transferring employee records, this may be as simple as specifying the primary source of new and updated employee records and the target database table into which to put them. In the case of sales order records we may need to transfer new and updated sales orders as well as new and updated line items.

6.   Specify the ODBC interfaces to the databases (data source name, user name and password)

7.   Press the Generate button and BellTran will compile all of this data into an executable BellTran runtime program that knows how to automatically transfer data between databases.

Setting up all the table and field definitions and determining the rules that relate them is, in itself, a non–trivial process that requires a good understanding of the underlying databases. This is required whether BellTran is used or an interface is hand coded. From this point on, BellTran generates the code automatically, which avoids the need to hand generate the code for the interface program. BellTran provides all the data transfer mechanisms pre–developed and pre–debugged, including automated generation of all the SQL code needed to get data from one database and put it in another.

While BellTran can move data between databases, it is recognized that there are occasions when it is not appropriate for BellTran to reach into a source database or to write directly into a destination database. BellTran has an import table into which a program can write data for transfer to a database. In this case, the import definitions for BellTran are setup to relate to the data in this table rather than to an external database. Similarly data can be transferred by BellTran to an export table, where it can be picked up by an external program. Transfers using these tables will require code changes to the external programs that interface with BellTran.

The data in the import and export tables is in a keyword–in–context (KWIC) format with a keyword specifying the type of data record being transferred followed by the parameters for that data record.

BellTran can also be configured to call stored procedures or to link with C++ application programming interfaces to get data or put data away. For databases with application programming interfaces, this may be preferable to having BellTran directly access the databases or to require programming to use the import and export tables.

BellTran Architecture

BellTran consists of:

1.   A user interface. For developers, this provides the ability to setup the definitions for the database tables and fields and the rules that relate them. For users of the interface, it provides the ability to initiate one–time or periodic transfers of data. It allows users to specify whether to transfers all the data, just the latest updates or data within a specified time range. It also allows users to specify which chains of data objects are to be transferred and to view logs of data objects transferred and logs of any errors that occurred.

2.   A BellTran database in which all the data that is needed by BellTran is kept. This database contains a command table into which external systems can insert requests for data transfers. It contains an import table into which external systems can place data for transfer to an external system. It also contains a table from which external systems can pick–up data extracted from a legacy system.

3.   A BellTran runtime server process. This is the executable process that performs the actual transfer. This is based on the use of the Activation Framework® real–time rules–based expert systems that was created by BellHawk Systems in collaboration with the US Air Force.  Activation Framework uses a collaborating intelligent–agent paradigm and compiles the user defined rules into executable code. This enables user defined rules relating the databases to be rapidly executed. It also enables complex algorithms, written in C++ to be integrated into the data transfer process, where needed.

4.   A Generation program that automates the generation of the BellTran Server process based on data stored in the BellTran database. This code generates SQL code to retrieve data from SQL databases and also to put data into SQL databases using ODBC connections. It also generates the code to retrieve data from non–SQL sources and to send data to non–SQL destinations. The Generation program generates the needed rule–code objects from the data definitions and user rules and also automatically generates the C++ wrapper code for the SQL calls. The BellTran Runtime server is created by pressing the Generate button on the developer version of the user front–end and results in the creation of a fully self contained executable program.

5.   The Launcher process. This lightweight process, which can be run as an operating system service, monitors the command table in the BellTran database to see whether any new commands have been placed there by the User Interface program or an external application. If it sees a new request then it launches a BellTran runtime server process to perform the transfer. BellTran is constructed in such a way that there can be multiple BellTran runtime processes active at the same time performing transfers between different legacy databases. The Launcher is responsible for the periodic launching of BellTran processes to carry out transfers on a periodic basis and for making sure that the last transfer was completed before the next one is initiated.

BellTran Status

BellTran currently runs on a Microsoft Windows Server computer and uses a SQL Server database. Except for the user interface, which was written in Access for expediency, BellTran, like its Activation Framework base, was written to be cross–platform and database agnostic. It is planned to release a Unix version in the near future that will be compatible with LAMP (Linux, Apache, MySQL, PHP) Webservers.

While BellTran is a new product, Activation Framework and the SQL code generation, which form the bulk of the BellTran product, are mature products that have been in use for many years. We plan to also add the import and export of comma delimited files from the BellHawk DEX interface to BellTran in the near future. This will enable user–friendly transfers of data to and from Excel spreadsheets. It will also enable data transfers with older legacy applications that do not support relational databases.  

We are taking orders for systems interfaces that require XML and UDP packet based transfers. We are also currently implementing a number of systems that integrate BellTran with BellHawk Systems BSAF™ store and forward technology to provide interfaces to wireless mobile devices for data collection applications.

For a PDF version of this document, please click here.