Foster & Gallagher is one of the leading direct marketing companies in North America. Primary lines of business include horticultural products, food and gift products, educational toys and publications. The company is divided into five business groups--the Spring Hill Group, Gift Group, Children's Group, Michigan Bulb Group and Corporate Services Group. Headquartered in Peoria, Illinois, the company has offices in nine states as well as a strong presence in Canada, Holland and Japan. In 1997, gross sales approached $500 million. Foster & Gallagher is a majority-owned employee stock ownership company (ESOP).

Implementation Summary

The Foster & Gallagher data warehouse implementation uses the Informix 7.2 RDBMS on top of an RS/6000 SMP (Model R50) running AIX with six 604E CPUs and 2GB of main memory. The system is configured with 50 4.5GB disk spindles using IBM's Serial Storage Architecture (SSA). The backup and recovery architecture uses ADSM with multiple 3490E tape drives. Data mining algorithms for predictive response analysis, cross-sell scoring and direct mail list selection are undertaken using AMSS from DMAS, Inc.

The customer-centric data model in the Foster & Gallagher implementation consists of approximately 50+ tables with atomic order and line-item detail maintained for 6+ years of history. Core tables range in size from tens of millions of rows to hundreds of millions of rows. The ability to construct "as- of" snapshots for any subset of customer households in the database for any point in time that the end users required for data mining or analysis was critical to the project's success. These "as- of" snapshots are used as input to data mining models to facilitate the development of predictive scoring algorithms for optimal direct mail list selections. Because of the flexibility required in the household population and "as-of" date criteria allowed for specification of these profiles, dynamic construction must be supported on demand by the end users.

A particularly subtle point is that household composition as of the specified snapshot date must be captured as part of the profile construction for the data mining algorithms. Households consist of one or more individuals and their composition changes with marriage, divorce and other life events. The data mining algorithms employed for quantitative modeling require that these changes not be lost. As a result, the data warehouse has a strictly enforced "no update" policy to ensure that all change transactions to household composition, order attributes, and so on, are explicitly maintained over time. All change transactions are time stamped and the scripts for building "as-of" constructions use SQL functions to extract all data "as- of" the point in time specified by the end user.

Key Business Benefits

The key business benefit of the Foster & Gallagher data warehouse is an integrated customer view of the data from across the company's multiple catalog groups. Prior to construction of the data warehouse, each catalog group had visibility primarily into its own customer base with limited access to information about shared relationships with other catalog groups. Bringing together a complete corporate customer profile enabled significant enhancements to capabilities for cross-selling and construction of more accurate predictive response models for direct mail list selection. John Lappegaard, president of Spring Hill Group, summarizes the business benefits of the Foster & Gallagher data warehouse, "As direct marketers, we rarely meet our customers face to face. For us to know our customer requires that we capture, maintain and analyze a vast amount of data and be able to access the results easily and apply them to our business. Our investment in the DSS system has led to dramatic improvements in business planning and execution." Better management of long-term profitability of promotions through customer lifetime values analysis and customer-oriented targeting models are expected to provide even bigger returns over the next year.

Practical Advice

Data warehouse projects are not like other technology projects typically undertaken within the corporate IS environment. In fact, the most important realization for a successful data warehouse implementation is to understand that it is not a technology project, but rather a business project. The success or failure of a data warehouse depends on the business value that it delivers. Therefore, the entire project must focus on this goal. Some of the key insights taken from the implementation experience at Foster & Gallagher follow.

Drive the project from the business. The data warehouse project team included five dedicated resources from IS and a consulting team of four individuals from Strategic Technologies & Systems--but the most important members of the project team were the business end users and executives. An end-user team was explicitly allocated to the project and was directly involved in all aspects of the implementation. Everything from phased application definitions and business deliverable ROI quantification to hardware and software platform selection to historical data retention strategies was driven from the end-user team involvement in the project. Executive sponsorship of the data warehouse from the very top of the organization was enlisted from the onset and resulted in very high visibility for the project.

Do not underestimate the work involved in data quality management. Over the last few years, it has become common knowledge that one of the more time-consuming tasks in data warehouse construction is data sourcing and transformation. However, one aspect of data sourcing and transformation that is often underestimated is the data cleansing piece. The source data is almost never as clean as the organization would like to believe. It is essential to budget sufficient resources to implement a total quality management (TQM) process for data quality management within the warehouse. This involves development of metrics and measurement scripts for data quality and sufficient time in the implementation schedule to allow for sourcing and re-sourcing of the warehouse data using data cleansing programs and source system fixes until data quality meets the end-users' standards. Business rule validation, referential integrity, domain value checks and frequency distributions should all be included in the data quality measurements. We made use of techniques developed at the Massachusetts Institute of Technology in the application of a House of Quality framework for Data Quality Management in the warehouse to provide focus and specific metrics for our efforts.

Know your database. A data warehouse workload exercises the database in ways which are very different from a traditional operational system implementation. As a result, design considerations in maximizing the performance and usability of the database structures are quite different. For example, most of the queries in the Foster & Gallagher workload are quite complex and typically involve full table scans and hash joins with very large data sets. Understanding how the RDBMS and operating system interact to achieve effective data striping is essential.

Debra Keach, Heather Wilson and Debra Mayer at Foster and Gallagher.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access