Best/Easiest System to Get Raw Temp Data into SQL Database?
Hello, I have a background in software, but I'm a newbie at HVAC control systems. My current mission at work is to select/purchase/install a set of temperature sensors, collect the data in (near) real-time, and insert it into a SQL database. The data will later be displayed as a new object on the company's Business Intelligence portal, and have alerts that go off based on temp thresholds (..but that is a different beast).
What I'd like to ask is if anyone could please recommend a good starter system that could be deployed to a relatively small department area (8 room sensors) at first, but then be scaled out to around 100 sensors if/when this project gets deployed to the whole company? The most important factor for me is that the data comes into the server in some kind of 'open' format that I can pick up and read using custom code. If there were some kind of widows ODBC connector to SQL Server '08, then that would be ideal, but I'd be happy even if the device could output plain text delimited files.
If anyone has done a project similar to this, I'd love to talk with you and get some feedback on the approach I'm taking. Also I'd be willing to trade some help getting your data to display nicely on a BI dashboard in exchange for some help getting the raw data in on my end.
It sounds like you might have to make a substantial investment to collect just input data. Many control systems that use web servers are based on XML. My guess is that you can write code to bring the data into your SQL database. The problem is that you will probably have to buy a lot of 'Input' modules to do your project. That can be a little expensive if that is all that you are doing. When all that data is incorporated into a full blown control system it is easier to justify the cost.
Matrikon OPC has some pretty good products that might help you get started...
In order to get information from your temperature sensors, you can use one of the Matrikon drivers listed on this page:
This software will convert whatever protocol is listed (BACnet, LON, SNMP, etc.) into the OPC open protocol.
Then you can use Matrikon's OPC Client for ODBC to put the information into a SQL database.
This would be an easy, piecemeal way to get the data into a SQL database. The hardest part would be finding the temperature sensors and communication method back to the OPC server.
Interesting... what's the required sample rate on your temperature trends? once a minute?
A JACE with an I/O module can more than handle your requirements, now and in the future. It can export data to MSSQL (2005), Oracle, DB2, and MySQL.
Your project sounds like fun, but you may have been tasked with reinventing the wheel.
What good will the alerts based on temperature thresholds do for you, if you won't have control of those temps? As dapper mentioned, bringing all of this data into a control system would make it a lot easier to justify the cost.
The latest version of Alerton's BACtalk software has SQL Server support - can also monitor and trend as many temp sensors as you want.
After you decide which control system to use you can use a program called OPC datalogger to capture the data and write it to a SQL database. Just make sure your control system has some type of OPC server built in.
I would recommend looking at an Arduino microcontroller.
For what you what it will do it easily and you could most likely program it yourself ( it is fairly basic but a very robust programming language)
Your total hardware cost will be in the neighborhood of about XXX at the most, It can natively store values in a excel file on a host pc, and any of the systems we deal with on here will be substantially more expensive, and they are only as good as the person programming it.
Pricing removed per forum rules
Last edited by Senior Tech; 11-20-2008 at 12:11 AM.
Try doing a Google Search on “temperature input to a pc”. I tried it and came with several manufacturers of temperature input devices directly into a pc ........ both, wired and wireless.
Following shiff's recommendation above, it looks like this company has something that fits your requirements by exporting the data to a .csv format.
wireless, ethernet, usb, serial, etc
You now realize there are alot of ways to do this, but not necessarily alot of inexpensive ways to do it.
Also, you are attempting to do all the setup via something you make rather than use controllers out there that easily can be setup alarms and inexpensive servers or other devices that will email the alarms to you.
I think you want to figure out more of what you want to do.
<bias alert> Maybe something like a Loytec LVIS touchscreen or web server would be easy for you to setup all of your alarms. The touchscreen gives you local interface and emails alarms which you can configure directly in the LVIS which also has a built in VNC server. Or, maybe you use some devices and attach a OPC based webserver and pull those points off into your SQL DB.
However, I'd look into some simple I/O devices that you can config your loops and go from there. It certainly sounds like you want to reinvent the wheel when there are literally hundreds of products out there doing what you want. Maybe even look at smartcontrols.com or distech-controls.com for I/O.
Last edited by sysint; 11-18-2008 at 01:19 PM.
Thank you everyone for the fast and insightful replies. I've been researching all of the recommendations here and building a comparison matrix, still in process.
To follow up with more detail on the requirements, the reason we are 're-inventing the wheel' is so that we can seamlessly integrate this data into an intranet Business Intelligence (BI) site based on Microsoft Office SharePoint Server (MOSS). The SharePoint BI site has a number of Key Performance Indicators (KPI) that monitor performance metrics specific to the business (Exa: 'Widget Output Per Day', 'Open Support Tickets', 'Server Uptime', 'Server Disk Space', 'Server Traffic (DOS Attack)', etc). So the bottom line is that we want a single source of BI that consolidates all data sources, and displays them using the Microsoft technology stack in a way that non-HVAC/Technical business users can understand. All of the business users already have Office installed, and have access to the SharePoint intranet, so there is no need to purchase/install/maintain additional software on any of the end-user client computers.
In our root BI page there is a big list of metrics, each with green/yellow/red indicator lights - and [digo] was right on in that the data refreshes once per minute. When an indicator icon turns red, then the user clicks on it to 'drill-down' to the details of that data. In our case, several of those KPIs are linked to pages that have embedded Visio diagrams in them. In these Visio diagrams, we have made use of some Add-Ins found on VisioToolbox.com to 'bind' the source data to the Visio diagram objects. Then we set the refresh rate, conditional formatting thresholds, such that in the end we have a diagram that looks just like our building floorplan, with icons representing all of the servers (or in my case, temerature sensors), and you can watch as the picture turns colors and you can immediately tell which server (or object) needs attention. Another great thing about using the KPI in SharePoint is that an email and link can be automatically sent to the person/list associated with that area.
Here are some links about this in case anyone is interested:
So the relevant requirements for my case:
1. Detailed temperature data needs to be displayed in Microsoft Visio
2. General temperature data needs to be displayed in SharePoint BI site
And both of these objectives are easy if I can first get the data into SQL Server at least once per minute. At first I was leaning toward a .csv format, but using OPC sounds promising because using a standards-based protocol would allow us to easily switch this to another OPC-based system in the future (like one that links with air pumps and controls). Even if the hardware vendor does provide their own software to view/control the same system, I don't see why we can't have two co-existing systems - one for the 'hardcore' HVAC/Techies to configure and control, and another for the 'softer' business users/execs who just need to know the high-level stats with sort of a read-only access. Does anyone know of a case where using one OPC-based system prevents another OPC client from accessing that same data (read-only)?
Again, thank you to everyone who has replied so far, and I'm open to hearing whatever kind of feedback is out there. I just joined the forum yesterday but this seems like a great community. Thanks for all the help!
They can co-exist. However, you would be adding to the overall cost by having your own sensors - which translate to duplicate parts (sensors, wires, I/O modules), plus labor (who's pulling those wires) - instead of tying into an EMS that can actually control all of this. Are you in touch with Facilities Mgmt at your company? Do they have an EMS in place, or is it just a bunch of thermostats hardwired to packaged A/C units?
Originally Posted by TylerWalts
In Tridium's environment, the 'hardcore' portion can be hidden from the end-user. Each user can belong to a group, and only be assigned views on the system that you allow. End-users don't need special software either, just a browser with JRE.
One of the main advantages of Tridium is their ability to integrate with diverse systems on different protocols. BACnet®, LonWorks®, MODBUS®, SNMP, OPC, oBIX, or a host of supported legacy protocols. Plus Enterprise RDBMS connectivity to various db vendors.
Take a look at their AX demo.
PS: Many of us in this forum are System Integrators. Tridium sells their products to various OEM channels, who in turn have authorized SIs.