Customer Solution Case Study
/ Retailer Expects $10 Million Profit Boost, Gets Better Sales Visibility with BI Solution
“Our director of merchandising for truck supplies and electronics estimates that he will improve gross profit next year by $10 million in his category alone, just by using the new SQL Server 2012–based planograms.”
Darren Seay, Manager of Business Intelligence, Pilot Flying J
Pilot Flying J is a leading operator of travel centers across the United States. To gain more insight from its sales and labor data, the company implemented a new Microsoft business intelligence (BI) and reporting solution that provides more-detailed store-level views and other analytical improvements. As a result, one Pilot Flying J division expects a U.S.$10 million increase in profits, and the company’s BI team has reduced its report development time by 20 percent. The company’s managers can also use the solution to boost sales.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published March 2012
Business Needs
Pilot Flying J is the largest operator of travel centers and the largest seller of diesel fuel in the United States. Founded in 2001, the company has more than 560 travel centers in 43 states and eight Canadian provinces, operating under the Pilot and Flying J brands.
To gain the most business insight from its data, Pilot Flying J makes extensive use of planograms, which are product diagrams that illustrate how and where retail products should be displayed within stores. The company’s business intelligence (BI) department provides thousands of planograms to internal customers including senior managers, marketing teams, and regional and store managers.
The companywanted to get better sales and labor data from planograms, with the goal of optimizing store sales. “Store managers wanted easilyaccessed data so they could provide performance measurements for cashiers to identify sales issues,” says Darren Seay, Manager of Business Intelligence, Pilot Flying J. “And our marketing team wanted to see under-performing storesand planogram performance at a high level.”Marketing also needed the BI department to provide faster reports. “They needed a more agile solution, and they wanted us to provide planogram prototypes faster,” says Seay.
However, responding to these business needs was difficult. The BI group had been modeling data using an approach that was too complex for self-service BI.Additionally, the company’s marketing departmentcalculated planogram performance manually. Seay says, “It was very time-consuming for them to put together the data.”
In late 2010, the BI team at Pilot Flying J started looking for new technology for its planogram development processes.
Solution
After looking at solutions from several companies, Pilot Flying J chose Microsoft SQL Server 2012 Enterprise data management software for its new BI solution. “We have used Microsoft technologies for many years and have been impressed with its functionality,” says Seay.
In early 2011, Pilot Flying J began developing a new planogram reporting solution based on the SQL Server 2012 xVelocity memory-optimized columnstore index and PowerView features. xVelocity organizes and stores data by column instead of by row. SQL Server 2012 PowerView is a self-service reporting tool in SQL Server 2012 Reporting Services that gives users advanced data visualization and presentation capabilities.
The Pilot Flying J BI team created SQL Server 2012 columnstore indexes on its seven tables of salesfacts, taking approximately 10 minutes to build an index for each table. The tables are partitioned monthly, with 100 million rows of data. The team then queried the view to aggregate daily information, importing that data into Microsoft SQL Server PowerPivot for Microsoft SharePoint. The PowerPivot for SharePoint data is used to speed report prototyping and query response times.
The BI team uses PowerView and new modeling tools in SQL Server 2012 to streamline the planogram-prototyping processes.Planograms are accessed by 1,000 marketing employees, store managers, and senior managers via a Microsoft SharePoint Server 2010 BI portal. The portal targets 20 different categories of performance analysis and serves as a financial-reporting and analytical application that gives users a detailed view of sales performance for store managers and cashiers. The application also holds sales and operational data for all 140,000 products sold at Pilot and Flying J stores.
The solution also includes data-mining functionality for attributes such as interior square footage, sales velocity, and parking spaces, and helps store managers determine the top-selling products to focus on to increase profits.
Benefits
With the new BI solution based on SQL Server 2012, Pilot Flying J has morein-depth analytical capabilities, which will contribute to millions of dollars more in gross profits. The company’s BI team can produce planograms in 20 percent less time, and store managers can use new reports to boost store sales.
Will Improve Profits by $10 Million
By using the new BI solution, employees can now quickly analyze an entire year of planogram store performance and store performance by planogram and linear feet, in addition to being able to drill down into other critical store-level details. For some Pilot Flying J departments, this will lead to bigger profits. “Our director of merchandising for truck supplies and electronics estimates that he will improve gross profit next year by $10 million in his category alone, just by using the new SQL Server 2012–based planograms,” says Seay. “Overall, the SQL Server 2012 solution has completely changed the retail culture of Pilot Flying J.”
Saves 20 Percent in Development Time
Using the SQL Server 2012 PowerView feature, as well as the new SQL Server 2012 modeling tools, the BI team at Pilot Flying J is reducing the time it takes to build planogram prototypes. “SQL Server 2012 PowerView streamlines our prototyping processes and makes us much more efficient,” Seay says. “We’ve cut at least 20 percent off the overall prototype lifecycle. Previously, we had to pull different data sets into separate databases, but now we can quickly match up all those data sets as a group in one place, using the SQL Server 2012 xVelocity feature.”
Helps Store Managers Increase Sales
The organization’s retail managers can use the new solution to boost sales in their stores. “Our store managers have the data they need to make decisions about which products to sell,” says Seay. “They can also compare same-week or same-month sales with previous years to better gauge sales trends. Overall, we have a cutting-edge BI solution. With SQL Server 2012, we can analyze data in very intelligent ways, and we can react a lot faster to changes in our market. It’s definitely a competitive advantage.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.Document published March 2012