Case Studies

Retailer

• All Data No Information - White Paper 

Issue
$54 Billion Retailer Client owned and operated 2,500 stores across the country, divided into 1,500 price zones, with each store carrying approximately 50,000 UPCs, The enterprise pricing strategy was managed out of corporate HQ, utilizing 9 legacy main frame systems that hosted all UPC pricing for their 13 banners (chains). In order to manage and set prices, negotiate with vendors and manage circulars, a minimum of 75 contract workers were devoted to reading the legacy mainframes green screens and typing UPC prices into Excel templates for use by the merchandisers. This process was time intensive and costly, error prone and produced limited timely information. 

Solution 
› Set up a SQL Server database with live feeds from the 9 legacy systems and deploy a GUI front end for UPC data mining and exporting to Excel. 
› Designed and led the development of 8TB SQL Server 2008 UPC data mart consolidating 9 legacy mainframe systems through 85 staging tables and normalized data rules. 
› Designed database architecture and the datamart. This single data mart is capable of looking up and analyzing UPC price, cost, vendor, gross margin, inventory balances and POS activity at any of the 2,500 stores represented in the 9 legacy systems. 
› Designed and developed user friendly front end analytical and reporting Access application for the UPC Data Mart, deployed to over 600 users enterprise wide. 
› Autonomous client-side installation, enterprise wide 
› Auto-updating for new application version release 
› Auto-Error reporting and notification 
› Built in security, with proprietary Kill Switch technology, to prevent data theft. 
› Search, retrieve and report by UPC, Category, MIG, or Price Zone. 
› Administrator Application to manage Price Zones, user permissions, query limits, and user log tracking 

Benefits 
› On first version deployment (at 8 week mark) Access front end, Access back end with 80% of all UPCs, client was able to eliminate 75 contractor positions, reducing annual cash expenditures by $4.5mil. 
› On second version release (at 90 day mark), Access Front end, SQL Server 2008 back end with 100% of all UPCs from the 9 legacy systems, client was able to eliminate 1,500 IT positions, reducing annual expenditures by as much as $100mil 
› Pricing Strategy team was significantly more capable of setting strategies and managing them in response to local market conditions due to available data in the UPC Data Mart. 
› Merchandising team was much more capable of negotiating with vendors, armed with data from the UPC Data Mart. 
› The UPC DataMart Solution met the business needs to the extent that the Client was able to shutdown a failing Oracle POS implementation which was 2 years overdue and millions of dollars over budget. 

Return To Case Studies