Hello, my name is Mauro Gerber. I work as a data scientist for Huber and Suhner in Switzerland. I would like to introduce you to the problem we had regarding SPC and I would like to have you on the right. I want to talk about SPC scripting and why it's important to write the information back into a database. W hat we have is an optical measurement system that measures dimensions on parts that we store in a database. The goal was now to get the data back out of it and to statistical process control on it. Now what happened was that over time some variables can shift and the worst case would be that we get the out of spec and then we have to do some measurements to get it back in. It would be preferable if we can do that beforehand.
The idea of SPC is like telling a dog to stay where it is. W e achieve this by redefine a stable face and say if the process moves out of that window, it gives a notification before it gets an out of spec and we can take measurements to get back into stable again. O ne way to achieve this is by the process screen platform. We can sort about this control chart alarms which gives us how many of the variables violate the test one. Test one is simply out of control limits. As you can see, the most alarms I get from the most stable process. T his is a bit paradox. As you can see here, I have a very stable process and the SPC limits that gets calculated automatically gives me very much false positives. ` I shouldn't react on this because it's just within the variation.
The second problem with it is if I have an order and analyze the samples, it calculates the control limits for me. Like in this examples, it says it's all good. I f I later on analyze a second order, the calculation of the limits automatically switches to the new process variations. I t makes the window bigger and again says, hey, everything is okay until we see of course that it moved and the variation got bigger. W hat I did making an SPC script that deals with the measurement and loads and stores SPC limit in the database so we can have a proper working SPC.
I would like to switch to demonstration of how this looks in our database. W hat we have is a script that imports the data. What does is it goes into the database and search the whole product order we have stored for this demo. I made a special data set and it automatically contains this program. For us is like article or part. W e can select like PO1 that the first one we did in our example, I can say okay, I want to have a look at that data and at the back and this is the final result. A s you can see here, after [inaudible 00:04:23] and SPC. In our example, this is what happened. I have a stable process, everything is good. Look at all processes. This is where the error came from.
Again I can show you the SPC. Y ou see here, in the beginning the process was okay. Then there was a phase from where the variation got bigger and then it got back again. T his X1 got a problem. The problem was X2. As you can see here, I got some parts out of spec. This, I would like to change now.
A s we discussed earlier, what we need to do is go to the first PO and set this table one I want to go back to and I make now run the script that goes over the table that is active and extract every line with spec limit. I see here, this is the spec limits and you can see here empty control limits. W hat I can do now is two ways. I can either activate this PC here, have a look at the data and say zoom in a bit and say manually on X2 lower control may need 2.98 and 3.02 and down. Tell the program hey, please update the limits for me and I go back to-- No this was wrong , sorry.
2.97 and 3.01, update. T he limits I just set are now in the control limits which I can check in X2 it added the control limits for myself. The problem is now if I close the table and read download the data, these control limits are lost. W hat I can do now is save it, close all, reload the data and the script now looks if there are control limits present for X2 and I did none for X1 and if I go here now SPC X2, the control limits are now set as desired. If I have a lot of control limits present in my data set, like 20 or so, it could be quite difficult to set the limit manually for everyone. This is where I made the script that you can actually select the desired variables you want set default.
A s I showed you earlier, if I would set it to one, it would copy the automatic calculated limits from the system which can be too tight. T his case I say okay, I want the margin twice as big. Now it runs through every row and sets automatic the limit. I save the limits to the database and if I run the limits again, you can see it's centered and it has a nice window around them quite easily. I can manage limits for a lot of variables.
If I go now to the problem we had. I now select all of them, run the script again and run it again. Now it takes over the limits I set earlier. A s you can see here, I would get warnings earlier on that something is wrong and then we could have prevented this from happening. A s you see here with some countermeasures and now we are back again into the stable phase.
What I use in the script is I search with spec limits to identify which one are variables to work with. This can also solve problems with platforms who depend on spec limits. I can filter them and only make limits or control limits for SPC or spec limits for the process capability platform. I don't get an error message that control limits are missing.
What is important is of course safety. Habit of jump is that the source is in the file. W hatever password or database connection you use gets stored into the table. This of course, can reveal server name or even username and password. To get around this there's a preference ODBC Hide Connection String. What you also can do is in the script itself, encrypt the code with password and username. You see in here, so people are unable to read it.
When I write into the database, I use the code create database connection. I set the reference and then there is SQL statement I put together from insert into which database name it is. Then what I write that's from the list I generate program names, empathize and the control limits. Th en execute these SQL statements from the connection string and the SQL string. What's important is that when the log is longer so I get an error message from the SQL, it beeps me and put it in the log. W hen something is working wrong, I can check it like credentials are wrong or the connection could build up or whatever it's possible.
Then I write the control limits into the data table. I check for the name of the article and if there are SPC limits present, if they are, I update the column properties with the control limits which then get automatically displayed in the SPC and the program handles accordingly.
Second use for writing something back is if we have a measurement we make a test with it like environmental test or endurance test and make a second measurement. I have the same part with two measurements or I have a false measurement like something went wrong. I retake the measurements and the second measurement is actually what counts. T his I can navigate by a small script that says update DB. I can give me a dialogue if I want to set the measurements inactive or update it. If I want to update it, I can type which label I want and what's the name of it. It works similar as the function name selection in column. What it does is for each measurement I get a unique ID, even if the part itself has a serial number. I have the serial number measured twice after zero hour, after 100 hours from test option one. I t got different measurement IDs. This is how I differentiate between the measurements.
For the inactive part, that's simply that in the SQL statement I can say if the measurement were inactive disregarded so these faulty measurements don't show up. K ey points is careful that you may not give out some sensitive information like password usernames. You can hide certain parts in the script with encryption the code is JSL Encrypted and then the encrypted code, is in here as a text. U se pref ODBC Hide Connection String. A nother way is use windows authentication to avoid credentials altogether. This also can help that you can use specific columns that are writable like the label one, label two column and to avoid manipulations.
We have to policy a user can set a measurement inactive but he cannot delete it. I f we will search to the database, we can restore all the data if something went wrong. Another good practice is check that the data is actually written into the database like a handshake and enrich the data with important information that can be handy for user years later. Because they may don't have the information w hy you took tree measurement of the same part and maybe wondering why it's getting worse and in that all have the same information to work with. I can straight this little future view. I can go back in here for the demo.
Usually when we have press builder make it row there in the string second two. What I do is select those parts row, name selection in column and that would give me a column which I then can save. I f I reload the data, this information is lost. What I can do now is this feature update database. I can update it, I can say, hey, label one, this is stable. Y ou can see here, label one is now marked as stable.
I can go and say okay, these measurements update I want to leave on stable, close it and I simply put the data from the database and as you can see the stable, unstable faces are stored. I f someone later makes it, then they can bow conclusion from the phases here is missing. This is the stable face, this is un stable face and the same goes with data. I can select some object inactive. It will warn me if I want those trees inactive a nd the next time I would call the function they wouldn't show up. If I want to have them back, I can select here include arrow meshes that those measures come back again. T his suits my script. I will thank you for listening and if you have further questions I will be [inaudible 00:19:42] later on. Thank you very much.