RAF Defence Equipment and Support Logistics

When your core responsibilities include keeping expensive, peace-keeping aircraft flying, the quality and immediacy of your maintenance and supply chain reporting activities will be paramount.

Previously, the Defence Equipment and Support department had clipped wings when it came to information reporting from its air logistics engineering systems. Although a wealth of engineering, asset management and other essential data was being recorded and safely stored, the process of extracting and reporting on this information was far from easy or efficient. So, when the RAF introduced the Typhoon multi-role combat aircraft, it decided something needed to be done.

So successful was its employment of specialist reporting solutions from OpenText™ Magellan™ Analytics Suite to provide Typhoon reporting efficiencies, the DE&S Air Logistics Reporting team is now exploiting the OpenText capabilities to replace other reporting solutions for other aircraft platforms.

Manual manipulation

“Typhoon data is stored in two different data sources, which we need to pull together to produce reports,” explained Squadron Leader Alan W Moore, head of reporting services within the Information Exploitation Branch, Supply Chain Support Programmes, within Defence Equipment and Support at RAF Wyton.

For other platforms, it didn’t help that the main database, Informix, was an older, legacy system. To provide actionable decision-support information from this source to internal operations staff, contractors and clients, Squadron Leader Moore’s department had the painstaking task of extracting the data using SQL queries and manipulating it using spreadsheet macros and then presenting the data in an appropriate way, a process which was not efficient for report customers to perform.

The Informix system holds logistics data, such as engineering and asset management records, including vital maintenance and product life parameter information, as well as reference data about the structure of the aircraft. Comprehensive reporting depends on being able to pull together data and presenting it under the reference structure.

Although Moore’s team did have some reporting capabilities in a business intelligence tool with the introduction of the Typhoon aircraft, this had several limitations, not least its inability to draw data from more than one source. “Modern reporting tools are generally very good at manipulating data from modern data sources, but they are not so good at looking backwards and working with older systems,” Squadron Leader Moore noted. It became clear that the RAF needed a dedicated, powerful reporting capability with greater flexibility.

Across the disparate systems, the team needed to extract data from numerous discrete sources, including the RAF’s Logistics Information Technology System (LITS) and the Maintenance Data System (MDS), data from the back-office system, plus the Management of the Joint Deployed Inventory Proof of Concept Management Information System (MJDI POC MIS) which tracks the flow of material.

“As it was, we only had one reporting tool for one application, and we couldn’t employ this to get a complete picture of activities across whole fleets when fleet data was managed in different E&AM systems,” Moore recalled. Trying to achieve this fuller picture was inefficient for his department, which at that time was populated with some 30 people dedicated to the job of generating reports.

Multiple challenges, single solution

When the DE&S’s Management Information Branch went to the market, its mission was to find a modern reporting tool that could effortlessly draw data from across new and older systems.

There were other critical requirements, too. Said Moore: “We needed a solution that would comply with MOD Architecture and be run over the MOD RLI intranet, connect to multiple database sources of differing standards, produce output in multiple, graphical formats and that would minimize development resources.”

Crucially, the reporting application also needed to provide web-based reports that could be manipulated by customers. The RAF needed complete flexibility, since a further objective was to maximize use of internal resources, as well as getting critical, meaningful, accurate information to the users that needed it without delay.

Requested reports can range from those with direct operational importance, as used by operating squadron personnel or support organizations, to those relating to customer support agreements and contract performance management. To ensure that accurate, up-to-date, comprehensive and relevant information was delivered to the right people at the right time, Moore’s team needed to establish a fully network-capable system that extended from the technicians on the ground to customers in different support organizations and which could be accessed flexibly across different locations, using workstations and laptops supporting both remote and autonomous use.

“Before, we didn’t have network-based tools, so there was no web access,” Moore explained. “We wanted the user to be able to log in wherever they were on the network, particularly for support staff who move from site to site.”

Flexible automation from OpenText

