Databases are the refrigerators of the industrial world. We all know they’re important and that we have to have one to store something we can’t live without, and we all understand basically how they work. Unlike a refrigerator, however, the more you know about databases, the more they can do for you.
Most operators and engineers typically use production data in their databases as intended by the software applications creating the data. The problem is that many engineers and operators simply aren’t aware of most of the data stored in these databases; plus they often don’t fully understand that data.
>> SQL or NoSQL: A new technology debate is breaking out of the clustered ranks of IT developers and into the user realm. Click here for more info.
Travis Cox, director of training and support services at Inductive Automation (www.inductiveautomation.com) (Sacramento, Calif.) says that most engineers don’t have screens built to pull in the information they really need. “We see people create raw data dumps rather than look at trends like averages over time,” he says.
In particular, time-series data (such as temperature readings over time) are collected in many facilities but not efficiently used, according to Chirayu Shah, product manager for FactoryTalk historian, Rockwell Automation (www.rockwellautomation.com) (Milwaukee, Wis.). Shah says the reasons for this include:
· Not knowing data exists or where it is stored;
· Not performing regular analysis of data;
· Failure to optimize data storage by storing data too frequently or keeping too much irrelevant data;
· Too much data also creates additional storage requirements, which equals higher costs;
· As data stores grow, the efficient retrieval of data becomes more challenging.
Database essentials
Beyond the database’s role as the storage house for collected production data, what should engineers know about manufacturing databases in terms of how they can be used to affect operations?
Roger Herrscher, P.E., senior electrical engineer at Opto 22 (www.opto22.com) (Temecula, Calif.), notes that manufacturing databases can be used to keep track of:
· Raw material inventory;
· Finished goods inventory;
· Vendor lot numbers of raw material components (a critical component of pharmaceutical or medical device production);
· Production process times or production labor times; and
· Production yields and failures.
Overall equipment effectiveness (OEE) is a primary application for which manufacturers need to leverage databases, says David McCarthy, president and CEO of TriCore (www.tricore.com) (Racine, Wis.). “OEE analysis, which includes downtime analysis and statistical process control (SPC), provides information that can be directly used for performance improvements,” he says. “SPC analysis can expose process variability, which generally results in product waste. Reducing such variability allows for tighter control and can be used to reduce raw material overuse, reduce scrap and, in fluid products, reduce package and container overfills.”
>> Manufacturing Databases: Click here for a brief primer to help better understand the database terms most often encountered.
Access to real-time and historical data from your production equipment and processes provides the basis for any sort of process improvement and root cause analysis projects, says Shah. “The availability of these data sets are an essential ingredient to reducing energy consumption, improving production, cutting downtime and meeting customer demands with on-time delivery and a high degree of quality. Engineers can use information collected on what they have already done to drive where they are going.”
The best part about leveraging production data for operational improvement is that engineers need to know very little about the actual database and its structure, says Shah. They just need to “care about the data itself and what it can tell them.”
IT’s role
To turn your databases into tools for process improvement, the first step is to work more closely with your IT department. This approach may be anathema to many plant floor operators and engineers but, chances are, the IT department is more knowledgeable about databases and what they can do.
The IT department can help increase your understanding of databases by designing the database and writing the SQL queries in a way that engineers need them to get the data they want. They can also help ensure your data gets backed up on a regular basis.
“It is amazing what you can find once you start adding more data to a database,” says Cox. “First, you start to realize that many things you do manually should be done electronically. Once you understand the data going into the database, you often start seeing trends more easily.”
IT can also help integrate new database methods into the existing database structure, or help develop new methods to move, migrate, replicate and utilize more appropriate technology based on the needs of the project, adds Robert Leonard, IT manager for Opto 22.
While IT’s help is essential in connecting engineers more closely with their databases, clear lines of responsibility should be drawn. For example, the engineering department should “own” the plant floor application and its associated data while IT “owns” the assets and their administration, advises Shah. “Engineering needs to embrace this role of IT instead of trying to build and support all their systems themselves,” he says. “Likewise, IT needs to help facilitate their role and function, and make themselves easier to interact with as a group.”
Database projects
If you want to begin learning more about your manufacturing databases, the best way to do that is to start interacting with them more directly. Following are a couple projects that you can do easily to start down the path of greater database understanding:
Use ad hoc queries to identify the 10 most common alarms in your system over a given period of time, advises TriCore’s McCarthy. “Investigate the individual frequency of each alarm and then compare this behavior over short-, medium- and long-term time frames. From this analysis, a good idea of common issues that the plant faces can be determined and potential corrective actions put in place to improve performance and reduce downtime of the system,” he says.
>> How to Analyze Data from Multiple Databases: Click here to read how manufacturers with multiple databases in place throughout their operations perform data analysis.
Overall Equipment Effectiveness (OEE) can be easily assessed with database information you likely already have. At it’s most basic level, OEE is an equation: Availability x Quality x Performance. So you just have to examine “how much you wanted to make versus how much you made (Performance), then determine how much good product was made (Quality), and finally see how available your equipment actually was versus what you thought (Availability),” says Shah. With the help of IT, “engineers can use these database tool sets to slice and dice data to focus on specific improvements and reduction of cost.”
Leaders relevant to this article: