CATEGORY: Data Integration

REVIEWER: Deb Masdea, director of business knowledge services for The Scotts Company.

BACKGROUND: The Scotts Company is the world's leading supplier of consumer products for lawn and garden care, with a full range of products for professional horticulture as well. Scotts' core business lines include consumer lawns and growing media, consumer garden and control, professional nursery/greenhouse groups and Scotts Lawn Service (SLS).

PLATFORMS: The company's enterprise systems run on a combination of Sun and HP/Compaq systems, with a combination of UNIX and NT operating systems.

PROBLEM SOLVED: In 1999, The Scotts Company consolidated many of the core enterprise applications that we accumulated throughout the 1990s to reduce our dependence on mainframe programmers and build a unified view of sales, ordering, shipping and customer services activity across all products. We selected SAP R/3 as our corporate ERP standard and SAP BW as our business intelligence platform. By spring 2001, 90 percent of our U.S. operational data was in R/3 and SAP BW. At that time, our IT group needed to integrate legacy and third-party sources into SAP BW. For example, our business development teams wanted point-of-sale (POS) data from retailers added into SAP BW to support reactive, in-season tasks such as replenishment order processing, product deployment and in-store execution. This knowledge provides a foundation for strategic collaboration with our customers, allowing for improved forecasting, ad planning and targeted marketing. While these assorted data sources represented less than 10 percent of our corporate data, they were essential to achieving further ROI from our SAP environment. While ABAP programs and flat files had worked for internal data migration in the past, these secondary sources were more diverse and complex. We needed a more extensible, less resource-intensive solution.

PRODUCT FUNCTIONALITY: In the SLS application, data from 30 separate FoxPro databases is loaded into R/3's general ledger system to support corporate and month-end financial reporting. The data is consolidated, summarized, validated, enhanced and finally batch loaded into R/3. In the POS integration process, DataStage loads millions of records from key retail partners each day into SAP BW. The load process, which begins with consolidated flat files from Scotts' Gentran EDI system, runs at a rate of 800K records per hour and includes aggregation, validation and enhancement transforms. Key among these transactions is a transform process that modifies each POS transaction record by replacing the retailer's internal product codes and other data with Scotts' standard values. The process uses a hash look-up file of Scotts' product code data that is extracted from R/3 with the DataStage PACK for SAP R/3. It also includes a data quality check based on DataStage's Quality Manager that tags, rejects, corrects and reprocesses all problem POS records in a real-time, closed-loop process. The finished data is loaded into SAP BW via the DataStage Load PACK for SAP BW.

STRENGTHS: DataStage XE is closely integrated with SAP BW, making it easier and faster to integrate non-R/3 data into SAP BW. For example, SAP BW comes equipped to recognize DataStage XE as an external source system, allowing DataStage XE jobs to be initiated from within SAP BW as an InfoPackage. Similarly, DataStage can read SAP BW table structures, allowing for faster, more accurate development of SAP BW load jobs. DataStage XE also performs an automated meta data match to ensure compatibility of non-SAP sources with SAP BW.

WEAKNESSES: While DataStage XE collects and creates meta data regarding data sources, SAP BW and transformation processes, it does not automatically generate meta data text files for business users. Functionality for parallel execution of load jobs into BW through DataStage has not been available, slowing our daily load of POS data. We plan to upgrade to DataStage Load PACK for SAP BW version 3.0, which offers built-in data streaming and multi-threading capabilities.

SELECTION CRITERIA: Scotts selected DataStage XE based on its automated graphical development environment because Ascential is a strategic data integration partner of SAP and because of DataStage's close product integration with SAP BW.

DELIVERABLES: DataStage XE's graphical development model and built-in transformation library speed development and simplify integration of third-party and legacy data into SAP BW.

VENDOR SUPPORT: Ascential Software provides excellent support, with nearly all support calls fielded by live technical staff and resolved within one day.

DOCUMENTATION: The DataStage XE documentation does an excellent job explaining the product's core metaphor and offers a comprehensive guide to advanced features.

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