9 Replies Latest reply on May 26, 2014 4:22 PM by benjamin.truquet

    Populate a table using PI TAGs

    benjamin.truquet

      Hi,

       

      I'm using the latest version of AF (2014, ABACUS) in order to calculate on different elements a certain volume. What I would like now is to be able, from a parent element, to aggregate those volumes by attributes. For exemple I would like to calculate the sum of "volumes" where the element's attribute "Zone" is equal to North.

       

      What I would like to do is to store all of my volumes, along with their element's attributes in a table. Then from the parent element create a Table lookup allowing me to use SQL commands to calculate aggregated volumes.

       

      But the big question is, can we populate a Table from within the PI System ? I would like to write my Tag and attribute values into a table. Of course I would like it to refresh every time the tag value changes.

       

      Is this possible !?  If yes how !? I tried to find the answer in the AF users guide, but .. nada ...

       

      Thanks in advance for your help  !!

        • Re: Populate a table using PI TAGs
          xwang

          Hi,

           

          I am afraid it might not be possible to refresh a Table linked / imported to PI AF Server.  The Table refresh is just triggered by time..

           

          Xi Wang

           

          vCampus team

            • Re: Populate a table using PI TAGs

              How big (how many rows) is this external table?  And how often would you update any Tag? every hour? every 15min?

                • Re: Populate a table using PI TAGs
                  Marcos Vainer Loeff

                  Hello Benjamin,

                   

                  We need to investigate first if storing the values on External Tables is the best solution, based on the fact you are interested on the final sum, is that correct?

                   

                  Here are some suggestions for you:

                   

                  1. PI Asset Based Analytics (Abacus) – it has the Roll-Up Analysis Rule that sums values of an attribute of child elements. The problem is that it only sums the first level of child elements on this first release. Nevertheless, there are workarounds for that.

                   

                  2. Develop a custom data reference to send the attributes values to the table. This cannot be done with the current Table Lookup DR. Please refer to the Custom Data Reference white paper in order to learn how to achieve this goal.  The idea is to copy the values from another attribute and send them to the table. This way, the values will be stored on the PI Data Archive and then they will be sent to the table only to calculate them sum.

                   

                  I prefer the first option since it is simpler to implement.

                   

                  Hope this helps!!

                    • Re: Populate a table using PI TAGs
                      Marcos Vainer Loeff

                      Hello,

                       

                      Just to clarify something. The current rollup functionality is based on a selection criteria and only goes down 1 element level.  It is unlikely that we would ever implement a feature to rollup all attribute values for all child elements. Although it is possible to achieve this goal through a custom data reference, the performance might be slow.

                        • Re: Populate a table using PI TAGs
                          skwan

                          Benjamin:

                           

                          You may want to consider doing the following:

                          • Calculate your volume using AF 2014 and asset based analytics, store the results as PI Points
                          • Rollup your volumes using asset based analytics, store the results as PI Points
                          • Once you have your results stored as PI Points, use one or more of the following to get your results, depending on your needs:
                            • Simple PI Point Data Reference
                            • Formula Data Reference acting on the attributes/PI Points
                            • Use PI OLEDB Enterprise to extract out the results from the PI Points either directly using SQL queries or dump all the values into Excel and use PowerPivot or similar tools

                          Hope this helps.

                            • Re: Populate a table using PI TAGs
                              benjamin.truquet

                              @Marcos & @Steve : The problem with analytics, specifically rollups is :

                               

                                                              - It is impossible to reach more than 1 element level

                               

                                                              - The criteria is based on the element's name and not the element's value

                               

                              Here's an example of my model :

                               

                              1- Global volume

                               

                                    Attributes :

                               

                                           - Region1 global Volume

                               

                                           - Region2 global Volume

                               

                                           - Regionx global Volume

                               

                                           - IntermediateRegion1 global Volume

                               

                                           - IntermediateRegion2 global Volume

                               

                                           - IntermediateRegionx global Volume

                               

                                           - Zone1 global Volume            

                               

                                           - Zone2 global Volume

                               

                                           - Zonex global Volume

                               

                                           - Area1 global Volume

                               

                                           - Area2 global Volume

                               

                                           - Areax global Volume

                               

                                    Child Elements :

                               

                                           2- IntermediateRegion1

                               

                                                     Child Elements :

                               

                                                                   3- Bloc1        

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v1

                               

                                                                   3- Bloc2      

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v2

                               

                              .                                      3- Bloc3      

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v3

                               

                                           2- IntermediateRegion2

                               

                                                     Child Elements :

                               

                                                                   3- Bloc4        

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v4

                               

                                                                   3- Bloc5      

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v5

                               

                                                                   3- Bloc6      

                               

                                                                          Attributes :

                               

                                                                                    - Region = (1 or 2 or...x)

                               

                                                                                    - Zone = (1 or 2 or ...x)

                               

                                                                                    - Area = (1 or 2 or ...x)

                               

                                                                                    - Volume = v6

                               

                                                     .......

                               

                                            2- IntermediateRegionx

                               

                              The needed result is :

                               

                              Region1 global Volume = SUM of all "Volume" where Region = 1 and ect ...

                               

                              Region2 global Volume = SUM of all "Volume" where Region = 2 and ect ...

                               

                              One more thing, I need those volume to be stored into Tags !

                               

                              Thank you in advance for your help/suggestions,

                        • Re: Populate a table using PI TAGs
                          benjamin.truquet

                          @Gopal : Approximately 100 rows, 10 columns with a 10 min update.

                        • Re: Populate a table using PI TAGs
                          benjamin.truquet

                          @Xi Wang : This isn't an issue, I can update my table periodically since my volumes are calculated every 10 min.