Data parsing custom application "cdWorksKParser" review and cost analysis


This application is a perfect example why sometimes it is a good idea to employ a custom programming regardless of the initial cost of the design and implementation.

Problem assessment.
Our client turned to us for help with extracting inventory data from a text data file. The file contained lines of inventory data for the products in the client's catalog. Each line consisted of a product ID and multiple columns with inventory info. Sound simple, right? Well the problem was that each line could be about 100 columns long and the list of items was about 40,000 long. Our client needed only the item ID and the inventory info total for each of the items. Manual data extraction was not even an option. In the past the client hired outside data consultant to extract the data and create a report for inventory level updates.

Data sample.
.....................
DAAGAW401028    EA0000015Y0000000N0000000N0000000N0000009Y0000010Y0000000N0000000N0000015Y0000000N0000001Y0000001Y0000000N0.....................
DAAGAW402028    EA0000000N0000000N0000000N0000000N0000002Y0000002Y0000000N0000000N0000004Y0000000N0000015Y0000014Y0000000N0.....................
DAAGDMD13532    EA0000000N0000000N0000000N0000001Y0000000N0000260Y0000000N0000019Y0000150Y0000000N0000001Y0000027Y0000000N0.....................
DAAGDMD14532    EA0000022Y0000000N0000000N0000018Y0000047Y0000304Y0000000N0000012Y0000111Y0000002Y0000014Y0000006Y0000000N0.....................
.....................
x ~40K lines


Solution.
Build a custom application that would load and parse the source data and enable users to save  the resulting info as comma separated values (.csv) file. The output data set consists of two columns of data per item. Average time required to process ~30MB data file - about 20 minutes.



Price tag.
The time required to complete this project was 8 hours of custom programming and testing. At $150.00 per hour the complete project cost: $1200.00

Return on investment.
With the cost of outside consultant at about $300.00 per each data extraction our client limited the inventory reports to a weekly base. The monthly cost of running inventory reports on average between $1000.00 and $1500.00. The custom application investment paid for itself within the first month and the client changed to semi-daily report schedule.

Conclusions.
In this case the decision to chose a custom application was very easy to make and the savings could be easily calculated because of the outside subcontractor being used for data processing. We also found out from the client that the ability to update the inventory information more frequently resulted in fewer order cancellations due to item availability (when the item is reported "out-of-stock" the client's catalog is updated to reflect the status). From our experience we know that whenever there is a data processing step involving manual data manipulation, replacing that step with an automated process (custom or off-the-shelf) results in direct and indirect benefits. Time reduction, error eradication and workflow streamlining.

No comments:

Post a Comment