What recommedations for the following

Archived Topics
1

In my Historical data which is being written to an SQL table from FSQL there is a column that I want to populate called ‘SHIFT’. This is the shift number that that employee is currently assigned. The owner wants this info so OT and such can be tracked as well as performance. Anyway currently in FSQL I have some items in the action column of each of my machines historical folders. The action item takes the assigned operator column from the historical data and does a lookup in another SQL table to find what shift that person is assigned then saves this in the historical data when triggered to do so.

Because of this lookup being a SQL query if the link between FSQL and MS SQL is severed FSQL will not do caching. So I need to remove this look up from the historical groups and find another appoach to having the shift be set according to whom is assigned to that machine.

I know one approach would be to when doing the assignments also write back to the PLC the shift value. While this would work there has to be a better and more efficient way to do this.

Can you make any recommendations other than writing this data back to a PLC. Some way that while FSQL is doing the hisorical data that something will do a lookup based on the operator ID number and will provide the correct shift number for the operator. And will not affect the caching should the link be severed between FSQL and MS SQL.

Thanks and have a great day.

2

That qualifies as one of the stranger requests I’ve heard. I’ve never given much thought to the scenario where FactorySQL has lost communication to the SQL database, but is still caching values from the PLC to be logged. In any event:

The key is to store the value (assigned employee) in the PLC. If this value comes from FactoryPMI or another source, simply create a (DB->OPC) group in FactorySQL that keeps the PLC register(s) updated. Then use that tag instead of the action item in your FactorySQL “Historical group”. The PLC value will always be “up to date” because if the SQL database goes down, FactoryPMI won’t be able to change the value.

3

Nathan, here is scenario. It is not that I dont trust computers or our IT staff or anything like that but we have had problems with our entire network from time to time and these scenarios have to be accounted for. I mean we all know Murphys laws and sorry to say but this place is where Murphy lives. So in an effort to make the system as robust as possible we have to try to cover all bases. Our IT staff is installing some sort of watch dog program or something like that, they say this software is like a traffic cop, it will monitor things and if a problem is detected it will immediately go to the backup server. We will also have redundant MS SQL servers as well as redundant FSQL/FPMI servers. But we all know that things can go haywire real fast.

One of the things that we recently found was that if an action item is in a group which is Historic data and that action item does a query on an SQL table that that group will not cache. This action item is a query that pulls the shift number from a table in SQL. So now I have to find a different way to put the shift number in the historical data stream. As I said I know one of the way would be to write it back down to the PLC. But is there another way that is better. Such as can you write to an action item that is in FSQL from some kind of script in FMPI or would this not do would the writing to the PLC still be the best approach?

[quote=“nathan”]That qualifies as one of the stranger requests I’ve heard. I’ve never given much thought to the scenario where FactorySQL has lost communication to the SQL database, but is still caching values from the PLC to be logged. In any event:

The key is to store the value (assigned employee) in the PLC. If this value comes from FactoryPMI or another source, simply create a (DB->OPC) group in FactorySQL that keeps the PLC register(s) updated. Then use that tag instead of the action item in your FactorySQL “Historical group”. The PLC value will always be “up to date” because if the SQL database goes down, FactoryPMI won’t be able to change the value.[/quote]

4

One thing you might think of trying is to break your history out into multiple tables. It’s the more “database-y” way of doing things anyhow. For example, I think what most people do in this case is create a separate history log of shift changes or shift/employee assignment changes. Then you can refer to that table when trying to resolve data from a certain range in the production table.

In my experience, shifts are rather stable, and rarely change. Assignments to shifts can obviously change fairly easily. Therefore, I might create a table of shifts, and a separate table of employee assignment history. Database are called “relational” for a good reason, they are good at handling data that relates to each other. My theory goes that if you can get the data into the database, there’s a way to get it out how you want it. So, following this, my shift table would have an ID, a Starttime, Endtime, whatever else you wanted, an effective start date, effective end date, and a NoLongerUsed bit. Whenever we change a shift, we simply insert a new one and mark the old one as NoLongerUsed, and set it’s effective end date to today.

You can then update your employees to be in the new shifts, adding records to the employee shift history log. The point is that no data is ever modified, it’s all there, so if you ever need to say “What shift was Jim on in june of '06, and what production data was there during that shift” it’s all possible.

There are obviously many tweaks to this scheme that you could make, but I just wanted to throw it out there to give you a different perspective. Hope it helps!

5

Colby’s idea is good as well. That type of scheme came to mind when I first read your question. In either case you won’t be able to effectively inform the system of a change operators if you lose connectivity to the SQL database.

Your 2 options:
1 (mine) - You could keep track of operators in the PLC by using a FactorySQL group. Shift data becomes tags in FactorySQL, that just like any other data, will be properly cached.
2 (Colby’s) - Keep track of operators in another table. This gives you more potential flexibility with queries, but may require more sophistication in tying the data together with your history.

