China's 10-story 80,000 ton press, one of the largest known in the world
We had just released two new APEX apps when our director called a meeting to decide what from the long list of application requests we should pick next. I had written on the whiteboard our high level list of projects - a hodgepodge of napkin ideas and individual wish lists for how we could better manufacture metal products. Our director asked the group what they thought should come next. Most heads turned silently towards Phil, our forging specialist who was shifting uncomfortably in his seat.
I had heard a few things. I knew there was a planned outage upcoming at our forging facility with a 12 million dollar price tag. I also knew the equipment in question - the press - was critical to our manufacturing process. It had been running overtime for weeks in order to build up a back inventory for the planned outage. I knew there was a tight schedule in which the press was expected to be back in operating condition, and any delay in hitting our date would be detrimental to the company.
Phil let out a sigh, and began to speak about the qualification process he was in the midst of. As part of the upgrade we were required to develop a benchmark to show a before & after of press operation for our customer's approval. Because our critical process data happened in under a second, this meant sampling data at a high rate (30 points per second). Due to our forging process being complex and multi-stepped, this meant thousands of samples were required both before & after the upgrade. This all totaled to around 10 million individual data points which needed to be collected & cross-analyzed.
Our company had done similar benchmarking for small upgrades in the past that had taken months to correlate. We needed the analysis completed in days this time. Due to the scope of the upgrade being a major overhaul, a large amount of work would be placed on hold until the data analysis was completed and qualification approved. An extended qualification process would mean money lost by the company, temporary layoffs across the board, and a lot of upset folks all around. If we could reduce the time needed to crunch the data, we could accelerate the entire process and save everyone a lot of pain and suffering.
Also different this time - one of our customers was requesting a more advanced statistical analysis of the data set. Six Sigma has gained popularity in the manufacturing world, and customers are increasingly looking for control charts, histograms, and other visual representations of data in relation to some established limits. As we had already developed this charting capability internally, the hope was we could leverage Oracle, PL/SQL & APEX to store, analyze & display forging data in an easy to use dashboard.
"We need it ready in three months. Is it possible?"
Following the unwritten APEX developer's code of never turning down a project, no matter how crazy it may sound, I nodded my agreement. Later that afternoon I sat down with my co-op, a fresh college graduate who had been hired nine months ago to learn Oracle/APEX development exclusively, and I started to explain the project. His eyes grew wider with each new detail, and when I told him the timeline his face went white. Did I really believe we could meet that deadline, he asked? To be honest I wasn't sure myself, but have found it best to lead through bold confidence and blind determination, so I sat up tall in my chair and channeled my best Obama: "Yes, yes we can!"
Our first chunks of data arrived that week. As we had no existing data collection system in place, we relied on an analog module to capture raw signals directly off the press and store these in CSV files, which were then manually imported to Oracle tables via SQL Developer. This was all raw data with only a timestamp, and while our forging records gave us rough start & end timestamps, unfortunately these times were unreliable and/or inconsistent.
Enter APEX! We had anticipated using APEX for just the finished dashboard, but we found by plotting the raw data to line charts we could easily spot start & end points over time. With a few small modifications we were able to create our own tool for viewing & labelling raw data!
The process was laborious but eventually we were able to group our data points and tag each with forging process & unique material IDs. Our next challenge was in labeling individual "strokes". Each time the press applied force we would see a spike in tonnage. Our customers were interested in the slope and peak of these strokes. However there were tens of thousands of individual strokes, which made labelling these individually an impossible task.
I've always been amazed at how effortlessly the Oracle database handles analyzing large data sets. A well-written query can crunch through millions of points in a matter of seconds. It took a bit of tinkering but eventually we had a 70-line SQL query that was capable of identifying the start & end of each stroke with > 99% accuracy. We wrapped this in a PL/SQL cursor and with a single function call that was capable of crunching through and updating 10 million+ rows in a matter of minutes.
Eventually we had our APEX labeling tools polished enough where we felt comfortable, and with a whole month to spare! The color was starting to return to my co-ops face, but this was no time to relax - we had a dashboard to create! We could easily draw a multi-line series chart with the standard APEX install, but found to join this with a spline area chart would require the advanced commercial license for AnyChart. We were able to quickly obtain a license via the Anychart website, however documentation was somewhat limited, and my DBAs were very unhappy about having to replace core APEX installation files with new ones.
Dramatization - no monitors were harmed during Anychart install
Once the upgrade was complete we realized there was no declarative support for the new chart types. While they were all now technically available to use in APEX, the only way to get these to display was to pick a "vanilla" AnyChart type that was a close match, switch to XML mode and adjust the syntax as needed. We were already accustomed to working in XML mode so it wasn't a huge hurdle, but having declarative support for AnyChart upgrades would have made the process much smoother.
After some messing with the XML and a bit of web help we were able to generate a chart like the below using some sample "after" data. The blue spline area region represents our "before" benchmark, with the lines representing individual strokes post-upgrade.
Pretty neat! And with dynamic PL/SQL queries we were able to adjust the chart data series on the fly to match whatever filters were selected on the current page. For our more advanced statistics we plotted the same data on a normalized histogram (column chart) with CpK, CpU and CpL values calculated at chart rendering
We finished just in time for the press to come back online, and spent the better part of the next week barricaded in a conference room using our custom created APEX tools to label raw data as it became available. Our dashboard was live within 24 hours of the first qualification material forging, and allowed our team insight into the operating condition of the press compared to our benchmark. The dashboard was eventually handed off to Phil, who was able to turnaround a customer approval that was expected to take months within just two weeks.
This is just one example of how we're applying Oracle and APEX to assist with the manufacturing process. Next week I'll dive into our melting furnaces and how we've leveraged APEX to keep a real-time dashboard of furnace health.
