Pennzoil-Quaker State Select SQL Server 2000 Analysis Services for Comprehensive Business Intelligence Solution
by Microsoft Staff
Pennzoil-Quaker State Company (PQS), a worldwide leader in automotive consumer products, needed a flexible and responsive business intelligence system to evaluate marketing effectiveness and other key sales and marketing metrics. Using Microsoft SQL Server 2000 with Analysis Services, Microsoft Certified Partner Symmetry Corporation implemented a solution for PQS that brought together internal data with data from five different market research providers. The robust multidimensional analysis capabilities of SQL Server 2000 Analysis Services were used to provide users with a wide range of views into the data, enabling users to easily access information in the form most appropriate to their business needs.
Pennzoil-Quaker State Company (PQS) was formed in late 1998 through the spin-off of Pennzoil Company's marketing, refining, and fast oil-change businesses and the simultaneous merger of those businesses with Quaker State Corporation. The merger resulted in one of the leading automotive consumer product companies in the world. PQS's consumer brands include top two motor oil brands Pennzoil® and Quaker State®, major maintenance and appearance chemicals, and top automotive accessories. In addition, the company owns Jiffy Lube International, Inc., the number-one fast oil-change operation, and holds a significant position with other channels of the installed motor oil market.
With historical roots in oil and gas exploration and production along with motor oil refining and marketing, the newly formed PQS needed to widen its marketing focus from oil, gas, and motor oil to consumer-packaged goods. To maintain and grow its market position, the company needed to understand how effective its marketing efforts were. This required a database that could bring together disparate data with different dimensionality and different granularity. Data on internal shipments, for example, is tracked by individual stock-keeping unit (SKU), whereas marketing data is tracked by product category.
The ability to perform market-share analysis using both external and internal data is a vital component of PQS's outwardly focused marketing strategy. PQS markets its products to consumers via advertising and promotions, but sells primarily to distributors and retailers who in turn resell the products to consumers. To address this issue, the company's new solution would require sophisticated modeling capabilities that could support the analysis of both internal and external data provided by market research companies. Data from five different market research companies had to be combined with PQS's own internal data in a way that provided managers and analysts with useful information.
To further complicate matters, data suppliers had different definitions for geography, product, and demographics. "Making the data meaningful was no small task, especially given that we were analyzing our shipment data from SAP, point-of-sale data from Information Resources Inc., consumer panel data from NPD, media advertising information from Nielsen, print ad information from Media Markets Report, point-of-sale data from key retail accounts, and Spectra demographic data," says Ken Forren, Senior Business Analyst and Marketing Effectiveness Tracking System (METS) Project Leader for PQS.
To meet these challenges, PQS selected Microsoft SQL Server 2000 and partnered with Microsoft Certified Partner Symmetry Corporation to build its new solution. "SQL Server 2000 was a natural choice for our new business intelligence system," says Forren. "We were already using SQL Server in almost every other area of our business. We like it because it offers high performance and availability with a low total cost of ownership. The addition of analytical capabilities to the product with SQL Server 2000 Analysis Services was icing on the cake for us."
Incorporating external syndicated data into the system was an important first step to providing new metrics that would help change the company's internal focus to an external, consumer-driven focus. PQS uses Data Transformation Services (DTS) to bring the source files from external data providers into SQL Server 2000. "DTS automates this process, making it easy to import and transform data from multiple, heterogeneous sources," says Forren. "Using the DTS graphical interface, we created custom transformation objects that simplified the integration of our third-party data sources. DTS also made it easy to schedule data loads-a significant task considering we import data from five different external sources on a weekly or monthly basis."
With so many sources of information, seamless and efficient integration of data was key. Using the Microsoft Visual Basic® development system, Symmetry and PQS spread the data across several partitions to ensure efficient processing. The Decision Support Objects (DSO) model in Visual Basic was used to control both the initial and incremental processing of cubes. Symmetry partitioned each cube into two partitions: one for history and one for current data. As new data comes in, only the incremental data has to be processed, not the entire cube.
Within Visual Basic, the ability to execute queries and return data sets helped Symmetry determine which cubes needed to be processed. "With other major multidimensional databases, we don't have the procedural control we need to combine relational and multidimensional operations," says Ed Gliwa, regional manager of Symmetry. "The additional control provided by Visual Basic enabled us to create a much more efficient, high-performance database."
Tricky data integration requirements were only the first hurdle PQS faced in designing and building the Marketing Effectiveness Tracking System. In addition, the complexity of PQS's analysis needs demanded a multidimensional database that could handle a large number of dimensions and cubes with quick response times. One of the cubes Symmetry built had 28 dimensions; SQL Server delivered subsecond query response times.
"Analysis Services' intelligent aggregation capabilities minimized the database 'bloat' that so often occurs with other multidimensional database technologies," says Forren. "The result is that we are able to handle more data than ever before with improved performance and faster initial and incremental load times. The Analysis Services aggregation engine in SQL Server 2000 is lightning-quick. Our old system took all day to process. Now we can process the entire database in two to three hours even though the new system has more data."
Using the robust MDX language that is part of Analysis Services, PQS created custom measures so that its analysts could get a better understanding of the effect of different marketing campaigns in different geographic regions versus the competition. The system has been key to understanding and increasing the organization's marketing effectiveness.
Selecting the Right Front-End Tools
The Marketing Effectiveness Tracking System has two levels of users: analysts and end users. End users need easy-to-use reporting capabilities through a browser-based user interface that enables them to be self-sufficient, and analysts need fast, easy, and accurate forecasting for measures such as volume and market share. Previously, PQS analysts had to learn different tools in order to analyze each different data source.
PQS evaluated several tools and chose ProClarity as its primary front-end tool. "We have hardcore analysts that need advanced visualization capabilities to help model complex marketing problems," says Forren. "ProClarity provides an open object model with components for customizing and extending ProClarity's capabilities."
With all the data sources in one Microsoft SQL Server database and with access through a single front-end tool, PQS analysts can spend time on analysis instead of learning the idiosyncrasies of each data vendor's tool. The result is that the analysts are learning new information about the company's competitive position in the marketplace and how to maintain and even grow its market share.
Moving Forward with SQL Server 2000 Analysis Services
Moving forward, PQS is looking at using the data mining and action capabilities in Microsoft SQL Server 2000 Analysis Services. "With so much data from so many different sources, the data mining feature will provide a valuable tool to help our analysts perform even more focused analyses," says Forren. "We're also especially looking forward to using the action feature, which allows users to initiate an action that retrieves information about a selected item. Imagine how useful it will be for analysts to be able to view a competitor's ad along with the market response numbers to that ad."
The future for the Marketing Effectiveness Tracking System also includes adding point-of-sale data from other key retail accounts as well as data from other business units. "Right now, we're analyzing data for the motor oil, appearance, lubricants, and chemicals business units," says Forren. "We will be adding data from other categories such as automotive accessories. Because SQL Server 2000 Analysis Services scale so efficiently and easily, we anticipate that, even though we will be adding more data to the system, overall system response time will be the same. We also have plans for an Internet rollout to our report-driven users so the information they need will be at their fingertips. ProClarity's ActiveX® and COM [Component Object Model] architecture gives us a great deal of deployment flexibility, scalability, and extensibility."
Selecting the Right Partner
"We selected Symmetry Corporation because of their proven ability to deliver robust analytical systems," says Steve Koch, Senior Vice President, Marketing Services and Customer Development, for PQS. "In the past four years, they have worked on two other OLAP [online analytical processing] systems for us with excellent results. When we needed to evaluate OLAP products, Symmetry's in-depth understanding of OLAP technology was invaluable in helping us to choose both front-end and back-end OLAP products. Another key strength of Symmetry's consultants is their knowledge of business processes and how to build analytical systems that provide not just technical but business value for our managers, analysts, and retail account teams."
Symmetry has provided OLAP consulting services to Fortune 1000 corporations since its inception in 1986. By maintaining its core focus, the company has developed a highly experienced cadre of OLAP consultants who understand both multidimensional and relational database technology. Symmetry's consultants know how to design business intelligence systems that interface seamlessly with operational systems, data warehouses, and external data sources.
Profile - Pennzoil-Quaker State Company (PQS) is a leading worldwide automotive consumer products company, marketing more than 1,300 products with 20 leading brands in more than 90 countries.
Situation - PQS needed a better sales and marketing analysis system to provide improved support to the retail channel, measure marketing effectiveness, and assess new product sales.
Solution - Using Microsoft® SQL Server™ 2000 Analysis Services, Symmetry Corporation implemented a business intelligence solution for PQS that integrates internal data with information provided by five different market research providers.
Microsoft Technologies/Products Used - Microsoft Windows® 2000 Advanced Server operating system, Microsoft SQL Server 2000 Enterprise Edition with Analysis Services
Benefits - SQL Server Analysis Services is able to recalculate the entire database in less than three hours versus an all-day run with the former system, enabling managers to make faster and better decisions about marketing and product development directions.
Architecture/Configuration - Compaq ProLiant DL580 four-processor server with 4 gigabytes of RAM
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Resource Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to www.microsoft.com.
For more information about Symmetry Corporation, call (415) 453-7966 or go to www.symcorp.com.
For more information about ProClarity Corporation, call (208) 344-1630 or go to www.proclarity.com.
For more information about Pennzoil-Quaker State Company, visit its Web site at www.pennzoil-quakerstate.com.
Denise Cochrell at Microsoft provided permission to use this case study at DSSResources.COM on Monday, April 29, 2002. Angie O'Hara, ProClarity Corp. Public Relations Manager, worked to arrange this publication. Posted at DSSResources.COM June 17, 2002.
Microsoft Staff, "Pennzoil-Quaker State Select SQL Server 2000 Analysis Services for Comprehensive Business Intelligence Solution", Microsoft, Inc., January 15, 2002, URL DSSResources.COM.
© 2002 Microsoft Corporation. All rights reserved. Used at DSSResources.COM by permission. Product names and trademarks may be trademarks and/or registered trademarks of their respective companies.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.