Operation Times Reporting In an Assembly Line - DB Application

Operation Times Reporting In an Assembly Line  - DB Application


In our previous post, we have studied on data collection. In this post, we are going to study on database design.

Let's remember, we were going to deal with more than 30 million records annually in a database, and we were seeking a way to handle it without throttling the database server.

Let's remember what our customer needed:

Operation times distribution as an histogram : Calculating an histogram may be a little bit difficult. We should prefer to display reports which are easily derived with SQL sentences. And displaying 70 stations histogram in a single page may not help to understand bottlenecks.

The box plot graphics  seemed the best alternative for histogram. It includes basic information from histogram:

and can present all stations graph in a single page:

Pictures are taken from wiki pages, unfortunately I can't publish samples from customers reports.


Idle time to find out bottle necks: For idle time, less is better. So, what about displaying them with a colourful background? Report data is simple, will just include two rows, 'Station name' for first row and average idle time for the second row. It would be nice to change proportionally the background of the cell for idle time according to idle time. If smaller values are better, we can display minimum idle time in totally green, and maximum idle time in totally red. And the others may be proportionally colourized in a gradient:

Station 3 is totally red, so customer can easily understand the most unbalanced station is the third one and can give more operations to station 3.

Also a box plot report should be fine for idle times to see more details.

Efficiency of stations : There are loops in the assembly line and those loops are used for fine tuning of some products if necessary. Because of those fine tuning operations, some products visits same stations more than once and this decreases efficiency of course. 

So, customer wanted an efficiency calculation of station x as follows:

Efficiency of x =  100 x (Total products produced by last station / products produced by station x)

A gradient colourized report fits for this need.

Transfer times:  Customer wants to see how mechanical movements between stations limit the productivity. Box plot fits better. 

Now, lets see the structure that we can get by PLC:

Nearly, 2 or 3 records in a second. 

We decided to store them in database in two steps. At first step, we appended all records in to a text file and gave a name ending with date and hour, e.g.

LogFile08_11_15_13.dat

which includes all detailed information between 13:00:00 and 13:59:59

Next file name should be LogFile08_11_15_14.dat.

Then we developed an application for summarising the detailed data file for database, and inserting them into database. It was a scheduled task to run for every hour, summarising the data, inserting the summary records into database and finally copying the detailed data file into a zipped archive file.

And the summary included following fields:

• Station number
  
• Date
  
• Hour
  
• Number of products
  
• Minimum of T1
  
• Maximum of T1
  
• Q1 of T1
  
• Q3 of T1
  
• Median of T1
  
• Average of T1
  
• Standard deviation of T1
  
• And same calculations same as T1 for T2 and T3 time intervals.
  

Our database transaction seriously decreased from approximately 8500 records to 70 records hourly.

And overall cost of the project was not too much:

• Half a day for PLC software modification
  
• Quarter of a day for PC software development
  
• Quarter of a day for PC summarising task application
  
• One day for report web pages
  

However, customer was angry with us because the project was too easy and cheap to be as a default feature of the assembly line. What can I say, customer is always right. I am planning to add this system as a default feature if someone orders us an assembly line again.