The Client
The Client is an American retail chain specializing in counterculture-related clothing and accessories, as well as licensed music. The company provides music-inspired accessory destinations for both guys and girls by online and offline modes.
They offer band-related apparel and accessories, licensed merchandise, and fashion apparel and accessories, which can be found at more than 600 stores.
The Challenges
The day-to-day activities of a retail company typically involve distribution, purchase, cost, inventory, sales and stock planning management. Hence, the client managed huge data for its large number of items and high volume of inventory across the 600 retail stores. Managing those datum manually was the biggest challenge which resulted in shortcomings like:
- Absence of real-time database update.
- Manual interventions resulting in low-quality data.
- Slow report generation.
- Data redundancy and inconsistency.
- Difficulties in information sharing and customer services.
The retail IT landscape mainly comprises of different systems like stores, warehouses, remote workers, head office data, that needed to be internally and externally integrated together in a way so that they can communicate and interact smoothly. Thus, the different systems were required to be integrated, to make them interact with the existing Retail Merchandising System smoothly. In addition to that, the client was also seeking help in:
- Routing messages to the newly integrated systems.
- Monitoring using custom BizTalk health Monitor tool built on Business Activity Monitoring (BAM) databases.
- Converting data into desired destination format (JavaScript Object Notation).
The Objective
The client sought support for real-time visibility of operations and seamless integration of Retail Merchandising System with third-party applications:
- Seamless integration of excel and SQL Database to automate a manual process, improve the visibility of operations and enable the real-time database update.
- Integration of Retail Merchandising System with other diverse business applications using Biztalk server in order to gain better support in managing, interacting and monitoring the applications.
The Solution
JK Tech, as the client’s solutions partner played a pivotal role in technology evolution and helping the client with development, integration, implementation and support services. To manage the datum of 600 retail stores and automate the manual process – the Excel- ETL (Extract, transform and load) based tool named PD Tracker was developed which enabled the real-time database update and automated the existing manual process, successfully.
Before the PD Tracker tool the client was maintaining RMS data in excel sheet only, the data was fetched from Oracle to excel sheet (no ETL was in the picture) and then perform their day-to-day activity. There was no real-time update possible from the excel sheet to DB. The tool made it possible to update/Insert/Delete data from excel to DB in real-time and helped in standardized data integration with an easy and quick generation of reports.
The integration of different applications with the Retail Merchandising System was accomplished using BizTalk Server, which acted as a middle-ware and a communicator between the two business systems coupled with data modeling and data transformation. BizTalk played a vital role in enabling open communication between the applications. The integration covered three interfaces:
- User
- Vendor
- Purchase Order
In addition, Manhattan- MIF, which is one of the endpoint business systems was used to store information about the users, customers, vendors, various stores, warehouses, etc. It also contained details about all transactions of various purchase order details (Items, price, tax, freight, etc.) The destination system worked on JavaScript Object Notation (JSON) format, unlike the existing system which worked on Flat File, a custom component was developed for effective data conversion in the desired format.
Solution Diagram:
The Benefits
- PD Tracker tool reduced manual work by 90%, enabled real-time update from Excel to database and faster and accurate report generation.
- PD Tracker tool helped with standardized data integration and now it’s possible to update/Insert/Delete data from excel to DB in real-time.
- The integration of the Retail Merchandising System with other systems using BizTalk Server resulted in improved data integrity and smooth and seamless execution of processes such as purchasing, distribution, inventory, finance and warehouse functionality.
- The open communication across systems empowered the end-user with the necessary information to engage and manage the entire customer experience. Orders from different regions/locations and stores could be placed now irrespective of the channel chosen.
- The monitoring tool BizTalk Health Monitor (BHM) made it easy to access and see the status of messages sent and received between systems. Also, the client can now delete, add and resubmit the failed messages using the BHM tool.
