How to achieve true System Integration without front-end modifications



Author: George Kounis



There is universal agreement that organisations benefit by having a Single Contact View or SCV (a combined view of the activities of a contact with an organisation, amalgamating financial data, for example, from the Accounts System with sales data in CRM).  There is also universal agreement among the experts that a fully integrated, active system is ‘nirvana’, something that we aspire to but does not quite happen.


With the recent launch of SIS from Single Click Solutions, this is no longer the case.





What are the options?


If you need a SCV, what are the options available? 


Short of leaving users to their own cut and paste devices, the options fall into the following categories:


1)      A data warehouse or several data marts. These can be snapshots in time or active, that is, build from live data in real-time.

2)      A stand-alone enterprise-wide application which would ensure that all the data of an organization is in one place.

3)      Database linking.


The table below tabulates these options against measures of Practicality, Integration and Sustainability:





1) Warehouse/Mart

Practical solution

No true integration

2) Stand-alone system

True Integration

Costly, unachievable or unsustainable

3) Database Linking

May be Practical

May achieve true Integration

Drawbacks vary depending on method used


The Measures Used




With this value we are measuring the practicality of implementing the solution (cost, business interruption, new learning curves, realisation, readiness, usability etc.)




This measure looks at the degree of integration. How do you avoid duplication when adding a new contact?  If you change an address, a status e.g. ‘deceased’, a mailing preference e.g. ‘No Contact’, are they changed throughout the organization?  Is the data clean and displayed in a uniform format?




Few organizations can escape the fact that they thrive on information and therefore need to adapt to changing data capture and data spread requirements. Will the solution cater for these changing requirements over time so that the solution can move and grow with the organisation?


On the basis of these measures, the Data Warehouse/Mart solution fails on integration and the stand-alone solution on practicality and sustainability.  Database Linking may provide the best of both worlds but only if the right solution is used.



Database Linking Solutions


Database linking solutions involve replicating or migrating the contact record in a central repository and cross-referencing it to the Unique Record Numbers of that record in each of the databases being linked. Using this cross-referencing as a switchboard, any changes to a record can be filtered through to every database in which it resides. Using the same cross-referencing, transactional and other information relating to that record can be retrieved, amalgamated and displayed in a Single Contact View.


Database linking solutions fall into two categories:

(1)    Those that require front-end modifications so that:


a.       inserting a new contact record in any of these applications forces the user to search a central repository for potential matches before adding to avoid duplication.

b.      Updating a contact record updates it in all linked databases where it resides; and

c.       Provides a facility that allows users to open the SCV of the record they are viewing.


The disadvantage of this approach is the cost of front-end modifications.


(2)    Those that require no front-end modifications and rely on background operations to search for duplicates after they are added or for updating or deleting records.  The SCV they provide is independent to the particular record being viewed in a linked application.


The disadvantages of this approach are:


a.       Whereas duplicate search by users ensures a very small mesh for duplicates to go through (users can pick up a record that is the same even if the details are substantially different) auto-matching uses a very large mess inevitably resulting in duplicates being created.

b.      Due to the system’s propensity to create duplicate records, the quality of the data in the SCV is not reliable.

c.       The SCV cannot be viewed from within a linked application , thus making it cumbersome when dealing with contacts on the telephone, for example.

d.      The cost of such a system due to the technology involved is high and usually overtakes the cost of modifying the linked applications.


SIS has created a third category of database linking, one that combines all the advantages of the first with those of the second and thus overcoming all the disadvantages of a linking solution and all the disadvantages of a Data Warehouse/Mart solution or a stand-alone solution.  Furthermore, both the cost and implementation of SIS is negligible compared to the other solutions.



What is SIS?


SIS stands for System Integration Server. It consists of the  following components:


SQL Server databases:


1)      CERES which acts as a Central Repository in which all the contact records are replicated and cross referenced to the URNs in the linked applications

2)      CDW which acts as the Central Data Warehouse.

3)      DexPro (optional) which holds information to support the data extraction and processing wizards in the DexPro application (see below).




4)      CERES Search Pop-Up that retrieves potential matches from CERES and displays them to the user when the user inserts a record in a linked application. The Search gives the option to link the inserted record to an existing record in CERES or add it in CERES as new. 

5)      SCV which can be opened from any linked application to display the activity of the contact with the organization.

6)      SIS front-end which allows viewing, adding, linking and updating contact records in SIS, adding associated records, creating relationships between them, recording communications and actions.

7)      DexPro front-end (optional) which allows users to do drag and drop data extraction using segmentation codes or to drill down to data in any of the linked applications and to export it, mail merge it, create reports or use it for eMail broadcasting and for scheduling or triggering reports.




8)      The Replicator which is a SQL Server transaction log reader constantly monitoring the linked databases that can be configured to take certain actions in response to certain events.   Apart from dealing with events such as updates, the Replicator may also be used like a trigger for business rule implementation without the overhead of triggers.


