The Business-to-Business data exchange solution was a proof of concept to demonstrate the capability of MS SQL Server to replicate data over the Internet.
Table of Contents
Background
Microsoft, India approached its certified solution partner Kumaran Systems to demonstrate the business benefits of a B2B data exchange solution for one of its major clients based in Calcutta. The business need of the client, in brief, was to enable it to exchange data, available at its central database, with its 900 wholesale dealers.
Thus, Kumaran was entrusted with the task of providing a B2B data exchange solution, which would be integrated with the Wholesale Dealers Information System that was being developed by the Client’s own IT Division.
Business Situation
The Client had automated the operational activities of their enterprise by means of SAP’s ERP package, which has Oracle as its backend database. They sell the different categories of their products, essentially by means of their 900 wholesale dealers spread throughout the country.
The Client and their Wholesale dealers exchange a lot of data between them (like purchase orders, shipping details, confirmation receipts, product catalogs etc) to address their business requirements effectively.
Initially, most part of the transaction of the data between the dealers and the Client was done manually. That is, the wholesale dealers would enter the data in a predefined format in an excel sheet and then, would send it across to Client, where the collected data is inputted to the central database. Also, data from the Client was transferred to the dealers, in the same manner.
This had led to the situation, where the dealers were working on copies of data, which were not always synchronized with the Client’s database. With so many Wholesale dealers depending on the Client’s centralized database, performance and reliability were suffering.
Hence, the Client needed a B2B data exchange solution with a database platform that would provide better performance and yet, enable them to continue building on the existing technology investments.
Solution
Our Client had recognized the importance of designing an effective data exchange solution for their Wholesale Dealer Information System, in an attempt to integrate their value chain. However, the system infrastructure available with the Wholesale dealers varied from one dealer to another.
While 60-70 percent of the dealers had adequate facility to replicate and exchange the data over the Internet, the remaining dealers had not-so- sophisticated computer facility.
Therefore, it was decided to give provision for data exchange through an Internet connection and also by means of XML in a disconnected environment.
Data Exchange in a connected environment A Visual Basic application, with SQL Server Desktop edition has been developed at the client end for the Wholesale dealers.
Whenever the Wholesale dealer needed to exchange data with the Central Server, the Application would connect to the Central Server that is situated in the Client’s premises.
The centralized server would run SQL Server Enterprise edition on Windows operating system.
A Firewall is provided which ensures data security by using the Internet Security and Acceleration Server that restricts access only to predefined users.
The Wholesale dealers would log in to the Web Server, which would replicate and transfer the data from the SQL Server that is behind the firewall.
The data that is usually transferred from the Wholesale dealers to the Central Server at Client’s premises are the Stock Position Report, Order details, Sales Estimations etc and that, which is transferred from the Central Server to the Wholesale dealers include the master data, shipment details, revised sales estimation etc.
However, for a few transactions, provision was given for the Wholesale dealers to enter data into their local database, before synchronizing with the central server at Client’s premises. The size of the data that would be transferred could grow later depending on the expansion of the client’s operations.
The Visual Basic client application would connect to the central web server, and after it was being authenticated by means of a unique Wholesale dealer ID and password would start exchanging the data.
The Wholesale dealer’s data would be consolidated and then would be transferred to the central database and vice versa. The complete details of the data exchange would be logged into a log file.
Data Exchange in a Disconnected Environment
When no dial-up connection exists, the data exchange has to be done in a disconnected environment. For Wholesale dealers who do not have Internet connectivity and in situations where the Internet connection gets lost temporarily, provision was given to take an extract of the data in the form of XML, in external storage.
The XML data that is saved in external data storage was transferred to the central database, from a browsing centre. Also, it was ensured that the data that was, transferred by means of an external memory was not sent again from the local database of the Wholesale dealer.
In the same manner, data pertaining to the logged in Wholesale dealer, was transferred from the central server, in the form of XML files, to the external data storage and then to the local database of the Wholesale dealer.
Benefits
As a result of designing a B2B data exchange solution for the Wholesale Dealer Information System, with SQL Server as the database platform, the client has increased their performance and reliability, and thus could integrate their value chain in an effective manner.
The data exchange solution provided has enabled the Wholesale dealers also to submit their purchase orders electronically and maintain their stock efficiently.
Moreover, the data exchange solution has enabled the client to ship the stock needed by the Wholesale dealers on time. Also, the Wholesale dealers now get their revised sales estimations in a much quicker time.
Further, the dealers and the Client, are able to exchange data among them with ease and work with the synchronized copies of data, at all times, which has enabled them to avoid any inconsistencies. The data exchange solution has also greatly reduced the manual work involved in data transfer, thus reducing maintenance costs. Finally, Wholesale dealers enjoy the performance and availability advantages associated with having a robust local database.
