Free OPC datalogger ideas

[Nathan 7]

So here's the deal - I suggested a free datalogging version of FactorySQL and the guys at Inductive Automation sound like they're willing to consider it! What I need from you is: a thumbs up that it'd be useful as described, answers to my questions, and ideas on how it could be made better. You don't have to get into the "knitty gritty" for a good response. A simple "yes" or "no" on the concept and why would be helpful. 1. FactorySQL currently supports: MSSQL, MySQL, Oracle, PostgreSQL, DB2, and, I think, Access. 1a. I'm thinking that a .CSV group to support logging to a spreadsheet (format) would be useful. It would need to be able to write to a network path and automatically separate files by date. The file lock aspect of users opening the spreadsheet (instead of a copy) seems pesky. 2. Logging is done with a "dynamic schema" (wide) approach where tags are represented as columns and each sample of everything is a row. It tends to perform better than the alternative, especially with things like table compression. The disadvantage is that adding tags to existing data tables creates an inefficient column of NULL data. 3. Some other packages use a (tall) "Static Schema" table design, which has a column for the: time, tag path, and value. This is more of the textbook design for tables, but not so good at historical data. It often takes up more space (time column for each datapoint instead of set of points) and is almost always slower to query for lots of data regardless of how you have it keyed (consider querying a time that spans 50,000 sets of 25 out of your 50 datapoints - 1.25 million rows based on time and path, versus 50k rows with 26 columns of sequential data). The nice thing about a static schema approach is that you can change the points you're logging or your logging intervals easily without bothering anything else. FactorySQL supports this in a "Block Data Group", but I don't think it'd be useful in a free datalogging version. "Realtime" array writes make sense to do that - datalogging doesn't - except for the special case where you're trying to plug it into a pre-written application that expect the format to be just so. Correct me if I'm wrong here... 4. Based on the row level datalogging, writing on value changes isn't very meaningful. That really works in an optimized package where the trending/reporting utility knows what the logger is doing. I think it would be best to log based on a preset update rate. In my experience this performs well. And, data logged a variable rate sometimes creates problems with certain applications and looks deceptive when viewed in a spreadsheet. 5. Alarming - I don't know whether they'd be willing to include alarming functionality in a free version. The current setup creates and maintains a "realtime" alarm status table and an "alarm history" table. It sends email alerts that users typically set up to receive as text messages on their cell phones. Would this be significant to you beyond a datalogger? As an aside, I despise the practice of #1b (CSV logging) for most applications. I think that it'd be a great alternative for users who do the VBA DDE through RSLinx in Excel thing. I guess it does have it's places. What do you think? Edited 7 Jan 2008 by Nathan

[Ken Moore 23]

I think a data logger that saves as a .csv file would be an excellant idea. Many times you just want to log data for a special circumstance, and don't need all the bells and whistles. I currently do something similar in house. To get around the open excel file issue. I write to one file which is not available to users, then create a 2nd file that is linked to the first file. When you open the 2nd file, it updates it's fields from the 1st file, works great.

[Ghettofreeryder 0]

1. I agree with Ken, so cirtain circumstances, i believe CSV datalogging is a very good idea. However, my plants run historical data to MySQL every second right now anyways, so it isnt a big need for me. 2.I definately this a wide table is the way to go. Based of of my experiences, and previous descussions with other integrators, wide tables work significantly faster than tall tables. We were once trending PID history every second for 60 pid loops, and the number of rows was quite significant(in the 5.x million rows a day). As you can imagine, switching to a Wide table with columns for every value helps our query times drop. As Nathan stated, it doesnt matter how you key it, it still takes longer then wide tables. I think there is definately a need for a free datalogger, as we are in the 21st century lol. Logging to a dbase file, excel or god forbid, Access just doesnt make sense to me when there are significantly better options, such as MySQL

[JesperMP 2]

I think it is a great idea ! Exactly the simplicity of a csv datalogger like you describe can be used by many. To many ODBC and SQL is as straightforward as learning urdu. I often see users post about how to make a very basic simple lowcost datalogging. And there is really no answer apart from a few comercial products. I suggest that there be datalogging triggered either by fixed time cycle, or by separate trigger tag.

[Nathan 7]

It's pretty straightforward to use. You just browse through your OPC server and drag your tags into a group. The group properties define the logging rate, file (in the case of .CSV), etc. I don't think the free version would be able to support triggering - that gives you the ability to do batching/recipe operations, downtime tracking, reporting, etc. That's what accomplishes the feature set that "SQL transaction managers" like RSSQL accomplish. The retail version (and 2 hour runtime trial) currently support this. What I was referring to was a "delta" based logging. For example, check every 10 seconds, but only log if: 1. Value has changed more than x% since last log OR 2. Some number of intervals have elapsed since last log (say 1 hour) Using 1 alone can yield funky data. Did you have another idea about "triggering" for logging. Edited 7 Jan 2008 by Nathan

[mracer 2]

I am vary green when it comes to the gathering of data from a PLC. I have Plaint Metrics now but it is so much over my head that I need to get someone in every time I need to add something new. The other problem I have is the FT Metrics is so much of an over kill for what we are trying to log. All of the OEE stuff is just fluff to me. All I need is how many times did the machine cycle and what part number was it making at the time. (corrugation die cutter, short runs) One thing I do need is, I need to be able to have the data available to several people, Plaint Manager, Unit Manager and Planers.

