8 Replies Latest reply on Jun 18, 2012 1:15 PM by Robin Verhoek

    GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver

    Robin Verhoek



      I´m trying to connect to the PI System using the OLEDB Enterprise driver in my GIS Software (ArcCatalog).


      The Connection can be established but I can not see any data in the tables. The other driver (ODBC) works only with the PI Server and this data can be accessed.


      Has anyone an idea what might be the problem?


      Generally what are the Pro´s and Con´s against a solution which uses a WebService to access the PI System and puts the data into the GIS Database so it is directly attached to the GIS data, instead of using the Connection via the PI ODBC Enterprise driver in the GIS(which at the moment doesn´t seem to work)?


      Thanks for suggestions answers and taking some time to help.


      Best regards



        • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver

          @Robin: I have been working with ESRI on a Proof of Concept, hoping to make a White Paper out of it and make it available on the vCampus Library. We are not complete with what we wanted to achieve so I am not ready to share that White Paper, but I can certainly try and help you here.


          First of all, let's try and troubleshoot why you don't see any data in the tables... what are you trying to achieve? What kind of queries are you executing against PI OLEDB Enterprise?


          At a first glance, I wouldn't recommend using a separate web service that does data transfers. Since the ESRI products (e.g. ArcCatalog) already have mechanisms to reach out to relational data sources (such as the PI System via PI OLEDB Enterprise), this would do an additional, unecessary ETL step and would mean you have an additional piece of code to maintain and support.


          Unless you want to do things with timeseries data, which the ESRI products are not geared to support...

          • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver

            P.S.: there is no such product as the "PI ODBC Enterprise driver". Currently we only have the classic PI ODBC Driver, much like the classic PI OLEDB Provider, which both only talk to the PI Data Archive.


            The PI OLEDB Enterprise provider talks to both the PI Data Archive and the PI Asset Framework (AF). As a side note, we are committed to delivering a next-generation ODBC driver that will do the same, although we haven't officially communicated any timeline on the PI System Roadmap yet.

              • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver
                Robin Verhoek



                thank you for your support. I´ve attached a screenshot of what I can see in the database using the PI OLEDB Enterprise provider.


                So I´m not putting in any statement yet. ArcMap probably does this internally to query the database and show the data in the table which you can see in the picture. Do you have any experience in making data accessible to ArcGIS/ArcCatalog?


                also: the PI OLE DB Provider works fine. I can see the data in the tables.

                My pro to the Web Service is that the Performance of this connection might be a bit slow. Also I´m not quite sure if I can join the data from the PI System easily with tables from the GIS since the data in PI has also to be joined. Usually I prefere the way to have all data in direct access to the GIS System.

                  • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver

                    I am seeing really strange behaviour here too... I think there may be a problem with ArcCatalog, because I can successfully get data in ArcMap.


                    I enabled logging on the PI OLEDB Enterprise side and I can see 2 weird things:


                    1. ArcCatalog appears to connect/disconnect every few milliseconds (!)


                    2. You can clearly see the "SELECT * FROM Asset.Elemement" statement being executed (and, surprisingly, an additional "SELECT ID FROM Asset.Element" statement!)... and the log shows that a certain number of rows was returned by PI OLEDB Enterprise. I'll work with the OLEDB team to investigate this further, but we might have to work this out with ESRI's tech support at some point.

                      • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver
                        Robin Verhoek

                        thank you for taking care of that.
                        We are trying to connect to Tables in the OsiSoft System. How did you create layers from that Data? Did you use a join to add dynamic data into the GIS Database which holds the location, or did you store the location in PI as well and just import that table somehow? Would be interesting to find out about your approach visualizing the PI data because I can not even see the data in ArcMap?! What I can do is exporting the table into a file Geodatabase but this doesn´t help me much because I need to visualize dynamic data from the table. It would be really helpful if you had some more detail for this subject. Thank you!



                          • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver

                            @Robin: sorry for the delayed response here...


                            I am still trying to work out the ArcCatalog issues with ESRI's Technical Support, but I can certainly let you know what worked for me. What is really strange is that ArcCatalog (and ArcMap) can open some of the default tables in PI OLEDB Enterprise (e.g. Data.Snapshot) but not some others (e.g. Asset.Element).


                            Either way I don't think you want to open up the tables themselves on the ArcMap side, as your asset structure (and the underlying real-time data) can be fairly large, and this could make up for rather complex queries. Instead, you probably want to create a view on the PI OLEDB Enteprise side and then invoke this from ArcMap - this works like a charm in both ArcCatalog and ArcMap.


                            In my case, I used the Transpose Function Wizard in PI SQL Commander to create a Transpose Function for a particular type of asset and its accompanying Transpose Function Table. For the sake of example, say the Element Template I used was for a Well, so the Transpose Function is "TransposeSnapshot_Well" and its accompanying Transpose Function Table is "ft_TransposeSnapshot_Well". Then I just executed the predefined query for that new Transpose Function Table, which looks something like this:

                            SELECT eh.Path + eh.Name Element, tc.*
                            FROM [AFDatabase].[Asset].[ElementHierarchy] eh
                            INNER JOIN [MRA].[DataT].[ft_TransposeSnapshot_Well] tc
                             ON tc.ElementID = eh.ElementID
                            WHERE eh.Path = N'\'

                            And then I created a new View in PI SQL Commander, where I selected only the columns I wanted (as opposed to tc.*, as in the above query), named them the way I wanted, and added appropriate WHERE conditions. And this newly created View, which I called "WellStatus" is what I use in ArcMap to drive a new layer with dynamic symbology.


                             A few head's ups:

                            1. ArcCatalog and ArcMap do not support column names with spaces in them, so name your columns carefully when creating your view.
                            2. They also do not the SQL_Variant data type, so make to clear the 'Values as VARIANT' option in the Transpose Function Wizard.
                            3. This is all good and dandy for snapshot values, but I am still trying to work out some issues where ArcMap cannot handle timeseries data coming from an OLE DB data source (i.e. trying to enable the Time aspect on a layer and then use functions like the Time Slider...)

                            I am currently working on a White Paper to provide that recipe in a more organized fashion, and these are the kinds of things I want to put in there. Of course I won't be able to make that White Paper available here until these little qwirks are resolved... In the meantime, let me know what you find out; I'd be happy to incorporate your findings in this "collaborative" paper ;)

                              • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver
                                Robin Verhoek

                                Hello Steve,


                                we found the issue with the connection problem I guess, at least the tables now show up in ArcCatalog. It´s slow but it shows the data out of the PI System.


                                We had to add another PI Trust for the machine connecting to the PI Database. Strangely some views were working and others not without a PI Trust.


                                Thanks for the hint with the variant datatype, we also had that issue.



                                  • Re: GIS (ESRI) Connection to PI using the PI OLEDB Enterprise driver
                                    Robin Verhoek

                                    Hello once again,


                                    I want to join the GIS Data now with the View created in Pi. I can see all the Values of the view in ArcMap. I can also join the data. 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. Do you know if there are restrictions to the PI view that doesn´t allow joins through OLE DB Connections?


                                    Thank you.