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. 

Customer is always right and sometimes undefined

You may remember the cartoon  about ‘IT Project Management’

This is a great similitude and fits for most IT projects.

In this post, I would like to tell you about a project of mine which is different at the beginning.

Normally customers may request features that are contradictional, those contradictions are eliminated during project design in co ordinance with customer.

One of our customers requested us a project.  It was a modification on an existing assembly line which was developed by our team.

Let’s see how our customer described the request:

“We need to see cycle times of all stations on a web page, and also want to warn the operator on exceeding the cycle time.”

That’s all? No.

“We have a limited budget and don’t want to spend too much money and don’t want operators to do extra operations for reporting cycle times.”

Yes, that’s all. I just imagined the cartoon below:

On the left, how most of the customers explain their request. And on the right, my customer.

A warning light lit in my mind, it may be risky without handshaking clearly. Something was wrong.

So I asked:

“What do you want to get by reporting cycle times?”

The answer was:

“For line balancing of course”

Well, no more information.  Just all, we had to guess what customer is really requesting.

First of all, “cycle times of all stations” was cloudily, because assembly lines don’t have distinct cycle times. Assembly lines have an average cycle time during a time interval, and that is calculated as:

Cycle Time = (Total time spent [e.g. in a shift]) / (Total number of products)

It’s the average time needed to produce one product, and it is same for all stations because all stations are cascaded to each other.

So, probably the customer was mentioning “operation times” actually.  Now it was clearer, probably customer wanted to see “how their assembly line balanced, and how efficiently working”

Even customer did not request clearly, they should need the following reports:

-          Where are bottlenecks? (To consider dividing station or sharing some operations between neighbor stations)

-          Where are the most inefficient stations? (This is different than bottleneck. Some stations may cause scrap and it is possible to detect if any station is producing more products than final stations output.

In most cases, data collecting terminals are placed on assembly lines to collect number of products and operation times, but ,in this project customer did not want to add anything. 

Customer also did not wanted the answer of question “why my assembly line stopped in a certain time interval”

We had approximately 70 stations with operators. Operators have pushbuttons to send the product to next station. We were going to take necessary information from this buttons and existing sensors.

As you see, some customers may have contradictional requests, and some have none.

We are going to start to a new series of a case study, collecting operation times from assembly line. We are going to tell about how to analyze the project and how to design.

I would be glad if you comment on this post, especially what would you like to see in the case study.