Analytics based on sensor data is of increasing interest for a variety of industries. In the context of industrial manufacturing, the goal is very often to reduce downtime through improved maintenance and to increase the output of high quality goods. However, data quality issues can lead to wrong decisions being made even when the analytics algorithm applied is correct. In this case report, an approach to data quality analysis for time series data is presented. The focus of the paper is on the best way to select and present metrics and the pitfalls which were encountered during the example project.
Introduction 3
Data Quality Problems and Their Origins There is a large body of works on data quality in general (see [1], [2], [3], and [4]). The most popular approach to data quality is to use a large set of metrics which are grouped into data quality dimensions. Not all dimensions and metrics are applicable to all data sets and analytics problems.
While some data quality problems are quite obvious choices for metrics (e.g. missing data), other metrics require some understanding of the problem domain. In the literature, there are certain problems that are associated with time series data (see [5],[6],[7],[8],[9],[10],[11],[12],[13], and [14] as well as the summary shown in Figure 1). It should be noted that not all of these problems can be detected easily and there are problems which cannot be detected and/or corrected at all.
subzero temperatures in a hot process). This can be applied to individual values but also to averages, minimums and maximums
(Recognize through gradients and max deviations)
trend etc.
variations. (This might be hidden bebind values such as 1000 or due X to scale of units (e.g. m3) Signal Noise iSnmaaclclucrhaatengmeesawsuhriechmaernetsn.o(tRiencothgenipzerowceitshs lbouwt rpeassusltfiflrtoemr) X
is only a problem if we "discover" those connections later.
problems (e.g. how many values to put into one day?)
X X X X X X
X X X X
X X
X X X X X X X X X X X
X
X X X X
X l. a t e r e u a b u H l. a t llyeenow lltrsaeeoo H P l a t e s o t s a B l tae iLu aanhm anduo
R G X X X X
X X s a x i SECUNO il.tssEaeenw irFSaaeeendd illttzaeCdo
X X X
X (X) X
(X) X X X X
(X) X X X
X
X X X
X (X) X (X) (X) (X) X X
X X
X
One would assume that fixing data quality issues automatically is a good approach, however, not all “fixes” really improve data quality. For example, a system might interpolate missing data to close the gaps. Depending on the analytics application and algorithm, the corrected values might avoid computational problems or might be interpreted as “interesting situations” which in turn misleads the algorithm.
Finally, when data from different sources is combined, issues such as different sampling resolutions can introduce artifacts into the data. If data has different formats, there might be conversion errors (e.g. centimeters vs. inches). 4
Measuring Data Quality The purpose of my data quality library is to understand the data quality problems present in the data and then take measures to fix/circumvent the problems. In the worst case, data quality is so poor that using the results of the data analysis is not recommended. One of the aspects of data analytics which I consider highly problematic is the perceived quality of the results. Often, once data has been corrected to such an extent that is can be used with the analytics algorithm, the results will look “clean” and accurate. However, if the data contains a lot of interpolated and corrected data points, there is a high risk that the analysis results will strongly reflect the assumptions made and thus might no longer represent reality.
The (still ongoing) development of the library described in this paper is a highly iterative process. Over time, I have identified the following design goals which I consider necessary to run an effective data quality analysis: • The large amount of information provided by data quality analyses has to be reduced to provide an understanding of general quality, i.e. is a dataset ready to use, worthwhile improving, or not suitable? Even a very basic analysis can result in more than 100 metrics and just providing a list is too confusing to derive immediate and effective action. • On the other hand, individual metrics are needed to understand the nature of the data quality problems. A highlevel overview indicates the areas where no attention is required but without a drill-down the real problems cannot be understood. • Information has to be actionable. This means that there needs to be concrete information about problems, their exact location, and how to fix them. • Metrics need to be configurable for individual data sets and analysis objectives. There is a lot of similarity in time series but there are different formats, different relevancies of fields etc.
These design goals are slightly at odds as they require increasing levels of detail. My solution was to create a hierarchy of metrics. (This is based on our work with non-time series as detailed in [15].) In the remainder of this section, I will describe the visualization concept and the architecture of the R library. 5
High-Level Visualization In order to achieve both a good overview and the ability to drill down to the level of individual problems, a hierarchy of metrics was created. At the lowest level, individual metrics are calculated which detect the data quality problems described above and measures the overall quality as a value ranging from 0% (bad) to 100% (perfect). Due to the fact that each metric needs to describe a very precise and actionable problem, the number of metrics can easily be in the hundreds. For example, a check whether there are empty fields in the data will lead to 10 metrics for a measurement with 9 values and a timestamp. A plausibility range will add another 10 metrics and so on.
When drilling down to the level of data quality dimensions, one gets a better understanding where the problems lie (see Figure 3). In this case, there seems to be one major problem in the category Free-of-Error and multiple problems with Completeness. There are few or no issues in the categories Plausibility and Richness-of-Information, which can thus be ignored. This level of display allows users to quickly drill down on the major problems. In this case, it makes little sense to look at any of the plausibility metrics for example.
The next level of drill-down leads to individual metrics. In the example case, there are two areas of interest. Figure 4 shows 9 of the completeness metrics. (Some identifying information has been removed for the sake of anonymization.) As can be seen, one of the major completeness problems is that data is not only missing but also that missing values are highly dependent on each other. This suggests that there might be a common cause for missing values which needs to be taken into account during the correction of the data.
Furthermore, in time series, it is also interesting to see where the problems occur. For example, if all problems are concentrated in the beginning of the data, it might be an option cut off the initial part of the data and just use the rest for training, testing and similar activities1. Figure 5 shows a heatmap that illustrates the “Empty Fields Independent” metrics for all columns of the data set. The columns of the heatmap correspond to the columns of the table with data. The rows are aggregations of multiple rows in the table. So, if we go down a column we can see how the data quality of a certain value changes over time. The darker the color of the heatmap, the lower the data quality.
In the example, we can see that the missing data for pressure (“barometer”) and humidity are not very independent. Also, the problems are spread throughout the time series so cutting off the beginning or end is not an option. 1 Of course, if there is some causal relationship which involves both missing data and equipment deterioration, this would be a bad idea. 6
Additional Views of the Data
If the timestamps are not equidistant, the heatmap in Figure 5 can draw a misleading picture. Let us assume for the sake of example, that a system produces 10 entries over the mission time. At first, it generated one entry per minute but the last 5 entries are at a rare of one entry per hour. In this case, the first half of the heatmap does not represent 50% of the mission time but only 10%. Thus, it is also of interest to understand the development over time, which can be vastly different from the “per entry” view.
Metric Value
Metric Value Metrics Table
Heat Map
Heat Map Metrics Boxplots
6 metrics.boxplots
Meta Data
Data Data as Text Since data sources and analysis tasks differ greatly, the assessment tool is implemented as a library that can be used to quickly implement data quality analysis tools for different use cases. The library is implemented in the functional language R which is a quite common tool for analytics. One goal of the package was to minimize the code needed to apply the metrics to an individual case. A typical application can be realized with the following steps (see Figure 6): 1. The data is loaded as text and in the correct format to allow different types of analysis. Also, metadata is needed to check for value limits, identify time stamps etc. 2. The required metrics are added to a list called the metrics corpus. The metrics are based on a series of rules which are provided in the library. 3. Via the apply.metrics.to.problem.array function, all metrics selected are automatically applied to the data and all problems found are stored in the problem array. The 3-dimensional problem array contains Boolean values to indicate whether a certain column in a certain row violates the rule for a certain metric or not. 4. The problem array can be used to automatically calculate all metrics which are stored in the metrics table. 5. The problem array can also be used to automatically create the heat maps indicating problematic areas in the data.
The list of metrics can be used to automatically create the hierarchy of box plots and other core graphs.
Overall, one or two pages of code can implement a data quality assessment which produces a series of PNG bitmap files to include in presentations. Examples of these files can be seen in the figures above. 8
Data Quality – Lessons Learned The system described above is the result of multiple iterations and rounds of feedback. While some of the reasoning is already explained in the text above, I would like to briefly summarize the key findings of this project so far: 8.1
Data quality does not equal data quality There are many generic data quality tools available on the market. However, an internal study has shown that these are mostly frameworks which need to be filled with rules. Domain-specific knowledge can greatly improve the data quality analysis.
As an illustrative example, consider vibration monitoring (see Figure 8). One has to understand the nature of vibration monitoring to know that if vibration is measured as 3 vectors of acceleration, the two horizontal vectors will show similar values, while the vertical vector will be affected by gravity and thus differ. Using this knowledge, it is possible to check the plausibility of two of the measurements and to verify the assumptions made about the sensor alignment. I.e., we might expect vectors y and x to be similar (see lower part of the figure). However, if x and z are similar, the sensors are mounted with a different orientation, i.e. our assumption about the direction of gravity is wrong.
Furthermore, some of the metrics with a statistics focus (e.g. sample size) or technical focus (sampling rate), are not implemented in generic data quality tools. While the tools might provide the capability to define such rules, we found no support in the sense that they would suggest such rules. To avoid misunderstandings, commercial data quality tools are not poor products but their strengths (e.g. volume and speed) were not critical for us and did not justify the price tag. The importance of individual data fields depends on the type of analytics which is to be performed later. A possible outcome of training a decision tree might be that “barometer” is a very important value and “humidity” is mostly irrelevant. Fixing data quality issues is time-consuming and thus focus should be on those fields where actual value is being generated. 8.3
Impact matters It is easy to come up with dozens of metrics. A naïve approach would be to sort all those metrics by value which quickly highlights the areas where the quality is really bad. However, it is important that the nice-to-have quality features can be separated from those of critical importance. Otherwise, mostly irrelevant poor performers will cloak essential columns which perform better but not good. Thus, the design of a good GUI is critical.
For example, let us assume a case where a large number of richness of information metrics is implemented. Information is rich if it contains enough detail – a statement “hot” has less richness than a statement “around 50°C” which has less richness than 49.3°C. If the richness of information is poor, this does not act as a showstopper but many metrics of this type might hide more important ones if just a list of metrics is provided. Another example is metrics which are related. Figure 4 shows that there is a strong relationship between empty fields. These metrics as well of those metrics looking at whether fields are empty or not, cover different nuances of the same problem. In a raw sorted list, such clusters of metrics will clutter up the top 10 if they are poor and might hide other important aspects. 8.4
Actionable information During the project, one issue that came up at multiple stages was that of a “reporting trap”. Interesting information was presented in graphs but when it came to the question how to fix these problems, I found that this particular information was missing or difficult to extract. Thus, besides all high-level visualization there has to be some machine-readable list which describes all problems and can thus be used to select and change subsets of the data manually or (semi-)automatically.
For example, consider completeness metrics which track the percentage of empty fields in a particular column. My initial assumption was this metric would support our decision whether to use a certain data set or not. However, reality is a bit more complex than that. For most analyses, it is not really critical to have all the values. For example, to determine a trend, a few missing values in between are not a problem. However, larger stretches without data result in unusable parts. Thus, missing 50% of the values is a problem if this means two large empty stretches. Depending on the analytics algorithm, it might be less of a problem if every other value is missing. Clearly, the metric does not give enough information for the next action – should I use this data set or a part of it or nothing at all? Which parts should I remove and which ones should I use? Only the addition of heatmaps such as the one in Figure 5 helped me to derive concrete actions from the data quality assessment. 8.5
The devil is in the details Coming up with a good data quality analysis is no easy task. A major problem are tiny details that can cause information loss. One very interesting case was time series data where I made an assumption about time zone, only to run into problems with daylight savings time. (There were date-time combinations which do not exist and thus translated as invalid.) Also, it is easy to define the algorithms for some checks (temperature must be within a realistic range) but difficult to come up with the concrete values, even in expert interviews. (The melting point of copper is a correct but not necessarily useful upper bound for the temperature of a wire, for example.) 8.6
Some problems slip by the metrics Even amongst those problems detectable by the metrics, there are issues which can slip by the analyst. For example, I implemented a check to discover ordering problems within the timestamps. A simple rule is to check whether timestamp n chronologically occurs after n-1. However, with this text, the following constellation (Figure 9) registers as a minimal problem because 99% of the timestamps are in correct order when compared to their neighbors. However, as the graph shows, different sections of the data are completely mixed up. In this case, some more fine-tuning is required to let the algorithm detect what a human can easily spot.
Conclusions This case study describes the development of a data quality analysis library with a focus on design issues which go beyond a description of individual metrics. In particular it addresses the aspect of quickly understanding the relevant problems and deriving concrete actions. The results shown here are but an intermediate step and hopefully the basis for a comprehensive library to be used for analytics projects in the company.
Acknowledgements
Oceanographic
Available online