How SIS works




With SIS the database linking process begins by de-duplicating, replicating and cross-referencing the contact records from all the databases to be linked into a Central Repository (CERES)


The Data De-Duplicator also available from Single Click Solutions can provide an easy way to de-duplicate data and is a useful periodic data cleansing tool.



Adding contact records without creating duplicates


A record can be added in any of the linked applications.




When the record is inserted a Search Results screen from CERES pops up on the user’s screen showing possible matches.





The user is then invited to select a match or add the record inserted as a new record in CERES



The Search Pop-Up involves no modifications to any of the linked applications. SIS detects the inserted record and traces the desktop from where entered, all in the background and all automatically.


This is a unique feature of SIS. It gives the user control to avoid duplicating a record. No other auto-linking method does this.



Updating and Deleting Contact records


At the heart of SIS is the Replicator, a transaction log reader that is fully configurable. It can be used to check and replicate any record, not just contact records. This can provide an organisation with whatever degree of integration it requires as well as business rule enforcement.


The SIS Replicator constantly reads the transaction log of each of the linked applications and applies any changes made in one to all other linked databases where the record in question is held.






Updating records is seamless. The user updates a record in any application and the changes are applied to any other database where the record is held.







Deleting records is also possible in this way, although an organisation may wish to limit deletion to the CERES link only or apply the deletion across all databases. Through the configuration facility, an organisation may determine precisely what happens when each event occurs.

The Setting Up the Central Data Warehouse


The activity of a contact with the organisation is brought together into a Central Data Warehouse (CDW). Depending on organisational requirements and data volumes, this may be in real-time or by running overnight procedures.




The CDW is used for running reports and general extractions (see DexPro below) and to populate the SCV.




A user can open the Single Contact View (SCV) by either clicking the SCV button on the desktop and entering the contact details or by selecting the record in any of the linked applications and then clicking the SCV button




In the latter case, SIS traces which record the user is viewing and opens the SCV for that record. The availability of the SCV button on user desktops involves no modifications to any of the linked applications. SIS traces automatically the record selected whichever application is being used.


This is another unique feature of SIS.  Users can use existing applications to capture and process data and have the combined view of activity from all applications at a click of a button.


The SCV window is divided into tabs each tab representing an activity of the organisation. The tabs, as well as their content, are fully configurable.




Only the tabs that have content are enabled. 




The SCV provided the option to display data either in a data grid or in a treeview.  Both allow users to drill-down to the next layer of detail.





SIS front-end


In addition to providing a way to link applications together, SIS also provides a way to access the Central Repository directly, to search for a contact, add, link or update, add associate records, create relationships between contacts, log communications and assign actions.  This functionality has been added in the absence of or in substitution of such functionality in linked applications and more such functionality will be added in future versions.


General Search


Search Form



Here the user can specify search criteria in any combination and also use wildcards.  If there are no matching records the user is advised, otherwise the Search_Results form opens and displays possible matching records




The Search Results Form


Apart from the ‘Close’ form command button, the Search Results form has the following commands:


§         The ADD menu that opens the New Contact form

§         The Contact Profile button that opens the Contact Profile; and

§         The Contact Record button that displays the Contact Record.


Adding a New Contact




The Contact menu command opens a blank contact form enabling the user to enter the new contact details.  The user can also create an associate contact and has the facility to copy across to the ‘Associate’ tab the main contact’s details and create a relationship between them.



The Single Contact View


The ‘Contact Profile’ button is enabled by selecting the AR_URN on the Search Results form.





The ‘Contact Profile’ button opens the SCV illustrated earlier.


Contact Record Form


The ‘Contact’ button is enabled by selecting any part of the record details (e.g. Main Name or Address) on the Search Results form.




The Contact Record form displays the Contact record and allows the user to edit details and update the record.




The Contact Record form also gives access to the following commands from the ADD Menu:



Create Associate


When opened from the Contact Record form, the Create Associate form displays the Contact record and allows the user to copy details across to the ‘Associate’ tab and create a relationship between them.





Create Action


Every action needs an internal or external communication associated with it.  If there are no communications recorded against the Contact record,  the Comms form opens (see section below) for a communication to be created before an action can be recorded against it.





After the first communication with the contact is entered or if there are already communications with the contact recorded, the Action form opens allowing users to record actions against any communication recorded against the Contact record.





Enter Communication


When the Communications form is opened, the user can manually enter a communication against the Contact record.





Apart from providing the facility to do a general search, the Search form also allows access to the facility to create an association between two records.


Creating an Association



By following the Relationship = > Create Association commands the user can enter two AR_URN’s and create a relationship between them.


























DexPro is an optional component to facilitate data extraction from multiple sources and mail merge, report generation, eMail broadcasting or data export in various formats.


A separate White Paper on DexPro is available