12 Replies Latest reply on Apr 29, 2016 5:09 PM by TomMcCarthyNeoPLM

    cross apply data.plot

    TomMcCarthyNeoPLM

      Hi All,

       

      I am writing a pretty basic query to return PI data using the PI OLEDB interface.  Here is my SQL.

       

      select eh.path,
             eh.name,
             ea.name,
             pl.value,
             pl.time,
             pl.isgood,
             uom.abbreviation
      from asset.elementhierarchy eh
        inner join elementattribute ea
        on ea.elementid = eh.elementid
        inner join System.UnitOfMeasure.UOM UOM
        on UOM.ID = ea.defaultuomid
        CROSS APPLY Data.Plot (ea.ID, '2016-Apr-23', '2016-Apr-24', '300') pl
      
      where eh.path = '\Company\CompanySite\' and
      eh.name = 'pipoints' and
      ea.name = '100090R011TIC04.PV'
      option (Force Order)
      

       

      I have been able to cross apply the interpolate range aggregate. but when I try the same thing with Data.Plot, I get this.

       

      [Plot] Overload resolution failed for (Guid, AnsiString, AnsiString, AnsiString) argument(s).

       

      Can someone explain this?

       

      Also, if my PI server if separated from my AF server, and the connection is down to the PI server, what type of error will I see when executing this query?  This is one scenario I cannot test in my lab, as i have a single server.

       

      Regards

       

      Tom

        • Re: cross apply data.plot
          asorokina

          Hi Tom, the data type of the IntervalCount argument is Int32. Removing the single quotes for the last argument should do the trick for you.

           

          Edit: For your second question: if the PI Server is down, you will see the following PI connection error [-10758] Failed to create remote connection.

            • Re: cross apply data.plot
              TomMcCarthyNeoPLM

              Hi Anna,

               

               

              That worked.  Thanks for the fast response.  Can you answer these other questions.

               

               

              If the pi server is seperated feom the af server, and connectivity is lost between the two servers, what would the query return.

               

               

              Again if the two servers are seperated and are in fact in different time zones, does the communication between the two servers take care of the time zone offsets.

               

               

              Regards

               

               

              Tom

               

               

              Sent from Outlook Mobile

                • Re: cross apply data.plot
                  asorokina

                  For the connection question, the AF Server and PI Server never talk directly to each other. Client software connects directly, and seamlessly, to both. PI OLEDB Enterprise, for example,  will first connect to the AF Server to load AF Element metadata (which often includes references to PI Points, i.e. as ConfigStrings of attributes using the PI Point Data Reference). Then, the client makes calls to the PI Server for data retrieval.

                  Here are a couple of test cases I ran to answer your general question of what happens when the PI Server is unreachable:

                  • Disabled network adapter on the PI Server host

                  Query completes with error:

                  [OSIsoft.AFSDK] [-10758] Failed to create remote connection.

                  • Shut down PI Server host and PI Server itself (i.e. using pisrvstop batch)

                  Query completes with error:

                  [OSIsoft.AFSDK] [-10758] Failed to create remote connection.

                  Note that if you enable the option to embed errors (e.g. option (Force Order, Embed Errors)), the query will complete successfully and any errors will be written to the value columns. Like this:

                   

                  To your question of time zones, PI OLEDB Enterprise operates exclusively in local client time: if your query contains timestamps, they must be in local time; if your query result contains timestamps, they will be in local time. Internally, the PI Server stores data in UTC.

                  From the user guide:

                  PI OLEDB Ent. query should be in local time,  retrieved data is in local time.

                  PI OLEDB Enterprise supports local time zone timestamps only, so that:

                  · Time references used in query strings must use local time

                  · Timestamps returned are in local time

                   

                  However, if you're hoping to use UTC, good news: The upcoming PI OLEDB Enterprise 2016 release adds support of a UTC mode. For more information see the following tech support alert:

                  https://techsupport.osisoft.com/Troubleshooting/Alerts/AL00294