6 Replies Latest reply on Nov 27, 2015 4:29 PM by Lowreno

    Event trigger of PI analyses is not updating correctly PI TAG value according a resultset of table link

    Lowreno

      Dear colleagues,

       

            So, i am having a problem to update a PI point  based on a resultset of a table link accessing a PostGreSQL.

            Basically, I have two situations where the first one, I have success and the second one, the values are just updated with the same value. Both are using the same architecture/idea (it means, same odbc connection, same Template, etc...). The "big" difference between cases is that:

       

            The first one is running the following SQL into PostGre:

            The second one is running:    

       

           As a "test" (which is returning a simple dataset), the function "CalcVazaoVertida" inside PostGreSQL is listed below:

       

      CREATE OR REPLACE FUNCTION CalcVazaoVertida(pIdReservoir int, pNaguaValue numeric)

      RETURNS TABLE(reservoirid bigint, valueoverflow numeric) AS

      $$

      BEGIN

         DROP TABLE IF EXISTS T_RETURN;

         CREATE TEMP TABLE T_RETURN(reservoirid bigint, valueoverflow numeric);

         INSERT INTO T_RETURN VALUES (pIdReservoir, date_part('second', current_time));  --just for test purposes...

       

         RETURN QUERY SELECT * FROM T_RETURN;    

      END;

      $$

          LANGUAGE plpgsql;

       

          The result set are two fields and just one row:  The reservoirid and the seconds of 'current_time' in double value as shown below:

       

          Below, I'm listing much more details between the cases where: the first one is FINE, and the second one is not updating the PI Point as expected:

       

       

           Please, if someone have any idea WHY i am running into this problem and have wonderful  tips to solve it, I'll be very happy ;-)

           If needed, I can provide more details...

       

           Thanks in advance,

       

      Lowreno.

          • Re: Event trigger of PI analyses is not updating correctly PI TAG value according a resultset of table link
            Lowreno

            Oppsss SORRY!... I thought the pics could be downloaded and visualized with success... but now, I realize that it's not possible....

            So, i am pasting the 2 pics in blocks once I cannot attach the original files and the option "original size" is not working for the full pictures ...

            I hope to have them readable here now ;-)

            Thanks!

             

            =================================================================================================================================================================================================

            =================================================================================================================================================================================================

             

             

             

            =================================================================================================================================================================================================

              • Re: Event trigger of PI analyses is not updating correctly PI TAG value according a resultset of table link
                Mike Zboray

                Ah ok now I see.

                 

                This is just a hypothesis that I can't really test. What you are seeing sounds like the Table DR is reporting that it supports the AF data pipe but that pipe is not actually producing new values, which would make sense since it is actually calling a stored procedure.

                 

                The AF data pipe is generalization of the PISDK event pipe concept for any data source. It provides a kind of "push" mechanism for consumers. (OK that's not quite true. At the SDK level the consumer still has to retrieve data pipe events, so its still kind of a pull but only for deltas and it happens in bulk. AFSDK wraps most of this so it looks like a push for most part.) The analysis service uses this mechanism for event triggered analyses. However most people don't realize that the data pipe is used, when available, for inputs to periodic calculations as well. That way it doesn't have to actually hit every data source for each evaluation, but it would for inputs that don't support the AF data pipe. My understanding of Table is that it was written to support the data pipe with AF 2.6. It sounds like it cannot support data pipe for this scenario but reports that it does.

                 

                To verify this some of this, hold shift and click on settings for the table DR. This should popup the property page and under SupportedDataMethods check if "DataPipe" is listed. Also try right clicking the attribute and selecting Time Series and go to the Data Pipe tab. Are there differences between the Table DRs for the working and not working cases. I expect that in the working case the Data Pipe tab has updates but in the not working case it does not. My theory is that "Evaluate" does not use the data pipe it just retrieves values at specific timestamps so it is always getting new values, but when running in the Analysis Service no new values are inserted into the pipe/data cache and the same output is produced every time.

            • Re: Event trigger of PI analyses is not updating correctly PI TAG value according a resultset of table link
              Lowreno

              Hello Mike,

               

              So, popup for working case:

               

              Popup for non-working case:

               

               

                 The supportedDataMethods field is showing the the following difference ( and I really don't know why once i built them similarly):

               

              working case:

              InterpolatedValue, InterpolatedValues, InterpolatedValuesAtTimes, RecordedValue, RecordedValues, RecordedValuesByCount, PlotValues, Summary, Summaries, DataPipe

               

              non-working case:

              InterpolatedValue, InterpolatedValues, InterpolatedValuesAtTimes, RecordedValue, RecordedValues, RecordedValuesByCount, PlotValues, Summary, Summaries, DataPipe, Future

               

               

                   So, the time series data is showing:

               

              working case:

              non-working case:

               

                 So, the value for non-working case is updated too.. (in fact, if I just refresh the attributes they are updated according - both cases). The problem on non-working case seems to be the value that is not updated to the PI Point. Looks like the value is requested and updated as expected -  IMHO, the execution of procedure in PostGreSQL looks like pretty fine (in fact, it is returning a similar dataset as a simple query. The ODBC Layer is 'guy' which is delivering the whole package as dataset  that AF table link is expecting ).

               

                  BTW:  I  just realized one "strange" thing:  The field "Time Stamp" for non-working case is showing "01/01/1970 00:00:00"

               

                  So, any thought/tip  with these inputs ?

               

                  Thank you very much!

               

              Lowreno.