To reduce the burden on Moore’s team and ultimately to enable headcount reduction (the team has now shrunk by two-thirds, from 30 to 10 people), meant automating report generation so that users could request and process their own reports against live or operational data stores.

The RAF found all of the capabilities it wanted, and more, with the OpenText™ Magellan™ BI & Reporting solution.

“Whereas before, we had to produce lots of individual reports for each piece of equipment, we can now rapidly generate a single, comprehensive, holistic report across the whole fleet, yet present this only in terms of what is relevant to a Harrier tradesman or a Tornado guy,” Moore said.

This has significantly reduced the report maintenance burden as there are fewer reports to maintain, while, more importantly, ensuring users get the information that is directly relevant to them quickly.

By contrast, with CA’s IQ/Eureka software, the department had been generating 368 separate reports (approximately 300 relating to asset management, the rest to maintenance management). Moore’s team has now been able to whittle this down by more than two-thirds (70 percent). The users benefit directly as well, as reports are more immediate and easier to digest. While this work is not yet fully completed and customers must confirm that the OpenText reports satisfies airworthiness requirements, it is expected that the IQ reports will be phased out in 12-18 months. This seems long but a period of parallel running is required by the users of airworthiness reports.

Pixel perfect, clear graphics

“The functionality of OpenText Magellan BI & Reporting is very impressive,” Moore noted. “We can do a lot of the manipulation in the report coding structure itself, rather than having to extract the data and run macros first, which was very time-consuming. What used to take up to an hour now takes just a few minutes. We’re employing more of a thin-client approach to reporting now, if you like, which is much more efficient.”

Indeed, the RAF has now deployed its OpenText solution on a Citrix farm for further efficiencies. This means report developers have the freedom to move about and can work from anywhere.

Moore also likes the “pixel perfect” output he gets with his OpenText reports, and the ability to bring reports alive with interpretative graphics. “We need to be able to reproduce the exact structure of the page, and this is very easy with OpenText,” said Moore. “We can also produce all of the data in graphical format, using traffic lights, dashboards or pie-charts to display problems.”

“These are capabilities we are exploiting now and it’s having a great impact,” Moore said. “Our customers are happy with the data they are getting back from us. The next step is to hone this further by asking customers to give us their data preferences, so that we can focus our reports even more by drilling down using graphics.”

Customer control

To this end, the RAF has invested in the OpenText query module, which allows objects to be created and made available to customers. “This gives our customers OLAP reporting functionality, so they can request specific columns of information or tables relating to specific products. They can order, sort and present the reports as they want to see them, whether by squadron, equipment name or other parameters,” Moore explained.

Equally importantly, the ability to set parameters and define log in rights means the data can’t fall into the wrong hands. “This is particularly important if the users are commercial organizations; it means we can be sure British Aerospace won’t see data about Rolls-Royce faults, and vice versa,” Moore added.

Usage-based log in also creates greater visibility into who is requesting the reports and how these are being used, so that Moore and his team can prioritize resources and rectify any reporting problems efficiently

Field efficiencies

Such is the flexibility of the OpenText solution and the way it has been deployed that the RAF has been able to extend its information flow to far-flung outposts, such as Afghanistan and Iraq. “Very up-to-date information on material flow can now be run by the staff out there,” Moore said.

Not all reporting needs to be up to the minute however, Moore noted, and it is an aid to overall efficiency that the team can differentiate between time-dependent and less urgent reports. For example, fault trend reports do not need access to live faults data and data that is in excess of 24 hours old is fully acceptable.

The next steps in the deployment of OpenText is to integrate more of the RAF’s data into the system, to spread its benefits. “We also want to make greater use of the graphical presentation, provide the customer with a greater ability to set trigger levels, add further data sources and possibly additional reporting team requirements,” Moore concluded. “We may also move towards a service-oriented architecture [SOA] and make increasing use of our Enterprise Data Warehouse to produce federated reports.”