Duane Reade sells more products per square foot than any other drugstore chain, but its manual reporting processes hurt efficiency. In 2010, the company engaged Pivotstream to host an innovative reporting solution based on the Microsoft platform. With it, Duane Reade has increased efficiency and accuracy, enhanced collaboration with vendors, improved agility, and achieved unprecedented insight into nearly 1 terabyte of data.Business Needs
Although it is the largest drugstore chain in New York City, Duane Reade must continually focus on optimizing the return on investment for every inch of its expensive retail space. To do this, the company must understand the latest consumer-demand trends to make effective merchandising and promotion decisions. This requires daily in-depth analysis of product sales, inventory, loyalty dynamics, and merchandise layout at 253 stores across New York State. Previously, employees at Duane Reade used Retail IDEAS from RPE for reporting. It created standard reports that contained raw data from a mainframe. Each employee formatted this information using Microsoft Office Excel 2003 spreadsheet software, and then printed reports to share.
||With our Microsoft-based reporting solution, we can drill down into data to understand product performance and trends across the state, at specific stores, and by vendor.
Director of Visual Merchandising and Analytics
Reports often lacked the detail that employees needed, especially about product performance at individual stores. The lack of insight impeded decision making and hindered collaboration, especially with vendors. To gain crucial business intelligence (BI), employees often worked with IT analysts to collect additional data. However, it frequently took days to collect this information because Retail IDEAS was difficult to use, and it suffered occasional outages.
Manually compiling and formatting multiple sources of information created inaccuracies, hindered efficiency, and created conflicting perspectives about data. To overcome its challenges, Duane Reade sought a solution that could streamline reporting and provide more in-depth BI.Solution
After meeting with third-party vendor Pivotstream in January 2010, Duane Reade engaged the company to host an innovative reporting solution based on the Microsoft platform. Known as DR Direct, the Pivotstream-hosted solution—which includes Microsoft SQL Server PowerPivot for Microsoft SharePoint and SQL Server PowerPivot for Microsoft Excel—offers fast performance, an intuitive web interface, and centralized access to reports. “After seeing what the Microsoft-based reporting solution from Pivotstream could do for us, we were thrilled with it,” says Scott McCulloch, Director of Visual Merchandising and Analytics at Duane Reade.
In just eight weeks, Pivotstream deployed the solution. It includes one data mart that engineers created with Microsoft SQL Server 2008 R2 Standard data management software, Microsoft SQL Server 2008 R2 Management Studio, and the Windows Server 2008 R2 Standard operating system. It uses Microsoft SQL Server 2008 R2 Integration Services to collect real-time data from three disparate corporate systems. Today, the data warehouse contains 1 billion records and nearly 1 terabyte of mission-critical data.
Four more server computers, which run Windows Server 2008 R2 Enterprise, support the Duane Reade portal built with Microsoft SharePoint Server 2010 and the Microsoft .NET Framework 4. Engineers worked closely with the Duane Reade management team to create 130 standardized reports using Microsoft Excel 2010, PowerPivot for Excel, PowerPivot for SharePoint, and SQL Server 2008 R2 Enterprise. Users access the reports on the Pivotstream-hosted analytics platform. Each one contains tabs that segregate data based on consumer and store segmentation attributes. Within each tab, users can view data from up to 20 business perspectives including sales, margins, vendor, or locations.
In September 2010, the 25 employees who depend on reports began to use the new solution. Several dozen leading vendors also subscribed to DR Direct to access and analyze data about specific product lines.Benefits
By taking advantage of a hosted solution based on the Microsoft platform, Duane Reade has achieved unprecedented insight, increased efficiency and accuracy, and facilitated agility and collaboration.Provides Unprecedented Data Insight
With the innovative DR Direct solution, Duane Reade has made it possible for employees and vendors to access valuable metrics on a scale not seen before in the retail industry. Reports include 2.5 years of transactional data, and users can analyze hundreds of business scenarios. “The depth and quality of information have certainly improved,” says Beth Stiller, Senior Director of Merchandising and Category Management at Duane Reade. McCulloch adds, “With our Microsoft-based reporting solution, we can drill down into data to understand product performance and trends across our business, at specific stores, and by vendor.” Improves Efficiency and Accuracy
Highly available and easy to use, the solution gives employees instant access to reports around the clock from a central portal. “We tend to be action and information junkies, so it’s refreshing to be able to ask questions and get the answers right away with our hosted Microsoft-based reporting solution,” says Stiller. In addition, the integrity of data is no longer in question. McCulloch says, “Reports provide graphical representations of information, but the raw data is there if you want to see it or modify how it appears.” Improves Agility and Collaboration
Because reports use the same set of data and are formatted in predictable templates, the company has enhanced collaboration and strengthened relationships with vendors. “What we know, you know,” is a new corporate mantra at Duane Reade. McCulloch explains, “We can immediately initiate meaningful conversations because people can agree on one version of the truth. By using our hosted Microsoft-based reporting solution, we can make better decisions faster—and it takes us less time to see the results of decisions in our financial performance.”
Offloading IT requirements only further boosts agility because Duane Reade has one less IT challenge: Pivotstream manages all aspects of reporting and can make most changes in just hours. This includes modifying reports, adding users, and scaling the solution to store data about 7 million new customer transactions each month (excluding pharmaceutical transactions). In addition, the solution may soon grow to incorporate significantly more users and data. McCulloch says, “We’re looking at how employees at our parent company Walgreens can benefit by using this solution.”This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.