Customer Solution Case Study
/ / Bookseller Gains Business Insights Across Sales Channels with New Data Warehouse
Overview
Country or Region: United States
Industry: Retail
Customer Profile
Barnes & Noble, based in New York City, is one of the largest booksellers in the United States. It operates 821 stores and employs about 40,000 people.
Business Situation
Barnes & Noble wanted to extract more business intelligence from its operational systems, including sales and inventory data, to make better business decisions for greater profitability.
Solution
The company deployed a new data warehouse using Microsoft® SQL Server™ 2005, SQL Server 2005 Integration Services, and hardware from HP and Intel.
Benefits
n Valuable transactional information is available to employees much faster thanbefore.
n Enhanced reporting and analysis improve business decision-making.
n Solution helps improve customer service because employees are better informed. / “We are currently processing anywhere between 1 million and 3 million sales transaction records, and it takes no more than 10 minutes to get the records into the data warehouse. That rate was not possible before.”
Eliot Gardener, Project Manager, Barnes & Noble
Barnes & Noble, the world’s largest bookseller, operates 821 stores in 50 states. To help improve its business operations and respond better to customer needs, the company needed new business intelligence tools that could access information faster and provide more detailed reports to managers. The company, which runs Oracle databases for operational systems, decided to deploy a new data warehouse using Microsoft® SQL Server™ 2005 running on an HP Integrity Superdome server equipped with Intel Itanium processors. Today, daily transactional information is loaded faster, reports are available to managers quickly for more insightful business decisions, and the company has a long-term solution thatis improving profitability and enhancing relationships with Barnes & Noble customers.
Situation
Barnes & Noble is the largest bookseller inthe United States. As of August 2005, thecompany owned 821 bookstores in 50states plus the www.bn.com online store.About 80 percent of its stores operate under the Barnes & Noble Booksellers trade name, and the company also owns and operates B.Dalton Bookseller. Through these outlets, the company offers trade books, hardcover and mass-market paperback books, magazines, computer software, and music and video titles, as well as runs in-store coffee shops. Headquartered in New York City, the publicly traded company employs about 40,000 people and reported revenues of U.S.$4.87 billion in its most recent fiscal year.
Trying to Better Manage a Large ProductDatabase
Barnes & Noble survives in an extremely competitive industry. To grow and compete successfully on a national level, Barnes & Noble needs to convince customers that they will likely find the product they want—and that if the product isn’t available at the moment, it will be very soon.
To back up this promise, Barnes & Noble maintains an active list of more than 2.2 million titles. Its complete inventory of products for sale, which includes both its book titles and its other retail offerings, tallies up to more than 7.3 million items.
Until 2004, Barnes & Noble had relied on operationally oriented Oracle database systems to provide business intelligence on its inventory and sales transactions. Although the database was capable of holding the company’s large base of stock-keeping units (SKUs), Barnes & Noble had trouble building valuable business intelligence from the information that could help drive merchandising and inventory planning.
“To access all of our sales details, store-level inventory, and sales data, we had to go through our merchandising system,” says Lou Ann Leary, Vice President of Application Development for Barnes & Noble. “This hindered any kind of ad hoc access to valuable business information because getting at that data would interfere with operational systems when people were trying to complete orders.”
Seeking Enhanced Business Intelligence
Within the company’s senior management, there was a growing sense of urgency that the company needed better business analysis tools not only to stay abreast of all of the information in the database system, but also to make faster and more informed business decisions. Although better business analysis was especially important for executive management, it was also a necessity for certain key employees, including about 50 buyers, the subject matter experts who influence what will be stocked by the retail stores, and about 20 field merchandisers, who are responsible for regional groups of stores.
Leary says that the company needed current sales data as well as historical information, which could be used to understand trends, such as in seasonal or regional sales. That kind of information could be used to shape promotions and marketing campaigns.
“There was no easy way to produce reports that could provide insights into historical trends or key performance indicators on current data, such as best and worst subjects in sales, membership sales, sales by departments, and out-of-stock situations,” Leary says. “Producing reports from the database system usually required the assistance of programmers. We had to devote two or three IT developers just to extract database information and produce reports.”
Solution
Following discussions between senior Barnes & Noble executives and Microsoft, Barnes & Noble decided to participate in a joint proof-of-concept project to create a new data warehouse using about six months of actual Barnes & Noble sales information. The proof of concept was designed using Microsoft® SQL Server™ 2000 running on the Microsoft Windows Server™ 2003 Enterprise Edition operating system. Both are part of Microsoft Windows Server System™ integrated server software. Hardware for the project was a 64-bit HP Integrity Superdome server equipped with 20 1.6-gigahertz Intel Itanium 2 processors and 84 gigabytes of RAM. The project was conducted at an HP Solution Center. By September 2005, the relational database was moved to a new SQL Server 2005 relational database.
To help create the data warehouse, Barnes & Noble implemented a beta version of SQL Server 2005 Integration Services, which provides the features, tools, and functionality to build both classic and innovative extraction, transformation, and loading (ETL) applications for the enterprise. It can be used to transfer millions of rows of data to and from heterogeneous data sources, such as Barnes & Noble’s transactional systems. It provides new ways to glean business intelligence from information by offering complete data integration, cleansing, movement, and shaping. SQL Server Integration Services also delivers significant ETL performance improvement.
Additionally, the project included SQL Server 2005 Reporting Services, SQL Server 2005 Analysis Services, and the ProClarity Analytics Server, which provides front-end reports using information from the SQL Server data warehouse.
Using a set of typical company data as the test basis, Barnes Noble compared the results of the SQL Server solution to those of an Oracle solution running Real Application Clustering on the Linux operating system. On the basis of that comparison, the company realized that the SQL Server solution would be less expensive to develop and implement than the Oracle solution.
The company also estimated that the SQL Server solution would perform up to 200 percent better than the Oracle solution in the ETL process, which would be critical in accessing business information faster for more effective business decisions.
As a result of the test, Barnes & Noble went into production with the SQL Server solution. In early 2005, the deployment team, including Barnes & Noble staff and technical representatives from its partners, began work on a production version of the SQL Server data warehouse, with the server holding about 2.5 terabytes of data.
The company uses a multidimensional data model in the data warehouse and implemented slowly changing dimensions, using the Slowly Changing Dimension wizard in SQL Server Integration Services. In addition to the HP Integrity Superdome, the production solution also includes an HP ProLiant server that runs SQL Server Integration Services, which processes data pulled from two Oracle servers that receive information from Barnes & Noble’s operational systems every night. The solution also includes a dual-processor HP ProLiant Web server that provides information over the corporate intranet.
For backup and recovery of the data, Barnes & Noble is using LiteSpeed from Quest Software. LiteSpeed provides integrated compression and encryption in the backup process, recovering storage costs and cutting hours off backup and restore times.
Barnes & Noble contracted with Microsoft Services to design the architecture and support the development of the solution. The Microsoft Services consultants designed the SQL Server databases and facilitated the design of the data transformation programs.
Microsoft Services is also helping create online analytical processing (OLAP) cubes and is assisting Barnes & Noble in developing reporting services and data-mining initiatives, which are scheduled to be in production by the 2005 holiday season. The OLAP cubes consist of three physical cubes, each of contains 13 dimensions (9 physical) and 150 weekly partitions.
Benefits
With the significantly enhanced ETL process offered by SQL Server Integration Services, Barnes & Noble decision makers have better, faster access to information from the company’s operational systems. The new reporting tools and the OLAP cubes created by the Barnes & Noble IT department give the company’s buyers and field merchandisers an easy means of creating reports and views of data based on different criteria without having to turn to programmers for help.
These reports and new capabilities, in turn, are helping company employees see more deeply into key performance indicators, historical trends, and other information. In the long run, the solution is expected to help Barnes & Noble be more responsive to customer needs and market conditions, which will boost the potential for higher sales and greater profit margins.
Valuable Information Available Faster
The extraction, transformation, and loading process represents the biggest part of creating a data warehouse. With the new ETL technology available in SQL Server Integration Services, data from the operational systems is taken from the Oracle servers, transformed in real time and in parallel, and deposited directly into the data warehouse without the need for any intermediate databases.
“We are currently processing anywhere between 1 million and 3 million sales transaction records, and it takes no more than 10 minutes to get the records into the data warehouse,” says Eliot Gardener, Barnes & Noble Project Manager for the new data warehouse. “That rate was not possible before.”
Because of the rapid extraction and loading of sales and inventory data, company employees and managers no longer have to wait to get information that can help them make business decisions.
“On a busy day, we can have anywhere from 3 million to 4 million transactions,” says Leary. “In the past, it would have simply been too time consuming and disruptive to operations to try and retrieve that data. So on Sunday mornings, we did out-of-stock reports. That meant stopping the operational systems for about four hours. The approach is cumbersome and of course interferes with the smooth flow of business.
Enhanced Reporting and Analysis for Increased Business Agility
“Today we no longer have to interrupt the operational systems to access important data,” Leary continues. “The business intelligence data is available on the same dayand within hours of when operational systems recognize the transactions. This will help our merchants and field merchandisers get a much clearer picture of the day-to-day business.”
The SQL Server Reporting Services, Analysis Services, and ProClarity reports provide richer and clearer views of critical company sales and inventory activity.
“We’ve already created a couple of dozen kinds of reports, some using Reporting Services and some with ProClarity,” says Leary. Information is presented in a variety of formats, such as graphs and tabular data.
The reports are available on the sites that the company created using Microsoft Office SharePoint® Portal Server 2003, also part of Windows Server System. These sites provide different groups of users with easy access to well-defined and easy-to-understand overviews of data in one report.
The company also plans to develop a dashboard application to make high-level sales and company data available to senior executives. Leary says that company executives use the reports for inventory planning and control, managing store finances, distribution center operations, and other tasks. With better access to historical trends, it is easier for the company to plan seasonal promotions, such as placing certain types of books and other retail items near cash registers around certain holidays.
Using SQL Server Analysis Services, Barnes & Noble is able to use OLAP cubes to understand multidimensional data that is continually updated. The IT department has created several types of OLAP cubes, ranging from 56 gigabytes to 277 gigabytes. Company managers use these cubes to understand customer buying habits and trends that can be utilized to boost individual store sales.
For instance, using Analysis Services data mining, a merchant could examine the sales of the latest popular CD in relationship to the top 50 items that would make effective add-on sales with the CD. This insight into when and why customers buy particular items can assist the company in planning.
“Previously, producing that kind of information required so much effort that it would only be done once a week,” Leary says. “Now we can run that kind of analysis every day, and it typically takes between 5 and 30 minutes. It’s an incredible, valuable tool for understanding what is going on in our stores—and responding appropriately.”
Better Customer Experience
The business intelligence available to Barnes & Noble through its new data warehouse solution will ultimately help create better customer relationships, which in turn will improve the company’s business.
“How does this technology help our business? By helping us facilitate a more positive customer experience,” Leary says. “Creating that experience means we have to get the right information into the hands of our booksellers in a timely fashion.”