USA Based Retail Company Doubles its Direct Commerce Business Across 600 Stores

Seamless integration of Excel and SQL Database as well as integration of Retail Merchandizing System with other diverse business applications to automate the manual processes, improve the visibility of operations and efficiently managing the retail IT landscape.

USA Based Retail Company Doubles its Direct Commerce Business Across 600 Stores - Case Study

Client
Client is an American retail chain specializing in counterculture related clothing and accessories
Industry
Retail

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 both 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 OBJECTIVE

The client sought support for real-time visibility of operations and seamless integration of Retail Merchandizing System with third party applications

  • Seamless integration of excel and SQL Database to automate manual process, improve the visibility of operations and enable the real time database update.
  • Integration of Retail Merchandizing System with other diverse business applications using Biztalk sever in order to gain better support in managing, interacting and monitoring the applications.

THE CHALLENGE

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 SOLUTION

JK Technosoft, 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 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 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 easy and quick generation of reports.

The integration of different applications with the Retail Merchandizing System was accomplished using BizTalk Server, which acted as a middle-ware and a communicator between the two business systems coupled with data modelling and data transformation. BizTalk played a vital role in enabling open communication in between the applications. The integration covered three interfaces:

  • User
  • Vendor
  • Purchase Order

In addition, Manhattan- MIF, which is one of the end point business system was used to store information about the users, customers, vendors, various stores, warehouses etc. It also contained details about all transaction 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.

USA Based Retail Company Doubles its Direct Commerce Business Across 600 Stores

Figure 1: Solution Diagram (Integration of new systems with the Retail Merchandising System)

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 Retail Merchandizing 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, client can now delete, add and resubmit the failed messages using BHM tool.