4 Replies Latest reply on Jan 17, 2017 1:21 PM by Roger Palmen

    Trend using SQL Data

    AnimeshK

      Hi All,

      Is it possible to generate trend for SQL data.

      I want to fetch data from SQL using AF and show trend for it on PB display/PI Coresight. Without using PI Tag.

      I have Data in SQL server, for which I want to show trend or charts on PI Coresight or Processbook Display.

        • Re: Trend using SQL Data
          gregor

          Hello Animesh,

           

          What you like to do is possible but we recommend not making heavy use of it because there's an impact on performance. If the data in SQL you like to trend is showing multiple thousands of rows for a single data item, I suggest to replicate the data to PI Points.

           

          Please refer to Table lookup data references in PI Server 2012 R2 documentation.

          1 of 1 people found this helpful
            • Re: Trend using SQL Data
              AnimeshK

              Thanks Gregor,

              Thanks for your valuable reply.

              There will be data for few months, and number of rows will not be more than 10k, for around 10 to 15 items.

              Please Suggest.

              Thanks

                • Re: Trend using SQL Data
                  gregor

                  Hello Animesh,

                   

                  The link which I shared in my previous post indeed goes to the PI Server 2016 R2 documentation rather than the 2012 R2 documentation as stated above. Please accept my apologies.

                   

                  I recall 10k was mentioned as the limit for rows in AFTables a few years ago but I believe to remember that Stephen Kwan mentioned the limit had changed because the performance was improved. I didn't find any limits mentioned in the documentation of the current version. By the way, what PI AF Server / PI AF Client version are you running?

                   

                  I however suggest to read through the existing documentation carefully, there exists a dedicated chapter which deals with Linked and Imported tables from SQL Server. There's a big difference already between creating an AFTable as linked or as imported table. If I understand your use case properly, you likely want to go with the linked table connection in order to receive updates. Because you are dealing with ~ 15 data items only, it may be wise to create a separate linked table for each data item to reduce the amount of rows. Also think about a reasonable cache interval.

                  • Re: Trend using SQL Data
                    Roger Palmen

                    There used to be a 'soft' 10K limit in the 'old' AF, but in newer versions the evaluation of filter criteria was moved from the client to the AF server. Before AF server 2014 (2.6.x) all data in the table was pulled to the client and filtered and sorted there. Starting 2014 (2.6.0.5843 or higher), table connections and parametrized queries were introduced:

                     

                    From the release notes: https://techsupport.osisoft.com/Viewer/File/546bcb34-7c3a-4776-ada6-98962e8a843e#_Toc384048389

                     

                    13573

                    AF Tables along with the AF Table Lookup Data Reference now support parameterized table queries which allow for more efficient querying of external database systems.

                    13580

                    A new object, AFTableConnections, has been added to the AF SDK.  This allows a single connection definition to be reused by multiple tables.  Both client and server updates are required to fully utilize this feature.  Older clients will be able to read AFTables that utilize table connections defined in the server.

                     

                    So i think, if your scenario is limited in size, it might work. Testing will prove if that works in your case.

                    1 of 1 people found this helpful