Either approach should be able to accomplish your requirement.

6

Thanks to both Colby and Nathan. I will present both of these idea to the group and see what comes about. You see the reason I had put the action item in was because it would look up the shift number during a write to the historical data. However at the time I did not know that by putting this action item in that it would not cache the historical folder. This was the whole reason behind this post finding a different way that might accomplish the same thing. The reason I had done it this way was because of the number of ways in which the assigned operator can be changed. The two distinct way in which the assigned operator can change are: 1. Thru the use of the Inductive automation Software. 2. At the local level with a system admin logging in to the PLC and changing the data. Number 2 was added in the event that the system went down for some reason. Some of the reasons might be: A lightning storm goes thru the area and knocks out some of our ethernet switches, the ethernet port on the PLC goes bad, one of the ethernet cards on the server goes bad, etc. These were all things that came up in our meetings and things that has happened in the past. Now during the hours of 8am till 6pm we have techs on site that can easily rush to the problem and get it repaired fast. But production runs from 6am till at least 4am and during the rest of the time there are no techs on site. So if one of these things happen the owner of the company does not like the people just standing around. So there has to be a method to keep the machines running at all time. And since the machines will not run without a operator being logged in. You see the point I hope. Like I said the owner does not like idle machines and he does not like interuption in his data. This is why we are trying to plan for any and all problems.

[quote=“nathan”]Colby’s idea is good as well. That type of scheme came to mind when I first read your question. In either case you won’t be able to effectively inform the system of a change operators if you lose connectivity to the SQL database.

Your 2 options:
1 (mine) - You could keep track of operators in the PLC by using a FactorySQL group. Shift data becomes tags in FactorySQL, that just like any other data, will be properly cached.
2 (Colby’s) - Keep track of operators in another table. This gives you more potential flexibility with queries, but may require more sophistication in tying the data together with your history.

Either approach should be able to accomplish your requirement.[/quote]

7

Martin,

I’m assuming that each employee has a unique ID, and that that ID is what you’re logging for analysis. Is this correct?

[quote]The two distinct way in which the assigned operator can change are: 1. Thru the use of the Inductive automation Software. 2. At the local level with a system admin logging in to the PLC and changing the data.
[/quote]

In the 1st case, is the IA software writing to a database table or to the PLC?

In the 2nd case, by what do you mean “system admin logging in to the PLC”? Is this through an local operator interface? Or through the PLC programming software? Or some other method?

Exactly, how is a shift identified? For example, is it something like 1st, 2nd, 3rd? Or is it identified by some label, e.g., “A”, “B”, “C” with the actual shift times changing on a scheduled rotational basis? Or is it simply identified by times?

How are shift schedules and operator assignments currently handled on the supervisor/management level?

At the machine level, how do you determine to what shift an operator belongs?

Just trying to understand…

8

To answer your questions, Yes each operator has a unique ID number assigned to them. THis is the same number that is used by the Kronos Time clock system.

Your second question about admin at the local level. In the event that communications is broken between the Server running the IA and Kepserver products that would mean that that unit can not send or receieve any data thru the network. Like I said the owner chooses what machines need to run what materials and the supervisor assigns based on that input. So lets say a machine is scheduled to run but for some reason it lost connection to the network and like I said these things seem to happen when a tech is not on site. So how do you run the machine because it is interlocked with the new system and requires a valid login. Well we adapted a small routine in the PLC where a supervisor can log into the PLC and change the parameters of the PLC. kind of like loading a new receipe. They use a specially coded badge that we call the admin badge. When they are logged in certain screens are shown on the PLC screen that they can navigate thru to change the required parameters. Hopefully this helps clear up this.

As for your third question. Because Kronos uses an SQL table we have a view that is setup that keeps updated from the Kronos system. This way as new employees are added or HR makes changes to a persons skill level we also get this information. As for how it is laid out, well it seems kind of redundant but there are actually three fields in the SQL table that define the employees shift. There is their defined category which is a NVARCHAR(50) this is a text string that says something like ‘1st shift sorter’ then there is the employee dept which is a NVARCHAR(5) this is a numeric value of 2100 for 1st shift and 2200 for second shift, and finally there is shift number which is a INT this is simply 1, 2, or 3.

