Company Profile

Discount Fashion Warehouse (DFWh) is a Columbus, Ohio based clothing retailer selling name brand overstock products at prices that are deeply discounted from standard retail pricing, buying directly from the manufacturer and selling through their four retail locations in the Central Ohio area. They also have an online presence as an eBay store, and routinely host weekend “pop-up store” events throughout Ohio to bring merchandise to customers outside of Central Ohio. Due to high daily transaction counts, DFWh relies on its technology assets to track sales dollars, determine stocking requirements, and predict types of seasonal products to purchase well in advance of their sale.

Business Situation

DFWh’s found it was outgrowing its point-of-sales system. While the system could report on sales and inventory numbers, it was inflexible in its ability to aggregate the data DFWh management needed to make timely decisions. This resulted in significant human labor to retype the data into spreadsheets, aggregate and set up proper formats and equations, and distribute these new files to appropriate staff on a weekly basis. The time required to perform these analyses reduced the window of opportunity to make quick buying and pricing decisions based on sales and inventory numbers. While it was clear that a new point-of-sales system was needed, this would be a major undertaking and a short term solution was needed to reduce the manual labor creating the weekly sales reports. In addition, changing point-of-sales systems would introduce its own problems, since sales data from the prior system would no longer be in a consistent, usable format with whatever system was chosen for its replacement.

Technical Situation

The DFWh retail outlets were networked, but the network was fragile and kept experiencing downtime. Additionally, to create security while keeping costs reasonable, a public VPN service was used to encrypt traffic between the stores, further stressing the wide area connection. The current point-of-sale system was not initially implemented as a multiple location installation, so DFWh found itself with four different database installations, one for each store, making aggregation of store-wide sales data difficult. It had attempted projects in the past to create a unified view of store-wide sales, but those projects had failed due to their complexity.

Solution

DFWh approached Halcyon initially to create a project to implement a new point-of-sales system. After meeting with DFWh management, we recommended that a smaller project first be taken on to create a centralized, web-based business intelligence (BI) portal that would aggregate store sales and inventory data and create the necessary reports for weekly sales analysis. This recommendation was based on our perception of the immediate needs of DFWh management.

Since DFWh was reticent about basing any more work on the current point-of-sales system, we proposed BI portal as a two-step project: first, we would do an analysis of the existing Microsoft SQL Server database tables and determine where the interesting data was being stored. This had to be done since there was no documentation regarding the internal data storage structure of the product and the tables weren’t created in the database using modern, self-documenting methodologies. If, in two weeks, we couldn’t determine where the data was and what rules surrounded their storage, we would cut the project short. As DFWh management had bad experiences in the past with this sort of analysis, they reluctantly agreed but expressed doubts as to whether any sense could be made of the structure.

By the end of the first week we had pared the 200-table database down to 12 useful tables and presented out findings to DFWh. We also provided a sample data extract that they were able to match up to their reports for validation. This provided DFWh the confidence that they needed to give Halcyon the green light to move forward on the project.

In order to keep the system centralized, redundant and secure, we provisioned a Microsoft Azure virtual machine with the VPN software needed to attach to the DFWh network. On a nightly basis, an automated process in the web server runs to extract data from each of the retail outlets and store them in a BI data model on an Azure database. These extracts can be triggered manually as well, giving DFWh an immediate aggregated view of store sales to the minute.

For user access, DFWh management logs into a web application that allows them to create reports in 10 different formats, either by a single store or combination of stores, and download as a spreadsheet, view online or output as a printable PDF file. The application traps and logs all data errors as well as ensuring that the scheduled processes run as configured through a command queueing mechanism.

Benefits

The system has saved significant staff time since implementation, as well as allowing for prior week sales data to be viewed as early as 10 minutes after the retail stores close. Estimated ROI for the system, without considering enhanced sales due to better inventory management, is 6 months.
With the data now easily available, DFWh management has had time to think of other ways to enhance their use of the system. As of this writing, a project is being planned to cross-categorize the retail inventory, allowing for orthogonal views of sales that were not possible before due to the time required to add the new categories.