Hands On

Visualize Critical Events

Teradata Aster nPath finds patterns that could save millions of dollars.

The foundations of the Teradata® Aster Discovery Platform are a parallel database with all the features you’d expect from a SQL platform. However, its power really shines once you start using its library of SQL-MapReduce® analytic components that make complex data analysis that is difficult to code in SQL much, much easier.
A clear example of this can be seen with the Teradata Aster nPath™ module.

The nPath function allows you to perform regular pattern matching over a sequence of rows. With it, you can find sequences of rows that match a pattern you’ve specified and easily extract information from these PATTERNs using SYMBOLs that represent those rows. It uses regular expressions because they are simple, widely understood and flexible enough to express most search criteria. While most uses of regular expressions focus on matching patterns in strings of text, nPath enables matching patterns in sequences of rows.

This walk through of a use case scenario illustrates the power of Teradata Aster nPath.

Challenges and Opportunities

An operator with multiple aircraft fleets was experiencing a large number of faults on engine related components, such as bearing assemblies and shafts. It wanted to integrate its aircraft sensor, parts removal, maintenance records, tear down reports and other data for analysis to find improvements that would increase part reliability, reduce maintenance costs and improve aircraft availability. But the operator didn’t have a good starting hypothesis on the nature of the problem, and no one had ever used sensor data for this kind of analysis before. The company wanted to find out what patterns would be revealed in an analysis of the millions of aircraft sensor readings and if there would be common pathways and event patterns that would ultimately lead to more accurately predicting part failure, enhance preventative maintenance action, improve safety and lower operating costs.

To achieve these goals, the company worked with Teradata Aster on a proof of concept (POC). The POC sought to answer three questions:

  • What common event paths preceded a fault or failure for an airframe series?
  • What factors contributed to failure over time?
  • What events could be investigated first to identify and prevent failures?

A Teradata Aster Discovery Platform was implemented that allowed for the loading and storing of large data volumes and diverse data types for analysis. The analysis covered one year of information, including more than 1 terabyte (TB) of data for two aircraft models and explored two distinct scenarios: asymmetric bearing assembly wear faults and engine shaft vibration faults.

The analytics team for the POC leveraged Teradata Aster nPath and nPath Viz SQL-MapReduce analytic functions, which are pre-packaged on the discovery platform. Within a few hours, the team had narrowed down the terabyte of data into a small table with 3,210 event patterns clearly visible, and concentrated on the first twenty or so.

Answers to Part Failures

The operator lacked the ability to perform analytics on integrated sensor data and information from other channels to predict part failure over time. The POC had four goals:

  • Extract and load complex aircraft sensor data comprising hundreds of files for each aircraft using
    Teradata Aster utilities.
  • Explore and analyze data through a sequence of event paths to discover new insights.
  • Identify common patterns that led to assembly wear faults and maintenance problems.
  • Demonstrate the discovery platform’s ability to reduce very large aircraft sensor data sets into well-defined attributes, which can be connected to traditional structured data in a data warehouse to conduct reliability-centered
    maintenance analytics.

The discovery platform provided the capabilities and functionality to address the operator’s challenges and deliver on
the goals.

Analysts used the discovery platform’s nPath SQL-MapReduce function to identify the event paths that led to part failures. They used this code:

select path, count(*) as cnt 
   from (
     SELECT * from nPath (
       on (select aircraftid, description,faultid,correctedtime,airspeed from faults)
       partition by aircraftid 
       order by correctedtime 
       mode (OVERLAPPING)
       pattern ('B{3}.A') 
         description like '%BEARING_ASSY%' as A,
         description not like '%BEARING_ASSY%' as B
         accumulate(faultid of ANY(A,B)) as path
   ) t
   group by t.path 
   order by cnt desc 
   limit 100;

For the first time, the operator was able to visually understand the key events and conditions that caused the failure. It now could see the exact sequences that led to trends in which the left side of the aircraft had a considerably greater number of faults than the right side. This helped diagnose circumstances that could not have been anticipated and that execution of a natural query in a normal SQL environment would not have uncovered.

The analysis also revealed patterns that did not make sense. Because of either spurious sensor readings or recording errors, some circumstances appeared correlated even though they were in systemically isolated systems. In a standard SQL environment, this process could have required days or weeks of development, which ultimately would have led nowhere.

Once the path to maintenance faults and wear was clearly established, the team had the detailed insights needed to explore maintenance and flight records, and conduct root cause analysis to address the problem. In a similar inquiry on a different fleet, the operator noticed parametric engine faults at a shaft location. Since it did not understand the causes for the fault reports, the analytics team looked at event paths that preceded it and identified common situations that occurred in the flight hours leading up to the report. The result was a discovery that at some point, excessive vibration had occurred, and then subsequently a fault was recorded.

The query leveraged the nPath function to invoke pathing capabilities. Analysts then used this output to select a time period, in this case 900 seconds, to look for parametric fault indication after a vibration. And, they were able to identify events that exceeded operating limits after a vibration event. The Teradata Aster nPath Viz sankey visually displays these event sequences. (See figure.)

By filtering the data, the operator was able to understand how engine parametric data surpassed operational parameters after excessive vibration. This would allow the company to perform cause and effect analysis, eventually improving safety by correcting the cause of vibration-induced malfunctions, reducing maintenance costs and minimizing inventory requirements.

Gaining Insights With SQL

The POC revealed the reliability analytics that could be used to capture millions of dollars in cost savings by creating preventive practices or preemptive removals. The operator now is in a position to productionalize these kinds of reliability analytics based on sensor data by linking the discovery analytics enabled by Teradata Aster to its Teradata data warehouse. This would be accomplished using the Teradata Unified Data Architecture™ to:

  • Capture and store raw, multi-structured aircraft sensor data in Apache™ Hadoop®.
  • Combine both structured and multi-structured data to conduct discovery analytics and identify issues that could lead to part failure or safety issues using the Teradata Aster Discovery Platform.
  • Operationalize and validate the predictive analytics using the Teradata Database.

One major benefit for this operator is its ability to leverage standard SQL skills for the analytical process. It doesn’t need expensive Java™ programmers or Java-based BI tools to convert data between disparate systems. The Teradata solution allows analysts to use SQL to deliver valuable insights on any data sets.

Identifying Excessive Vibration

This query was used to provide an understanding of the parametric events that were experienced in the engine following excessive vibration:

create table flev_2_4_2
distribute by replication
as select * from nPath (
    on (select * from aircraft_events_v2_flat_partial) 
    as ttt
    PARTITION by 1
    ORDER BY date_time
          eventcode  = 'VIBRATION'
         )  as S,
          eventcode  = 'VIBRATION' and
          extract(epoch from date_time) -
          extract(epoch from LAG(date_time,1,
          '2012-04-05 04:06:02.678-07')) < 900
         )  as X,
          eventcode != 'VIBRATION' and
          extract(epoch from date_time) -
          extract(epoch from LAG(date_time,1,
          '2012-04-05 04:06:02.678-07')) < 900
         )  as OTHER
    PATTERN ('S.(X|OTHER){4}')
    RESULT (

Jason Janetzke is the senior product marketing manager for Teradata Aster.
Peeter Kivestu is the director of travel industry solutions and marketing at Teradata.

Your Comment:
Your Rating:

Fuzzy Logix