Now as for the shift information, we do not have at this time a 3rd shift, it is in there in case we ever need it. Now this is where it gets a little crazy. We do have set and scheduled shifts these usually run 6:30am till 3:00pm for 1st and 4:30pm till 1:00am for second. But due to production demands and employee previous obligations these can change by a managers signature. So someone might have a DR appt. they can have their schedule adapted so that instead of comming in at 6:30am they can come in at 4:00 and leave at 12:30 or they can come in at 11 and leave at normal time or stay and make up their hours. Sometimes they are even allowed to change shifts for the day. This is what adds to the confusion if you see my point. And finally if production demands are really high they will work a 12 hour shift from 6:30 am till 6:30 pm or 4:30am till 4:30 pm depending on what need to be done. This is why I think the Data Processing Dept wants the assigned shift in the historical data stream. I tried to tell them that why not just create a simple Query that would go thru the data base use the employee ID and do a lookup in employee database and populate that field, they are doing something similar for the reports they are generating when they print the reports thru crytal they do a look up from one data base to another to get the employee name so instead of having their number on the report they have their name. But I have not heard back yet about this idea.

Hopefully this clears up all your questions. If not dont hesitate to call or PM. Have a great day.

[quote=“MickeyBob”]Martin,

I’m assuming that each employee has a unique ID, and that that ID is what you’re logging for analysis. Is this correct?

[quote]The two distinct way in which the assigned operator can change are: 1. Thru the use of the Inductive automation Software. 2. At the local level with a system admin logging in to the PLC and changing the data.
[/quote]

In the 1st case, is the IA software writing to a database table or to the PLC?

In the 2nd case, by what do you mean “system admin logging in to the PLC”? Is this through an local operator interface? Or through the PLC programming software? Or some other method?

Exactly, how is a shift identified? For example, is it something like 1st, 2nd, 3rd? Or is it identified by some label, e.g., “A”, “B”, “C” with the actual shift times changing on a scheduled rotational basis? Or is it simply identified by times?

How are shift schedules and operator assignments currently handled on the supervisor/management level?

At the machine level, how do you determine to what shift an operator belongs?

Just trying to understand…[/quote]

9

Martin,

What I’m trying to tease out is where information resides and how it’s used.

If I understand correctly…

  1. A shift is identified by a unique combination of the three related “shift” fields in a Kronos managed SQL Server table.

  2. The operator is linked to a shift by his/her ID number.

  3. There is no “historical” logging of the shift to which an operator is assigned. In other words, all you can determine from the Kronos table is to what shift an operator is currently assigned.

  4. When an operator is assigned to a machine, his/her ID number is placed in a PLC register that’s logged by FactorySQL to a SQL Server table.

  5. The operator ID can be entered either via a FactoryPMI window (if every network device is connected and working properly), or via a “local” operator interface by a person with the proper administrative credentials.

  6. Likewise, the “recipe” for the machine is either entered via FactoryPMI or at the “local” operator interface.

  7. When an operator changes his/her schedule (but not shift assignment), the changes are not made in the Kronos “shift” table. For example, let’s say Joe is assigned to 1st shift. He has a doctor’s appointment today so he comes in at 10:30 am instead of 6:30 am. In order to get his full day in, he stays until 7:00 pm. I’m assuming this information is not reflected in the Kronos shift data.

Depending on whether I correctly understand the above, I will have additional questions for you…

10

Congradulations MickeyBob you win the prize. i am not sure what the prize it yet but you put it better than i have ever been able to. What you have said is entirely true and correct of the project as it is right now. There is only one thing that was missed and this was probably on my part. As per a meeting that took place today some items finally came into a clarification. there will be two shift values in the historical data. One will be as mentioned and give the shift that BOB is assigned, the other will be the shift that he is currently working. This way when they finally get all the reports generated they will be able to do an end of shift report. And finally we were able to make headway on come to a conclusion that anything ran between 4am and 4pm will be considered 1st and anything from 4pm to 4am will be 2nd. So at least now we have some concrete figures to go on. the working shift will autochange, I will write some code in one of the IA software packages that will at 3:59:59 fire off a collect routine then at the end of the routine update the working shift from 1st to 2nd. So now with that is the picture becomeing more clear?

11

Martin,

Ok, let’s divide and conquer…

Our first task is to make sure we have enough information to determine when an operator is in charge of a machine. If you are logging the employee ID entered into the PLC register for the machine, we’ll have that information.

Note: You don’t have to log the employee ID on a timed/periodic basis. You could configure FactorySQL to log only changes to the employee ID which is way more space conservative.

The second task is to historically log the shift to which the employee is assigned. There are multiple ways of accomplishing this, depending on where the information resides.

If the shift assignment information can be written, along with the employee ID, to register(s) in the PLC, we could simply log them like the employee ID.

If the shift assignment information cannot be written to PLC registers, then we will need to get the shift assignment information directly from the Kronos database tables.

The simplest way I know to do that doesn’t even involve FactorySQL, just SQL Server. You or your IT DBA could define another ‘historical’ shift assignment table. You can then define a trigger on the ‘realtime’ shift assignment table that will log any new assignment to the ‘historical’ shift assignment table. You would need to make sure you put enough information into this ‘historical’ shift assignment table so you can link the employee, by ID, with the historical assignment records. This linking would only actually be done when generating your reports.

