How to set up ODBC connection to SQL DAS?

Hello Olav​ ,

I believe that you are trying to connect PI ODBC Driver (indicated by "PI OLEDB Provider" Type) against PI SQL Data Access Server (RTQP Engine) (indicated by Net.Tcp via port 5465). This will not work because these products are 2 different PI SQL generations.

If you like to stick with PI ODBC Driver, you need to install PI SQL Data Access Server (OLE DB) as the server component.

If you like to stick with PI SQL Data Access Server (RTQP Engine) which is what I would recommend based on the information shared, please use PI SQL Client (ODBC).

One extra question though. I see no connection options for the data archive. Does the PI Client ODBC driver still allow direct queries towards the PI Data archive tables, or will I be forced to go via the AF server?

Hello Olav​ ,

Thank you for selecting my answer as best answer!

PI SQL Data Access Server (RTQP Engine) is Asset Centric only. Another limitation is that access is read-only, meaning UPDATE, DELETE and INSERT statements are not supported. On the other hand, RTQP Engine comes with a simplified schema, performs better and does not require setting up Kerberos Delegation for the access to AF Server and PI Data Archive.

If you need write access to time series data, you will have to go by the previous PI SQL generation and use PI ODBC Driver, PI SQL Data Access Server (OLE DB) with PI OLEDB Provider.

Please take a look at the PI SQL Playbooks for more details and keep asking if you have more questions:

• Playbook - PI SQL Client and RTQP Engine
• Playbook - PI SQL DAS (OLEDB)
• Playbook - PI OLEDB Provider

Hi. I only need read access through this connection, so that is fine. I don't need write access to time series. But can you pull data from the PI server which does not have any elements/attributes defined in the AF server? We don't have a very good/extensive AF structure yet. Or does anything you want to query have to be defined as an AF attribute?

Hello Olav​ ,

You do not have direct access to PI Points through RTQP Engine. To read time series data from PI Points, those have to be mapped to AF Attributes as you suggest. Please accept my apologies for not being more clear on this before. I was assuming to answer your question by saying "Asset centric only".

We believe that there is a lot of value in utilizing PI Asset Framework and to create a Hierarchy which describes equipment as objects (Elements) with Properties (Attributes) and to use Templates wherever possible. Sometimes different user groups have different views to look at facilities / equipment and we see customers creating multiple AF Databases to serve the desires of those user groups.

If you do not have the PI Points you are interested in mapped with AF Attributes, you may want to create an AF Database with a very flat AF structure i.e. a root level Element representing the PI Data Archive host and Attributes underneath, one for each PI Point. This should at least be an option with 10~20k PI Points and by utilizing the PI Builder add-in to MS Excel, creating the entire AF Structure should be done in 30~60 minutes.

Hello Olav​ ,

You do not have direct access to PI Points through RTQP Engine. To read time series data from PI Points, those have to be mapped to AF Attributes as you suggest. Please accept my apologies for not being more clear on this before. I was assuming to answer your question by saying "Asset centric only".

We believe that there is a lot of value in utilizing PI Asset Framework and to create a Hierarchy which describes equipment as objects (Elements) with Properties (Attributes) and to use Templates wherever possible. Sometimes different user groups have different views to look at facilities / equipment and we see customers creating multiple AF Databases to serve the desires of those user groups.

If you do not have the PI Points you are interested in mapped with AF Attributes, you may want to create an AF Database with a very flat AF structure i.e. a root level Element representing the PI Data Archive host and Attributes underneath, one for each PI Point. This should at least be an option with 10~20k PI Points and by utilizing the PI Builder add-in to MS Excel, creating the entire AF Structure should be done in 30~60 minutes.