QUOTE(Conor @ May 2 2009, 05:23 AM) [snapback]81752[/snapback]
Thanks for that Paul.
I have recently done a basic course in SQL. I am extracting data from our Wonderware to an SQL DB. I am by no means any kind of SQL guru, a novice.
If you are doing subqueries, know what correlated subqueries are (and what a triangle query is and why you have to avoid them), views, and stored procedures, and editting/improving your indexing, then that's the level of "expertise" that you need to be an "expert" for this kind of work...the goal is to use the data, not be a DBA.
QUOTE
Our problem at the moment is that our system was set-up a few years ago, about 10. Anyway our data is being stored in csv and notepad files. Some of the csv files are over 50 Mb per day, which excel can't open complete file, it comes back with an error file too big. Our data is extreemly important to us, so I believe that it would be better for us to save this data to a server/db.
You were ready years ago.
QUOTE
We have a budget for an Historian, if I can sell it to the managers. My IT manager is behind me and my engineers as well. I have to convince the management to give me the money.
As you said we need to know what info we want to store. We are kind of on top of that (I think). We have a fair idea of what we need to store.
Thanks again for your help.
Ok, that's good that you've gotten that far. Here's the real issue/problem though. Excel is really, really good at doing a lot of more or less arbitrary data analysis and chances are that everyone is already very comfortable with doing it this way. The goal is to get rid of the spreadsheets (CSV files in your case). And it's probably killing the E-mail system too when people keep E-mailing these 10-50 MB files around everywhere, so I'm sure IT is on board with it, too. I'm sure this sounds familiar because I've created those kinds of monsters myself multiple times in the past, and I'm very familiar with the problem.
One road that you can take is to simply set up automated data collection. Then you provide an interface to be able to collect and download all that data right back into the same spreadsheets that were the problem in the first place. That is the Wonderware model. This is exactly what ActiveFactory, the free thing gives you. You might go as far as embedding VBA scripting in your Excel spreadsheets (been there, done that too). But in the end, you've got a monster on your hands. People will still spend an obscene amount of time and suck down a lot of file space with those giant 50 MB spreadsheets, and keep right on E-mailing them.
The better model is to wean them totally or nearly totally off the spreadsheets. You've got to go around collecting all that data analysis knowledge and embedding it into a front-end system. You need a report generating system, or something similar. Each of the historians out there has a front end as well. For Wonderware, it's called Information Services. For GE Fanuc, it's called Real Time Information Portal. For Rockwell, it's called FactoryTalk VantagePoint. These things provide a web-based front end for managers and usually some sort of very simple point-and-click interface for doing basic SQL calls. They also do charts and reports, and provide some way to export data back to Excel. The goal of course is to put all the intelligence into these and do away with Excel altogether since it eliminates all the E-mail and all the effort to constantly recreate everything (create the report once and for all). There are some generic versions of this stuff, too. Crystal Reports and the free version (JasperReports) very popular among the IT folks.
One feature that I found is very useful and you should look very closely at is whether configuration requires a special license or if you can configure/modify reports online by anyone. This is one of the big downfalls of Crystal Reports and at one time, the way you use the FactoryTalk program...they made a clear distinction between "viewers" and "programmers". Excel doesn't do that, and neither do some of the other reporting systems. And you probably want to provide that capability to everyone who had it before.
This takes time to set up. A LOT of time. You end up with a dashboard showing all the plant KPI's in it and some drill-down or trending systems to analyze WHY things didn't go right. One of the keys here is that there are always a few heavy-duty data analysis gurus in your plant. They are frequently process or production engineers, but their actual titles can be quite variable (even a plant manager). You have to enroll and engage them into the process and get them to help develop this part of the system. To be successful with this approach, you can't just be a plant services organization. You've got to enroll and engage the operations folks into being active participants in the development.
The real key here is to go after the front line supervisors and other guys that have a lot of tedious clerical work, or who can't really get at the visibility on the data they need...give them a real time chart. And it's key to give everyone plenty of comment fields that are generated through the "portal" that I described in the last paragraph...raw data by itself is not enough. Don't forget too that anything that a supervisor is typing in or writing down in a report should also some how be entered and incorporated into the system. You've got to code business rules into it too, such as "<90% efficiency is bad". I usually do it by coloring things because that's easy to set up. If you do this and do it well on the first couple in roads, what you will find is that these things take on a life of their own. Before you know it, everyone is diving in and extending the system further and further out.
There is one huge limitation on most of those historians that I just touched on. The best way to do all the data analysis is to incorporate ALL the data, even the supervisor comments and stuff that ends up getting entered by hand (such as daily staffing numbers). This stuff has to be mixed in with the PLC-generated data to be effective. This is one place where most Historians fall down...they only really support the data generated by the HMI/SCADA and don't provide a mechanism for doing the data collection part. This is one area where FactorySQL and Cimplicity PE's built-in logger (not the same as iHistorian) do really, really well. These historians output their data directly into SQL and don't try to provide a "SQL-like" front end. The downside is that SQL is not a speed demon when it comes to data collection (tops out at perhaps 200-300 data points per second) but the advantage is that you can freely mix HMI and business data.
The third area to consider is any of the "metrics" based packages. First off, I'm going to tell you right up front that these things are very, very expensive. The most expensive historian product is usually half or less of the cost of these. I've been quoted anywhere from $20K up to $80K for them. I'm not sure what the Wonderware product name is but with GE Fanuc, it's Plant Applications and with Rockwell, it's FactoryTalk Metrics. In a nut shell, what these things are is a very generic model of a plant. They are an add-on to the above reporting/portal system. You essentially "model" your plant...you program them with details about the number of production lines, the production flow, key attributes on those lines, what the major process steps are, how the PLC's are tied together, what "downtime" means, and so forth. Then these packages automatically calculate key performance indicators (KPI's) such as OEE, efficiencies, production rates, downtime, percent defective, etc.
The nice part about these packages is that all that "process intelligence"...the KPI's and the reports are more or less already programmed for you. You just have to provide a bunch of up front data about the configuration of the plant. These save an incredible amount of time in discrete manufacturing (widget) plants. They can work in process or batch plants but they don't seem to have as much value there. Also, they still do take a lot of time doing a lot of customization with both the existing HMI/SCADA system and the software packages themselves.
In the end, using these packages is supposed to save a lot of time on your part since you are buying a lot of templating and canned stuff. What I can tell you for sure is that if you go down the road of developing a historian and reporting system without the benefit of a metric system, figuring that you will be able to add it on later, it won't happen. You will end up duplicating what the metric system does and you will never go back. You need to decide up front if you are going to use one, or never use it. So download the manuals and any other literature on them and look really close because if you don't buy it up front, you'll never use it. They are huge time savers, but they are very overpriced for what they do.
The other reason for reading up on them is that you will also get a lot of hints about how to go about implementing metrics, ESPECIALLY in widget plants. I found that the user manual for FactoryTalk Metrics is a treasure trove of ideas on how to implement performance metrics. Once you get the idea, I'm really not sure how much value it does have.
I'm saying this up front because I've been through several iterations of this process. Historians are very valuable but a pain to work with since they don't fully support SQL unless they truly log to SQL (which the ones you mentioned don't). So right up front, be aware of this limitation. In the end I've been happier with a true SQL logger and not the specialized historian systems. Second, you need some sort of front end interface. And it better do way more than just show trends and data. Because very quickly you are going to outgrow that or you are just going to rehash the old spreadsheet system.
By the way, historians are pretty generic. And the front end reporting systems are also generally pretty generic. But there are certain features which you've got to pay attention to. You may want the "almost SQL" historian if you are in a process plant. In a process plant, trending is very, very key to your system. If I'm collecting data points at a rate of once per second, 1 hour of data is 3600 data points. The data for the trend can be gathered and displayed in a couple seconds. But let's say I want to look at the same data over the last day or the last week. Most trend charts absolutely choke on anything larger than a few thousand data points. The way around this is to SAMPLE the data...if you want a week's worth of data, simply take data points once per hour (or perhaps even the averages, mins, and maxes over that time period). The non-SQL historians can do this very easily because they collect statistics and time-based indexes on the data as it is collected. You can still usually do it with the competitor's system by writing a SQL query. Usually you just have to specify an additional where clause to a metadata variable, but this is not nearly as clean as the built-in capabilities. You can also do it with generic SQL. The key is to generate auxiliary summary tables using triggers, and then use stored procedures that can piece together the data out of the various auxiliary tables. It's how the non-generic SQL historian is doing it. This is kind of ugly compared to the pre-built historian package but not an insurmountable problem.
Finally, you've got to consider how you are going to be summarizing/filtering the data. Again, SQL makes this really easy to do but there are limitations. As you write more and more complex stored procedures and as your data grows to thousands of millions of rows, you run into a very real performance problem even with indexing. Historians often provide something called a "calculation engine". This is a specialized programmable module that automatically calculates things like running totals, averages, mins/maxes, weighted moving average filters, and so forth. The data is stored in auxiliary columns in your table. They can be used to calculate down time and such with some very clever tricks (again, look closely at the FactoryTalk Metrics manual to get the idea). You will eventually find yourself writing stored procedures with triggers to do the same thing in SQL, or automated routines that have to run periodically, or some additoinal ladder code to massage the data, or views and/or stored procedures. Pay close attention to what the calculation engine does and does not do and how complicated it is to use, if your particular historian has one.
Ultimately you should be trying to drive as much of the calculation functions as possible into the historian/SQL server. All data sent to the front end should be more or less "polished" data. If you don't do this, then again, you are right back into the Excel spreadsheets. You need to be aware of just how hard/easy this is to do.
Calculations engines are a must if you take the "log everything" approach. It's really the only practical way to do it. Of course if the calculation engine will not go BACK in time to sift through all the old data and you are doing the "log everything" approach, then obviously the old data is of really limited value to you in reality.
I strongly suggest that you get copies of all the software you want to look at. All the vendors usually offer something like a 2-hour runtime where you can run the system for 2 hours before you have to reset it and try again. Most let you do timed (30 day) trials, too. You should try to set up the historian and at least the front end and purposely try to more or less duplicate the functionality (you probably won't have exactly the same appearance/layout) of some of the reports that are being generated today around your plant. Perhaps even go after one or two of the "annoying issues" that the plant is having now with some new capabilities that the existing system cannot do. You should be able to get to some sort of working system and at least explore the capabilities with less than a week's worth of work. Expect 1-2 days as a bare minimum though.
One I suggest you try as I mentioned before is GE Fanuc's iHistorian. You can get a 10 point version with NO limitations on it. The license is good forever. This lets you truly start using a historian immediately and see how you like it without having to buy anything at all. 10 points isn't much but for a process plant it is usually just enough to cover one major process area. Otherwise, everyone (including GE Fanuc) lets you add as many points as you want in their various trial options. This gives you the experience and knowledge to know what you are looking for.
QUOTE
Also, you didn't really mention Rockwell. Is there product any good???
Rockwell's historian works pretty well. I have no qualms with the historian. I think their front end interfaces though are very limiting in terms of price-to-value. It seems like you have to pay for a very expensive license for pretty much every PC you put the service on. This can be incredibly expensive when every supervisor and manager and engineer in the plant needs the capability to access the system. But that was my experience 5 years ago and Rockwell may or may not have gotten a clue about pricing in the mean time. With FTA, it should be pretty easy to set up a floating license system but I don't know if they have that capability or not.
Let me give you some idea of how I set things up in a discrete manufacturing plant as an example of how to do this kind of stuff. We were very interested in trending and downtime on our casting processes. The casting process takes about a minute per unit of product, and there were 4 parallel lines.
One way to implement the data logging would have been to log raw data. I could log the positions of all the actuators and sensors on the machine. Then I'd have to set up a way for the calculation engine to glean from this data what a "cycle" of the machine is. And determine based on the cycle data (it's semi-automated, operators initiate each cycle of the machine and are intimately involved in putting in a fresh mold, pouring liquid metal into the ladles and such). This can be done and we might (or might not) get some useful data on how the machine is performing within each cycle. I set up some data collection on this but never really got anything useful out of it.
The second approach is to create a "batch record". Each cycle of the machine recorded all the setup and measured parameters along with time stamps and various timers. With these records, it is very easy to check on production rates (just count the number of entries). By knowing what a "typical" or otherwise properly running cycle is, we could easily detect downtime by just looking at cycle times and picking off all the ones that exceeded the "typical" cycle time with some margin accounting for slow/fast cycles (charts of cycle times showed a typical long tail distribution...long tails were downtime). This provided far more and richer data to work with than the "log everything" approach. It is possible (especially with a metrics module) to do most of these additional side-calculations within the historian system but it took less than a dozen lines of code for almost every process step to implement this at the PLC level. All I did was create a "cycle counter". Then the historian logged data whenever the cycle counter updated (a triggered update instead of a timed one). Within the PLC, I made sure that all timers and such were updated in order so that the last piece of data to be updated was always the cycle counter.