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.

Operation Times Reporting In an Assembly Line

In our previous post, I have told about a customers request for reporting operation times of stations in their assembly line.

They were going to use it for assembly line balancing

Many readers may think that 'well, assembly line balancing should be done before establishing the assembly line', you are right.

But our customers assembly line was producing mixed products, I mean there could be several types of product at the same time. In the other hand they had too many types of product, and new types of products were being developed by their R&D department.

If you want to see the performance of stations in your assembly line, what would you like to have in your reports?

• I would like to see the operation times distribution as an histogram.
• I would like to see the idle time to find out bottle necks.
• I would like to see the efficiency of stations.
• I would like to see how mechanical movements limit my productivity

We had a budget limited with only software change, customer did not wanted to add any extra devices or operations on assembly line.


Let's consider our scenario with a single station:

• Station is free.
• New product comes from previous station
• Operator starts working on product.
• Operator pushes the 'operation complete' button to send the product.
• Product waits if the next station is busy.
• Product moves to the next station when the next station is free.
• Station becomes free.

What we have as hardware:

• Product complete button
• Product presence sensor
• A buzzer to warn the operator on exceeding the cycle time

 

Customer has approximately 70 stations, designed system should be flexible to add new stations.

We had to design the system for both convenient for PC and PLC software development.

First we discussed about storing the measured operation times in PLC, then transfer to PC. Not so convenient. There were more than 70 stations, meaning 70 more timers or counters in PLC.  For our project it was impossible without PLC upgrade.

And not only operation times of course, we had to think about transfer times and idle times also. It was impossible with customers existing PLC system.

And making necessary software changes in PLC should take at least one week.

So we decided to copy only hardware inputs from PLC to PC. Solving the problem in PC was more convenient.

In PLC, we made two flags:

Product presence flag, which is directly connected to product presence sensor, set to “1” if station is occupied with a product and reset to “0” when station is free.
 

Operation complete flag, which is set to “1” when operator pushes to the operation complete button and resets to “0” when product leaves the station.

Let's draw a timing diagram, this is not an handshake, just a timing diagram for two flags:

 

T1,T2 and T3 shows only one products timing. TA,TB and TC is the next products timing.
Normally  T1, T2 and T3 corresponds with TA, TB and TC respectively.

T1 is the time interval that the station is free, however a new product may be on the way.
T2 is exactly the operation time which spend for assembly operations.
T3 is the idle time, where all assembly operations are completed and product is waiting for the next station to become free.

T2 and T3 were the exactly time intervals we would like to see. But, what about transfer time? We also needed to report transfer times.

For an exact transfer time calculation, we have to measure the time between our stations product presence flag and next stations product presence flags rising edge.

However, it is just a little bit difficult because to do this, we also have to consider order of stations to know which station follows which. It was difficult to manage because assembly line was not straight, there were branches and loops.

So we decided to accept that TA as a transfer time.

Why not T1?  Also T1 was possible, but we considered that PC is an unstable device, may be frozen or application may hang. So PC software may be restarted anywhere in the cycle and measure time intervals as shorter than it is.  We wanted PC to record clear measurements only.

In PLC software, we had represented the flags as bits in a byte, so a single byte stored 4 stations information.

For 70 stations, 18 bytes were enough. PC was going to read 18 bytes from PLC and was going to write 9 bytes for buzzer alarm.

In PC, we have considered all stations as an array, just wrote few lines of code for one station and applied to all stations in a For..Next loop.

Overall code except 'Read all inputs' and 'write all inputs' took less than 100 lines of codes.

Data was collected successfully.

For our next post, I am planning to tell you about the database structure and web page reporting of this project. We had some limitations in DB.

I think may people guessed our problem, there were 70 stations meaning that each product will produce at least (at least because there were some loops in assembly line) 70 records in database.

One shift was producing approximately 600 products.

There were three shifts daily.

Working 5 days in a week.

Working nearly 50 weeks in a year.

So we were expecting:

70 x 600 x 3 x 5 x 50 = 31 500 000

more than 30 million records.

And customer was planning to apply the project to their second assembly line if project not fails.

More than 60 million records. So huge that consumes too much CPU in database server and possibly may slow down other applications.

For the next post, we are going to focus on database design and some statistics.