Commentary by Stan Ferguson, Chief, Data Management Division, DeCA
The Business
The Defense Commissary Agency (DeCA) supplies more than 12 million U.S. military customers with groceries, meats, and produce at 263 commissaries around the world. Based in Fort Lee, Virginia, with more than 18,000 employees, DeCA sold $5.41 billion in goods in 2006 at a 5 percent surcharge over cost, saving customers more than 30 percent over commercial prices. DeCA takes customer satisfaction seriously, as demonstrated by a 4.61 satisfaction rating by customers on a five-point scale.
The Challenge
An enterprise data warehouse (EDW) is central to DeCA’s strategic objective of costeffective customer service. Throughout the 2000s, DeCA has expanded the warehouse that it uses to collect and track data generated by point-of-sale (POS) systems from commissaries in the U.S. and around the world. DeCA uses the Informatica Power Center platform to source POS data and channel it to a Teradata-based EDW.
With the EDW, hundreds of DeCA regional, zone, and store managers have improved customer service and product offerings by analyzing sales by time and geography, promotion effectiveness, product affinity, checkout time, and other dimensions. Importantly, the EDW also supplies data to DeCA’s computer-assisted ordering system to help ensure that products customers want are in stock.
In late 2006, DeCA began rolling out the first phase of a two-year, $200 million upgrade to POS systems at its 263 commissaries. Aging NCR-based POS terminals and supporting software would be replaced by an IBM hardware and software package that DeCA called CARTS (Commissary Advanced Resale Transaction System). The CARTS system offers touch-screen and self-service checkout, as well as greater efficiency and reliability.
CARTS also introduced a challenge for the data integration processes that drove POS information to the EDW. DeCA would need to adapt its warehouse integration architecture to accommodate XML/XSD data from the new POS systems, while simultaneously integrating flat file data from legacy POS systems.
In the past, PowerCenter had sourced and delivered uncomplicated flat files generated by the POS terminals to the EDW. CARTS would use complex XML files based on an industry standard XSD (XML Schema Definition) as well as complex flat files derived from the transaction data files. Naturally, this introduced new complications and challenges on how the data would be integrated into the warehouse.
In addition, DeCA envisioned moving to a more real-time system by which POS data loads to the warehouse would occur incrementally throughout the day, rather than once a night. DeCA needed a data integration solution that could:
- Be rapidly adapted to seamlessly integrate XML/XSD and complex flat file data
- Simultaneously integrate disparate data from both legacy and new POS systems
- Support greater load frequency and reduce commissary restock time
The Solution
DeCA first selected PowerCenter in 2000 for its near-universal access to data, codeless development environment, and flexibility for future growth. By implementing PowerCenter for data warehousing, DeCA IT personnel and data integration experts from Claraview, a Virginia-based consultancy specializing in business intelligence, were knowledgeable in its capabilities. After due diligence, DeCA decided that PowerCenter was ideally suited to help it transition to the XML/XSD and complex flat file processing required by CARTS.
“Initially, we had concerns over the data integration challenges that arose as we looked at phasing in our new POS system,” said Stan Ferguson, chief, data management division, DeCA. “But Informatica PowerCenter has let us quickly adapt our infrastructure to handle complex file data integration with our EDW with no disruption to our core business processes.”
PowerCenter’s support for complex files such as XML/XSD and complex flat files meant that DeCA could readily access and integrate the new CARTS data, and would not need to identify, test, and purchase a second integration platform, nor would it need to look at custom-coding alternatives that would have been exponentially more costly, time-consuming, and prone to error. PowerCenter’s platform-neutral architecture has enabled development at a higher level of abstraction by removing physical ties to data and allowed DeCA to adapt to different source types without affecting business logic.
“With Informatica, we had a tool already in place that could read the XML files, and then validate those XML files so we can import data out of sources and targets,” said Carol Griffith, EDW system manager. “Compared to other alternatives, it gives us a quick turnaround. We don’t have to invest in any other software or integration platform, or get resources trained in a new technology.”
A team of four Claraview developers, already skilled in PowerCenter technology, was able to rapidly adapt DeCA’s existing PowerCenter solution to handle XML-based source data, as well as complex flat files. The XML/XSD source systems went live in November 2006.
Once completed in 2008, CARTS will be deployed at each of DeCA’s 263 commissaries and PowerCenter adapted to integrate the XML/XSD and complex flat file data and deliver it to the EDW, which stores two years of POS information, or about 4 TB of data (with 7.5 TB of capacity). A full six years of historical POS data is archived in storage.
PowerCenter’s performance in loading up to 15 million rows of XML/XSD data in 30 minutes is well within DeCA’s acceptable timeframes and achieved without the benefit of performance tuning and optimization.
The Results
Save Development Time and Reduce Overall Costs
DeCA’s rapid adaptation of an existing data integration solution to integrate CARTS POS data is due in large part to PowerCenter’s capacity to reuse source and target data definition objects across multiple systems. The reusability, along with PowerCenter’s drag-and-drop ease of use, has dramatically reduced the time otherwise required to develop, test, and implement mappings.
“The reusability is very beneficial,” said Griffith. “Once someone builds a mapping, you just save it and can reuse it elsewhere with some minimal tweaks.” DeCA also leverages reusable mappings to create and validate XML files from data in the Teradata warehouse and send that information to CARTS.
Improve Restocking and Customer Satisfaction with Right-Time Data Integration
By updating POS data every 10 to 60 minutes rather than nightly, PowerCenter enhances the business value that DeCA realizes from its EDW by accelerating ordering and restock processes, particularly important for perishable items. The accelerated load frequency can reduce by a day the time required by DeCA’s computer-assisted ordering system to restock commissary shelves with products that customers want and is an important step in DeCA’s mission of maximizing customer satisfaction. The warehouse and a homegrown business intelligence tool called COMS (Commissary Operating Management System) enable DeCA managers to analyze customer satisfaction, product performance and promotions, seasonal sales fluctuation, and other characteristics.
Enhance Data Accuracy and Trust in Decision Making
The core mission of DeCA’s EDW team is to be the trusted historical central repository for data analysis. PowerCenter has helped the team build that trust with data quality technologies designed to ensure that warehouse data is accurate and reliable. DeCA takes advantage of PowerCenter error handling capabilities to screen data being loaded into the warehouse and deliver alerts on attempted duplicate data entries and other anomalies. By avoiding the “garbage in, garbage out” phenomenon, DeCA’s warehouse team has given business users confidence in the integrity of POS data.
Extend EDW atop a Plat form-Neutral Integration Architecture
In DeCA’s pipeline is an initiative to extend the EDW’s reach by using PowerCenter to integrate data from eight disparate instances of a 1982-vintage legacy application covering ordering, receiving, and inventory. This would give DeCA an on-demand enterprise view of core business activities, rather than manually building a large workfile of data from the eight instances to answer basic business questions. From a broader perspective, PowerCenter’s platform-neutral architecture plays a key role in enabling DeCA to comply with the Department of Defense’s Business Management Modernization Program, in part with an open platform to replace or complement aging, proprietary legacy systems. Also in the works are Power Center-based metadata services to enable users to easily search warehouse data and the establishment of a data management office.