A graphical "Query Builder" for PI SQL Commander?

Discussion created by spilon on Nov 20, 2012
Latest reply on Mar 5, 2013 by Bannikov

If you've ever used PI OLEDB Enterprise or PI JDBC, you know it is designed with in a very traditional relational database approach: a fairly large number of atomic tables with relationships. Additionally, PI OLEDB Enterprise 2012  and PI JDBC 2012 are coming up soon and they bring support for PI Event Frames – which means additional tables and relationships. As a result of that normalized design, the SQL queries that can be built generally involve multiple tables and can get rather complex. That also means there are often multiple ways to write a query that yields the same results, and finding the most efficient way to write a given query can be challenging for people starting to use PI OLEDB Enterprise or PI JDBC.


To help with the writing of the SQL queries in the most efficient way possible, we are considering creating a graphical "query builder" as part of the PI SQL Commander utility. This query builder would not be constructed like other generic query tools that already exist (which are targeted for SQL experts) – instead, it would be use case driven and targeted for PI System experts.


The way we envision this is that queries would be designed graphically, by simply drag & dropping objects onto a canvas. These objects would represent queryable data (e.g. AF Elements, AF Attributes, PI Event Frames) and the builder would find and establish relationships (i.e. JOINs) for you; of course these relationships could be modified as needed. This tool would allow you to easily apply filters (i.e. WHERE) at the object level or globally, and give you the ability to select desired columns and create new ones (e.g. some kind of expression builder, using functions and formulas). Of course you would be able to save/persist queries (or parts of them) and we would provide a number of predefined examples revolving around common use cases.


As mentioned before, there are cases where multiple SQL queries would yield the same results – this query builder will strive to generate the most performant queries possible. At any point in time the resulting SQL queries can be visualized, executed or copied to be used in other tools. Optionally, the resulting query can be wrapped within a View or Table-Valued Function (TVF), so that its usage gets greatly simplified.


At this point we need your feedback: would such an add-on be useful? Do you think there's another, better way to tackle the query complexity/performance problem? Are there any other enhancements you can think of for PI SQL Commander, which would make your life easier when it comes to writing SQL Queries for the PI System?


Please cast your vote here and leverage this discussion thread to share your ideas and make sure you come see us if you are attending vCampus Live! 2012: members of the PI SQL Data Access team will be present at the event, including at the Developers Lounge throughout the conference!