topic Re: Using OSISOFT PI ODBC driver to access data in Data Management Questions

Using OSISOFT PI ODBC driver to access data

CarstenVock — Tue, 29 May 2012 08:32:17 GMT

Hello,

I´m currently working on a Project that needs to access data from a OSISoft PI Database that holds realtime waterdata.
I want to join this data with Objects in my SDE and create another layer from this data. This layer I want to use to visualize data on the map in a WMS.
I´ve managed to access the Database and I can see the tables and the columns in the Table.
I just can´t see the data itself.

Does someone know if this might be an indicator that the database driver does not work in ArcMap?
I used a database driver called "PI OLEDB Enterprise"

Thank you for help and any suggestions on this!

best regards
Carsten

Re: Using OSISOFT PI ODBC driver to access data

VinceAngelo — Wed, 30 May 2012 11:18:03 GMT

I haven't used this particular product, but not seeing data is usually an issue with the client-server
configuration. You'll probably want to make sure the OLEDB driver works outside of ArcGIS before
you try to figure out why it's not working within ArcGIS.

- V

Re: Using OSISOFT PI ODBC driver to access data

CarstenVock — Wed, 30 May 2012 14:24:39 GMT

Hello,

thanks for your reply.
I tried to view the data with 1) the native OsiSoft PI Software to access the data from this client, which worked.
I also used Excel to access the data which also worked.
My guess is that something with the driver is not working together with ESRI. Any suggestions what I could try also or where to look for the problem?

Thank you

Carsten

Re: Using OSISOFT PI ODBC driver to access data

VinceAngelo — Wed, 30 May 2012 17:27:37 GMT

This is probably something that should be addressed via a Tech Support incident.

- V

Re: Using OSISOFT PI ODBC driver to access data

StevePilon — Fri, 01 Jun 2012 14:04:09 GMT

Hi, I work at OSIsoft and I am the Product Manager for the PI OLEDB Enterprise provider you are talking about. We had a similar question posed on our own discussion forums (on the OSIsoft Virtual Campus, or vCampus), so I tried it myself and I could indeed reproduce.

When I enabled logging on the PI OLEDB Enterprise side, however, I discovered a few strange behaviors (e.g. fetching the metadatata (the columns) but not the data (the rows), constant reinitialization of the session), so I contacted the ESRI tech support to clear this up. I will try and follow-up here when I get somewhere with them. Did you open a ticket with them already? Did they find anything?

That being said, it is unlikely that you will utilize the tables directly in PI OLEDB Enterprise; your asset structure can be rather large and this can make up for complex SQL queries. Chances are you will rather create a view to wrap up a more complex query - and getting data from views worked for me in the ESRI software. If you are unsure about the kind of queries, views, etc. you should create with PI OLEDB Enterprise, I invite you to post your question on the OSIsoft vCampus - the equivalent to ESRI Development Network, but on the OSIsoft side 😉

Re: Using OSISOFT PI ODBC driver to access data

CarstenVock — Mon, 11 Jun 2012 09:10:54 GMT

Hello Steve,

I work on the same project with the people who created the post on your V-Campus, that´s why there is a similar post on your OSI-Soft forum.

I contacted ESRI Support Germany also about this problem and they said it´s maybe because the length of the textfields is too big for ArcGIS when using an OLE DB connection. The text field has a length of 4000. Fields with this length will be defined as BLOB and not be displayed in ArcMap according to ESRI support.

Do you mean you created a view within or with help of OSISoft programms and use these views in ArcMap?
I don´t want to create a SQL Query in ArcMap to get the data out of the OSI-Soft Tables since I think this should be done with the native tools of OSISoft.
I hope I can connect to these views in ArcMap and join them with X-Y-Coordinate data.

Thanks for your help, we will follow up on this in OSI-Soft Forum how to create these views.

Carsten

Re: Using OSISOFT PI ODBC driver to access data

StevePilon — Tue, 12 Jun 2012 12:56:25 GMT

@Carsten: this is good information (re: the length of text fields). Regarding the views and everything, I just posted a reply on the OSIsoft Virtual Campus. Cheers!

Re: Using OSISOFT PI ODBC driver to access data

CarstenVock — Wed, 20 Jun 2012 07:46:46 GMT

Hello,

I can see all the Values of the view in ArcMap now. I guess the reason was that it had to do with the fact the OsiSoft DB needed a special trust created on the DB-machine for the Client. Values with a Variant Datatype won´t show up though.
I could also establish a join. The Problem is when I join this data within ArcMap via "rightclick on the layer" --> joins and relates, the viewer gets veeeeeery slow.

Using a different way via the Arc Toolbox --> DataManagement Tools-->"Add join". I get an error saying that the table selected is not supported.
Does someone know why this happens in Data Management tools and not with the other way?

Thank you!

Re: Using OSISOFT PI ODBC driver to access data

VinceAngelo — Wed, 20 Jun 2012 10:27:22 GMT

There are two different types of joins. The faster type is done in the database, allowing the optimizer
to choose the most efficient query plan to process multiple "fetch" requests. The slower mechanism
is to have an application generate new queries for each row, fashioning a virtual table the "hard way".

When the data is in different databases, you only have access to the latter solution, which is why most
heterogeneous joins eventually get rearchitected into what Oracle calls a "materialized view", where
the key data in one database is regularly replicated into the other for realtime use.

- V