You need to learn SQL, not tomorrow, not in a few hours. You need to learn SQL right now! The good news is SQL, which stands for Structured Query Language, isn't that hard to learn.
The Why?
Ok, why should you learn SQL? Let me countith thy ways.... SQL allows you to do awesome things with your data. Do you want to look at all rooms that were occupied according to a patient database and join that data with temperature data from a BAS database well you could....
On the ROOM_SCHEDULE database we run the command
GRANT SELECT ON ROOM_OCCUPANCY TO (TEMPERATURE DATABASE USERNAME HERE)
This allows us to grab the data from another database in our query.
We then log into the TEMPERATURE database and run the query below
SELECT ROOM_TEMPERATURE AS "Temp", ROOM_OCCUPANCY AS "Occupancy Status", ROOM_ID as "Room #"
FROM TEMPERATURES t
JOIN ROOM_SCHEDULE r
ON t.ROOM_ID = r.ROOM_ID
WHERE (r.ROOM_OCCUPANCY IS NOT NULL) AND ROOM_TEMPERATURE IS BETWEEN 65 AND 75
ORDER BY ROOM_ID DESC
This simple query, takes two disparate databases and merges them together to create three columns labeled: Room #, Occupancy Status, and Temp.
The query joins the two tables together and only selects data where room occupancy is not null (meaning it has a value) and the room temperature is between 65 and 75.
The funny thing is, unless you have DBA in house who you have access to you would pay good money for someone to write what I just wrote in a few minutes. The nice thing about SQL is that the "syntax" is pretty easy to learn.
SQL allows you to take multiple disparate systems and do data analytics and data comparisons. Many of the tasks that customers ask me about can be performed using simple SQL queries. For instance, I could tell you how to take an occupancy sensor, power meter, temperature sensor, and light sensor and write some custom logic that will then appear magically on a BAS graphic that you will have no idea how to edit. OR....
I can work with you and your team to write a simple SQL query that can grab data from all of the databases, that anyone can work on (SQL is a wonderfully vendor agnostic language), and can be easily pulled into a variety of graphics sources... Did I mention, all of the software involved is pretty much free... HOLY COW BATMAN!
So then how do you make this magical language sing? First you need to know the Syntax!
The Syntax?
"The Syntax?" you ask. Yes, the syntax... The syntax is the "dialect" of SQL. There a few dialects out there. For this article I am mostly following the Oracle SQL syntax.
The four core pieces of SQL syntax are: SELECT, FROM, WHERE, and ORDER.
The syntax flows like this,
SELECT these columns
SELECT CHAIR_MODEL
FROM these table
FROM FURNITURE
WHERE these conditions exists
WHERE CHAIR_COLOR = 'Brown'
ORDER the results by these parameters.
ORDER BY CHAIR_MODEL DESC;
A query, is a call against the database using the syntax.
Thinking through some potential scenarios, what if you want to compare after-hours request, the requester, the temperature, and the amount of time in order to properly bill customers? What if you wanted to take the temperature and the amount of time and use those tables to create a table called Billing based on a calculation?
You could simply, write a Query, that SELECTS the Temperature and Time table does a little math,
maybe (setpoint - standby setpoint * (agreed on temperature rate here))
SELECT ((ZONE_SETPOINT - ZONE_TEMPERATURE) * 200)AS "Billable Charge
FROM TEMPERATURES
ORDER BY ((ZONE_SETPOINT - ZONE_TEMPERATURE) * 200 ) DESC
Then you take the timestamp of occupancy and create a time amount (Time_OCC_END - Time_OCC_START). You could then round this time period to multiples of 15 minutes. Finally you add an associate charge to the time, and send the data to a table containing afterhours invoices. You write a script that will create an invoice in the database of your local CMS.
SELECT ROUND((OCC_END_TIME - OCC_START_TIME),0) AS "Time Occupied", ROUND(((OCC_END_TIME - OCC_START_TIME),0) * 150) AS "Billable Charge"
FROM OCCUPANCY
ORDER BY ROUND(((OCC_END_TIME - OCC_START_TIME),0) * 150) DESC;
So WHERE do I learn this?
I have composed a list of a few good resources to learn SQL.
Conclusion
This is one of my more technical articles, for some this will be super basic. For other's this may be the first time you have ever seen a SQL query. While I don't expect everyone to become a SQL expert, you should at least have a fundamental working knowledge of SQL if you are in the Intelligent building sector.
Depending on the feedback I get from this article, I may branch into working with Web Services, and Big Data Suites like Cassandra and Hadoop. It is critical that you learn the following four subjects:
- SQL- Almost all of the smart building systems use an SQL or an MS Access (shudders...) database system. If you know how to work with SQL data you can do a lot of your enterprise analytics and reporting without integrations.
- Web Services/XML/SOA/WSDL- If none of these terms ring a bell to you and you are in the Intelligent Buildings Sector then SHAME ON YOU! You need to know how XML, SOAP, and SOA's work. You should be designing your middleware around services not around integrations. Services are flexible, and you can share standard API/XML Schemas with anyone. Integrations are one off tables waiting for a design engineer to get hit by a bus....
- Big Data Technologies- As we move to smart systems in buildings the velocity and format of data will become more rapid and more unstructured. This will require the use of Big Data systems like Hadoop to store the massive amount of vital signs that a living breathing building will create. The days of SQL for everything will not be gone, but rather will be augmented by Big Data. The analytics platforms of the future, cannot effectively use a SQL database on large scale, multi-million point systems.
- IoT, Cloud Architecture, Virtualization- The name of the game is divesting of expensive capital assets and owning technology as a Service. By web enabling systems, and hosting your services and infrastructure, you create the ability to shed operational costs and rapidly expand and contract based on profit, costs, regulations, ect.
I hope you had as much fun reading this as I did writing it. Give me your feedback, how do you use SQL on a daily basis.
What is your readiness on the four subjects I mentioned in the closing?
What technology do you think the industry should keep an eye on?
Let me know in the comments below!
