U.S. Defense Commissary Agency

U.S. Defense Commissary Agency Rapidly Adapts to New XML Data Sources from
Point-of-Sale Systems with Informatica PowerCenter Data Integration Platform

Printer-friendly PDF version

“Initially, we had concerns over the data integration challenges that arose as we looked at phasing in our new POS system. But Informatica PowerCenter has let us quickly adapt our infrastructure to handle complex file data (such as xml and complex flat files) integration with our Enterprise Data Warehouse (EDW) with no disruption to our core business processes, and without the time and expense of deploying a new platform. It also allowed us to move to a more real-time model thus improving service to our servicemen and women.”

—Stan Ferguson, Chief, Data Management Division

Fast Facts

Challenge
Adapt data warehouse integration architecture to accommodate XML/XSD data from new point-of-sale systems being phased in at 263 DeCA commissaries worldwide over two years, while simultaneously integrating flat file data from legacy POS systems. Rapidly Adapts to New XML Data Sources from Point-of-Sale Systems with Informatica PowerCenter Data Integration Platform

Informatica Solution

• Informatica PowerCenter
• Informatica PowerCenter native XML processing capabilities

Benefits

• Save development time and reduce overall costs
• Improve restocking and forecasting with right-time data integration
• Enhance data accuracy and trust in decision making
• Integration flexibility atop a platformneutral architecture

Nuts and Bolts

• Data Integration: Informatica PowerCenter native XML processing
• Source: Legacy NCR file-based POS and new IBM XML-based POS data
• Target: Teradata data warehouse V2R5, ODBC connection
• Platform: HP-UX, 11i v1, 8 CPUs

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.

The Challenge

Offering groceries, meats, and produce at affordable prices is an integral part of the compensation package that the U.S. Department of Defense offers to active duty personnel, reservists, retirees, and their families at military installations across the globe. DeCA takes customer satisfaction seriously, as demonstrated by a 4.61 satisfaction rating by customers on a scale of one to five.

An enterprise data warehouse is central to DeCA's strategic objective of cost-effective 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. The Informatica PowerCenter platform sources the POS data and channels it to a Teradata warehouse that takes advantage of PowerCenter/Teradata seamless interoperability jointly built in by Informatica and Teradata

With the warehouse, hundreds of regional, zone, and store managers have been able to improve customer service and product offerings by analyzing sales by time and geography, promotions effectiveness, product affinity, checkout time, and other dimensions. Importantly, the warehouse also supplies data to DeCA’s computer-assisted ordering system and plays a vital role in ensuring that products customers want are in stock.

In late 2006, DeCA began rolling the first phase of a $200 million upgrade to aging POS systems at its 263 commissaries. The old NCR-based POS terminals and supporting software would be replaced by an IBM hardware and software package that DeCA called CARTS, for Commissary Advanced Resale Transaction System.

The CARTS system offers such benefits as touch-screen and self-service checkout, as well as greater efficiency and reliability. But it also introduced a significant challenge for the data integration processes that drove POS information to the warehouse.

In the past, Informatica PowerCenter had sourced and delivered uncomplicated flat files generated by the POS terminals to the data warehouse. But CARTS would use complex XML files that are 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 data 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 turnaround time

The Solution

DeCA first selected PowerCenter in 2000 for its near-universal access to enterprise data, rapid and codeless development environment, and flexibility for future growth. By implementing PowerCenter to support data warehousing, both DeCA IT personnel and data integration experts from Claraview, a Virginia-based professional services consultancy specializing in business intelligence, were knowledgeable in its capabilities. After some due diligence, DeCA decided that PowerCenter was ideally suitedto help it transition from the old NCR flat file data integration system to the XML/XSD and complex flat file processing required by the new CARTS deployment.

PowerCenter’s base platform 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 platformneutral architecture supports development at a higher level of abstraction to remove physical ties to data and allows DeCA to easily adapt to different source types without impacting 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 data integration developers, already skilled in PowerCenter technology, were able to rapidly adapt DeCA’s existing PowerCenter solutions based on the previous data source files to XML-based source data and are getting ready to source from complex flat files in the near future. The XML/XSD source systems went live in November 2006.

Over the next two years, CARTS will be deployed at all 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 from DeCA commissaries is archived in offline storage.

Flexibility for Dual-Source Integration and Greater Load Frequency
PowerCenter’s flexibility will enable DeCA to simultaneously integrate and deliver data from the legacy and new POS systems while CARTS is phased in through 2008 without disrupting business processes.

“The NCR simple file-based POS and CARTS complex file-based POS data needs to be integrated and then the data needs to be presented seamlessly at the front end so it is transparent to end users,” said Stan Ferguson, Chief, Data Management Division. “Informatica gives us the ability to run the two heterogeneous POS systems in parallel atthe same time by dramatically reducing the effort needed to adapt to the new data definitions.”

PowerCenter will also play a key role in enabling DeCA to achieve business goals of greater customer satisfaction by updating the warehouse 24/7 with fresh POS data from CARTS at intervals ranging from 10 to 60 minutes, rather than the nightly load process that had been the norm. In effect, the greater load frequencies can reduce by a day the turnaround time for DeCA’s computer-assisted ordering system to restock commissary shelves with products that customers want.

DeCA officials estimate PowerCenter’s performance at up to 15 million rows of XML/XSD data in 30 minutes. The window is well within DeCA’s acceptable timeframes and is 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, production data integration solution to integrate CARTS POS data into the warehouse 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’ drag-anddrop ease of use has dramatically reduced the time otherwise required to develop, test, and implement mappings, according to DeCA officials. “The reusability is very beneficial,” said Carol 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 back to CARTS.

Improve Restocking and Forecasting with Right-Time Data Integration
By updating POS data every 10 to 60 minutes rather than once a night, PowerCenter enhances the business value that DeCA realizes from its data warehouse by accelerating ordering and restock processes, particularly important for perishable items. The warehouse and a homegrown front-end business intelligence tool called COMS (Commissary Operating Management System) give DeCA managers powerful query and reporting capabilities 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 data warehousing 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 consistent, reliable, and accurate. DeCA’s Informatica implementation 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 the POS data.

Integration Flexibility Atop a Platform-Neutral Architecture
PowerCenter’s platform-neutral architecture is in play as DeCA strategizes over broadening its data integration capabilities to meet strategic business objectives and 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. In the pipeline is an initiative to channel into the warehouse data from eight disparate instances of a 1982-vintage core legacy application covering ordering, receiving, and inventory. This initiative 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. Metadata services to enable users to easily search warehouse data and the establishment of a Data Management Office are also in the works.