Air Force expands parts tracking app to other must-have data

Project at a glance

Who: Air Force


Mission: To aggregate views of the service's data spread across multiple systems and databases


What was: Analysts compiled reports by drawing data from disparate systems and compiling it by hand or in spreadsheets.


How the new system works: Air Force Knowledge Services automatically draws data from end sources and places it in a data warehouse. The compiled data can be manipulated by Web query and report software.


Users: Commanders, analysts and field workers


Impact: The Air Force has identified excess parts inventories and quickly prepared servicewide reports for Congress. AFKS will be expanded for other fact-finding and analysis tasks.


Duration: Four years to get to the initial deployment


Cost: $500,000 for prototype; $10 million for first phase of full deployment.

WINGMEN: The Air Force's Mike Riley, left, and Eric Wilson say AKFS combines data from multiple systems and allows for trend analysis.

Ron Alvey

Taking Flight: Maj. Peter Milohnic and Capt. Jonathan Gration, pilots with the 27th Fighter Squadron, return home from training over the Atlantic Ocean, Aug. 22, 2004. The Air Force Knowledge Services can aggregate data about their F-15Cs from multiple sources much quicker and more efficiently than previous systems.

Courtesy of Air Force Tech Sgt. Ben Bloker, 1st Communications Squadron

Air Force Knowledge Services can analyze aircraft uptime so efficiently that the Air Force is expanding it to other kinds of intelligence.

CIO John Gilligan called it 'a foundational piece of our future architecture. All the information relevant to the enterprise will be in AFKS.'

It is 'one of the larger government enterprise warehouses that we're aware of,' said Gary Ahrens, a vice president of BearingPoint Inc. of McLean, Va., which worked on the implementation.

Pulling material from 26 databases and information systems, the data warehousing service rapidly summarizes key metrics, such as how many aircraft are ready to fly at any given time.

Commanders access the tool through the Air Force portal, at www.my.af.mil, to get a quick visual summary of equipment readiness. Analysts can do ad hoc queries to generate reports. AFKS currently provides about 20,000 summaries a week.

'We used to have several people analyzing data for six months, and now one person can do it in a few minutes,' Gilligan said.

The Air Force is no stranger to enterprisewide IT, but what's new about AFKS is the way it aggregates data from multiple existing systems.

'If you wanted to look at maintenance data, you could see maintenance data,' AFKS program manager Eric Wilson said. 'But if you wanted to look at maintenance data with supply data, you would have to take output from both systems and work it into your own Microsoft Excel or Access application. It was very time-consuming.'

Data aggregation is increasingly important to assess readiness, he said.

'If I'm looking at what it takes for an airplane to be mission-capable, I'm looking at maintenance data, supply data and a host of other things,' Wilson said. 'In the past we just haven't had the opportunity to get that data together.'

Spotting trends

The Materiel Systems Group of the Air Force's Enterprise Systems Division at Wright-Patterson Air Force Base, Ohio, brought AFKS online in 2002. The program's first goal was to aggregate maintenance information from five base-level maintenance systems. Each company's maintenance team knew what bedeviled their own aircraft but, across the service, there was no way to show trends, such as faulty parts arriving from a manufacturer.

'Each base had its own basic database, so the initial problem was to provide an integrated, online, ad hoc query capability for a global perspective,' said Mike Riley, the original program manager.

The development team visited a number of Fortune 500 companies, such as Wal-Mart Stores Inc., and learned how they made sense of their data: through warehouses.

'We were impressed by the kind of analysis that Wal-Mart was able to do to keep its supply chain moving,' Riley said. 'The Air Force also needs to track a lot of parts and where things are in the maintenance pipeline. Wal-Mart was doing things in hours and days, but the Air Force was doing it in weeks.'

First, the developers had to find a way to put all the data in a single location. Second, they needed tools to make sense of the data.
[IMGCAP(2)]
To transfer data from the original databases into the warehouse, the Air Force turned to PowerCenter from Informatica Corp. of Redwood City, Calif. The extract-transform-load application copies data into Teradata 2 from NCR Corp. of Dayton, Ohio, running under Microsoft Windows 2000 on 32 NCR servers.

Prototyping AFKS cost about $500,000, and the original rollout for 1T of data amounted to $10 million. At first, the warehouse incorporated only basic maintenance records. Then supply records were added to show where incoming parts were in the supply chain and when they would arrive.

Next, the developers added contracting and financial information, which lets managers decide which equipment to repair, based on financial resources and contractor capabilities.

Information from the original databases or management systems is updated regularly'some nightly and some almost instantaneously, depending on the source system. The data warehouse now holds about 16T.

Dashboard view

To execute queries and produce reports, AFKS uses WebIntelligence software from Business Objects Inc. of San Jose, Calif. It presents the information through executive dashboards generated by Business Objects' Enterprise Performance Manager.

The dashboards are usable by everyone from commanders to line mechanics. Commanders can get simple metrics for high-level views'for example, the status of all F-16s. They can ask, 'How many do I have in repair? When will they be out? How many are ready to fly?' Riley said.

Someone curious about why one particular F-16 is down can click through to find out the stated cause.

Analysts can query to spot other trends, such as excess equipment inventories along the supply chain.

AFKS identified more than $600 million in excess parts and recouped $300,000 worth within the first week, said chief architect John Rusnak of Mitre Corp. of Bedford, Mass.

For shop mechanics, AFKS provides Federal Express-like tracking for key parts.

With maintenance and aircraft intelligence now in place, the Air Force will keep adding metrics from other domains. Wilson identified 22 knowledge areas, such as human resources and finance, that could benefit from cross-function metrics.

'As we get more data, the better we are able to leverage AFKS,' Gilligan said.

Reader Comments

Please post your comments here. Comments are moderated, so they may not appear immediately after submitting. We will not post comments that we consider abusive or off-topic.

Please type the letters/numbers you see above