The third task is to determine whether the employee was working on 1st or 2nd shift as defined by time (i.e., 4 am - 4 pm, and 4 pm - 4 am). The data collection part of that is trivial. The time stamp (i.e., t_stamp) column of your table tells you that. What’s more difficult is processing the data for your reports. You have to do something like query for the first instance of each unique employee ID over the shift time-frame. Writing this query can be a little tricky, but it can be done.

If I’m missing something about your particular installation that makes this approach not work, just let me know…

12

Ok lets divide and conquer. As for the shift information it is nothing more than an int, so yes it can be written to the PLC. And as of this posting it is being written to the PLC. So that is not a problem. Currently in the PLC we have several registers that are setup for operational characteristics of the machine, ex. Operator, Batch number, assistants as required, and others. These are all written to the PLC from the IA software or can be done at the local level with proper admin privledge. Next we have a boolean bit which is the trigger to write to the compact flash on the PLC, this is the bit that is being monitored thru FPMI, when this bit is active it also polls the OPC server and writes the same info that is being written on the flash to the SQL server. Currently we are in the process of validating the data. This was the reasoning behind the whole timing post elsewhere on this forum. Our DB staff is currently making a little program to take the CSV files created on the flash card, load them into a temp SQL table, then using some sort of comparison compare the temp table to the SQL table. If any discrepancies arrise an alert of some sort will be issued and someone will look over both tables to find out what the discrepancy is and make any required corrections and notifications. We are also in the process of making other screens based on requirements.

A few issues that are still a slight thorn are my time sync section which for some reason still hangs up, the hourly count off, and SQL tags. I am hoping the newest version will correct whatever may be causing the issue. Or who knows it might be something on my end. What is happening is I have SQL tags which will allow a different screen to be shown if they are active. And even if they are not active they still sometimes allow the user to go to the other screen. It could be as simple as an update rate, dont know. But if it were an update rate, and my rate is set at 2 seconds, and I do nothing on the screen for 10 seconds it still allows access to those screens. Not sure but am doing what I can.

As for your ideas I will put them on the table at the next meeting and see what comes about. Any other questions please feel free to post I will do my best to answer. Have a great day.

[quote=“MickeyBob”]Martin,

Ok, let’s divide and conquer…

Our first task is to make sure we have enough information to determine when an operator is in charge of a machine. If you are logging the employee ID entered into the PLC register for the machine, we’ll have that information.

Note: You don’t have to log the employee ID on a timed/periodic basis. You could configure FactorySQL to log only changes to the employee ID which is way more space conservative.

The second task is to historically log the shift to which the employee is assigned. There are multiple ways of accomplishing this, depending on where the information resides.

If the shift assignment information can be written, along with the employee ID, to register(s) in the PLC, we could simply log them like the employee ID.

If the shift assignment information cannot be written to PLC registers, then we will need to get the shift assignment information directly from the Kronos database tables.

The simplest way I know to do that doesn’t even involve FactorySQL, just SQL Server. You or your IT DBA could define another ‘historical’ shift assignment table. You can then define a trigger on the ‘realtime’ shift assignment table that will log any new assignment to the ‘historical’ shift assignment table. You would need to make sure you put enough information into this ‘historical’ shift assignment table so you can link the employee, by ID, with the historical assignment records. This linking would only actually be done when generating your reports.

The third task is to determine whether the employee was working on 1st or 2nd shift as defined by time (i.e., 4 am - 4 pm, and 4 pm - 4 am). The data collection part of that is trivial. The time stamp (i.e., t_stamp) column of your table tells you that. What’s more difficult is processing the data for your reports. You have to do something like query for the first instance of each unique employee ID over the shift time-frame. Writing this query can be a little tricky, but it can be done.

If I’m missing something about your particular installation that makes this approach not work, just let me know…[/quote]

13

If I understand correctly, you are allowing navigation to certain FPMI windows based on who is assigned to a machine, and further, you are also determining who is logged in by looking at a value in a database table whose value is updated from the machine/PLC by FSQL. Are you implementing any FPMI security?

14

No not yet, I am hoping to convince the powers that be that it would be much nicer to have each person using the system log in, even if using the windows authentication routines, but as of yet that has not been adopted by the board. I do not like the fact of everyone using the system currently and providing feedback using the same login but again I was overruled. Such as life.

If I understand correctly, you are allowing navigation to certain FPMI windows based on who is assigned to a machine, and further, you are also determining who is logged in by looking at a value in a database table whose value is updated from the machine/PLC by FSQL. Are you implementing any FPMI security?[/quote]