• State Verified Answer
  • Replies 1 reply
  • Subscribers 2 subscribers
  • Views 190 views
  • Users 0 members are here
Options
Related

How can I capture queries against PI SQL Data Access Server (RTQP Engine)? How can I use query folding in Power Query with PI SQL Client?

Kenneth Barber

Currently, I have Power Query queries in Excel that send SQL queries from ".sql" files to PI SQL Data Access Server (RTQP Engine). However, this approach causes a "Native Database Query" warning window to appear when the SQL query is first used after any changes are made to it, and I would like the users to not have to deal with this window. The solution would be to convert my SQL queries to pure Power Query code and let Power Query perform query folding, which will convert the Power Query query to an SQL query for the database (SQL Data Access Server (RTQP Engine) in this case) to run.

 

I set up a basic 2-step test query using only Power Query code. However, when I right-clicked on the last step of the query in the Power Query Editor, the "View Native Query" option was greyed out, which means that I cannot tell if the query was folded.

 

Therefore, I am looking for a way to tell which queries are sent to the PI SQL Data Access Server (RTQP Engine). It does not matter to me if the capture of the queries occurs on the client end or on the server end. If anyone has any experience with using query folding with PI SQL Data Access Server (RTQP Engine), then I would appreciate any advice that you can give me. For example, which of the 3 variants of PI SQL Client (OLEDB, ODBC, or JDBC) is best suited for query folding?

 

Thanks in advance!

Parents
  • 0

    AVEVA tech support told me a method to capture queries on the PI SQL Data Access Server (RTQP Engine) end: https://docs.aveva.com/bundle/pi-sql-data-access-server-rtqp-engine/page/1016124.html.

     

    Excel's Power Query supports OLEDB and ODBC but not JDBC. I found that using ODBC led to shorter Power Query queries since the tables are less nested, but this does not necessarily mean that ODBC is better than OLEDB overall for query folding.

     

    Unfortunately, the queries generated by query folding are not optimized for PI SQL Data Access Server (RTQP Engine) and can even hog the PI Data Archive and cause issues with other programs trying to connect to the PI Data Archive. I suspect that a query is sent to the PI Data Archive without considering the joins with and filter conditions on the PI AF tables, which leads to data from all tags being retrieved, only to possibly be filtered if these constraints from the PI AF tables are later considered.

     

    Also, keeping separate ".sql" files means that you can still edit and test your query in PI SQL Commander Lite if you want to. With everything considered, I will stick with my current approach and just have the users deal with the "Native Database Query" window. If anyone gets further than I did with query folding against PI SQL Client, I would still be interested in hearing what you tried, what you learned, and what worked for you.

Reply
  • 0

    AVEVA tech support told me a method to capture queries on the PI SQL Data Access Server (RTQP Engine) end: https://docs.aveva.com/bundle/pi-sql-data-access-server-rtqp-engine/page/1016124.html.

     

    Excel's Power Query supports OLEDB and ODBC but not JDBC. I found that using ODBC led to shorter Power Query queries since the tables are less nested, but this does not necessarily mean that ODBC is better than OLEDB overall for query folding.

     

    Unfortunately, the queries generated by query folding are not optimized for PI SQL Data Access Server (RTQP Engine) and can even hog the PI Data Archive and cause issues with other programs trying to connect to the PI Data Archive. I suspect that a query is sent to the PI Data Archive without considering the joins with and filter conditions on the PI AF tables, which leads to data from all tags being retrieved, only to possibly be filtered if these constraints from the PI AF tables are later considered.

     

    Also, keeping separate ".sql" files means that you can still edit and test your query in PI SQL Commander Lite if you want to. With everything considered, I will stick with my current approach and just have the users deal with the "Native Database Query" window. If anyone gets further than I did with query folding against PI SQL Client, I would still be interested in hearing what you tried, what you learned, and what worked for you.

Children
No Data