[Nathan 7]

This is sort of relevant to my request. To begin with FactorySQL currently does what you're describing in a very simple and straightforward manner. OEE isn't a very tough concept. Packages like Plantmetrics are complex because they try to generalize the optimization of any process in a precanned nature. If successful, you leverage lots of meaningful research. If unsuccessful, it can be kludgey, complicated to work with, and not map very well to your process. I don't have enough experience with that particular product to comment one way or the other. Your next need, about sharing the data among several people, would be best done with FactoryPMI - or a web based (or very lightweight) client. The idea there is that any of them could pull up a web browser on any computer on the network without anything special installed, and see what's going on. Your post describes the "web based SCADA" model perfectly, except the concept usually continues to include: dynamic reporting, alerting, Enterprise Integration, etc. Now for what I'm referring to. I was suggesting a historical data logger. This is a software version of what a hardware data logger would do. It's for monitoring and recording periodic value changes that come from an OPC source (PLC). FactorySQL does what you're asking for, "transactiony" or triggered SQL operations, but the free version would be simpler to use and more focused.

[JesperMP 2]

I dont think they have to worry about batch and recipy type data transfers, as long as the datalogger can only read from the PLC. Approx half of the data I need to log is from continous processes and thus fits continous or some sort of continous logging. The other half of data is from machines that discharge a product in a non-continous manner. Continous datalogging for these is useless. I think there are many others in need for event-based datalogging as well as timed continous datalogging.

[Nathan 7]

You're right, I hadn't thought of that. Supporting triggering and handshaking is important for a lot of "data logging". It wouldn't really support a batching type operation if the group could only "read" (OPC->DB mode in FactorySQL talk). I was trying to answer what I though was people trying to "pre-optimize" continuous logging.

[paulengr 44]

As to the CSV data logger...just thinking about it, I believe that this capability is built into most of the HMI's out there for free in some way. About the only downside I can see of several of them is that you have to sort of know your way around SQL a little bit in order to extract the data. The RS-whatever line of products, Wonderware, and GE Fanuc's HMI's do this now. At one time I actually used RS-whatever with ".DB" (DBase files...almost CSV) as the only method of collecting plant data with a time resolution of once per day. Trending was treated separately and only had a time window of stored data of 2 hours...this was a 1995 design without a SQL system present in early Pentium processor days on Windows 95 and <20 gigabyte hard drives. As to the reference to "wide" vs. "tall" data...For any data that I consider "continuous" where I'm going to be making a trend chart, unless the data is very sparse, I shy away from it. Even then, most of the "event" tables (except alarms) in my plant are in "wide" format. The ones I'm thinking of consist of logging all the key parameters of a machine for each cycle. There is one entry created at the completion of each machine cycle. The cycle count triggers the logger. The rest of the parameter entries are just "along for the ride". I could log all operator settings in a "wide" table with the cycle events in a separate "tall" table but then I'd lose the correlations. The place where "tall" data tables make the most sense is when you have a sparse set of variables when change infrequently or where correlation is almost nonexistent (or is a "happens before" type relationship). This is typical of "alarm" or "event" logs. In those tables, the order of events and the exact times are more critical than knowing the values of all alarms across the entire table in a single row. You can cram an arbitrary number of such variables into a single table. That is the number one reason that even though I needed the full capabilities of something like Plant Metrics, I walked away from their particular product. They insist on whacking you for licensing fees for everyone everywhere. What we actually did went well beyond a standard data logging/trending system. But we fell short of shelling out for one of the canned data modelling systems such as Plant Metrics, Plant Applications (Fanuc's version), or whatever OSI calls their's. I really like their systems because everything is prebuilt but I can hire two interns for a year for the price of one of those packages once all is said and done. What we actually did was halfway in between. We paid for the front end of Fanuc's version which nicely interfaced with practically anything as long as it's SQL based (never really bothered using any of the other data interfaces), but developed our own custom data models via SQL statements.

[Nathan 7]

I don't think that's true about the CSV data logging - or at least practical and known. I see posts about every other week asking how to do that (usually on plctalk).

[JesperMP 2]

Errr... the HMI packages themselves are not free, on the contrary they are VERY expensive.I use WinCC Flexible myself, and has made some CSV datalogging via scripts. The main reason for doing so was my dissatisfaction with the "tall" format of the canned datalogging in WinCC Flexible. I think there is a need out there for a very simple basic (and free or at least cheap) CSV datalogging. Logging to SQL would also be nice, but then you start to compete with the big comercial products. May I suggest to make the CSV datalogger first, and when that is OK proceed to make an SQL datalogger ;)

[bguinn 0]

I am currently using a 6" Automation Direct touch screen, created some trending charts and am logging information in a csv format to a 4gig jump drive. I then created an FTP site so I could extract the data without having to remove the jump drive. Currently, I am only logging data every 10 seconds. I believe the screen was about $800 dollars, but I was already keeping it around as a spare, so you decide whether it actually cost or not. The software if I remember correctly was $130. Redlion also has G3 series touch screens that will do the same thing except they have to use a different memory card. The screen price is comparable to the Automation Direct screen, and the software is a free download (Crimson 2.0). They also have a communication gateway that you can create a virtual screen, but is just a little black box. This can be mounted in a panel or set up in the office. Just some ideas if anyone is just looking for something to get by. It is not actually free, and probably not readily available for just anyone to get info from, but in some ways that might be a blessing.