4 Replies Latest reply on May 28, 2015 9:31 AM by Eugene Lee

    What's the best approach to find elements by attribute value? Can the indexer help?

    Andrew Pong

      Given the structure below, is there an efficient manner to find one or more elements based on the value of one of its attributes?

       

      For example, return the WebID of one or more elements with a CustomerID_Attribute value equal to foo, or elements that have an InstallDate_Attribute later than a certain date?

       

      Elements

           Asset00001_Element

                CustomerID_Attribute (value: foo)

                InstallDate_Attribute (value: 12/30/2014)

           Asset00001_Element

                CustomerID_Attribute (value: bar)

                InstallDate_Attribute (value: 01/15/2015)

           Asset00001_Element

                CustomerID_Attribute (value: foobar)

                InstallDate_Attribute (value: 04/20/2015)

           Asset00001_Element

                CustomerID_Attribute (value: barfoo)

                InstallDate_Attribute (value: 05/25/2015)

        • Re: What's the best approach to find elements by attribute value? Can the indexer help?
          bshang

          Hi Andrew,

           

          I don't believe there is a native PI Web API call to search elements by attribute value.

           

          Looking at GET assetdatabases/{webID}/elements, there is no parameter for an attribute value query.

           

          Looking at GET search/query, I also do not see any search fields for attribute value.

           

          It is possible to do this in AF SDK, using AFElement.FindElementsByAttribute Method

          This is the method used by PI Builder to retrieve elements by attribute value.

           

          Some non-programmatic ways to speed this up is to first ensure that all of the elements above belong to element templates. Also, the attributes should be indexed configuration items.

           

          Using PI Web API only, I imagine one way is to used indexed search to grab the list of attribute webID's, check the value for each, and if the criteria matches, then grab the elements webID. There are other ways as well but the which way is fastest would depend on other factors like number of elements/attributes, depth of hierarchy, physical location of machines, etc. An advanced way to do this would be to chunk on the client side and issue the attribute value queries in parallel, but this would need additional testing. Would be interested to see what others have in mind.

          1 of 1 people found this helpful
            • Re: What's the best approach to find elements by attribute value? Can the indexer help?
              Andrew Pong

              Thanks Barry.

               

              When you say attributes should be indexed configuration items, does that index the attributes value, and the index is updated when the value is changed?

               

              If the indexed search contains attribute values, would iterating through 100,000 attribute values be prohibitively slow for the Web API? How about 1,000,000 values?

                • Re: What's the best approach to find elements by attribute value? Can the indexer help?
                  bshang

                  Hi Andrew,

                   

                  Yes, marking the attribute as "indexed" in PSE will index the attribute's value in a separate PIFD table. Under the hood, it creates a row for that attribute in one of the AFElementAttributeValueIndex* tables, so the underlying SQL query will be much faster (tree search vs. looping through a list). Technically, we don't need to mark the attribute as a "configuration item". Doing so requires us to check in value changes so it's one way to ensure static values don't get accidentally changed without checking in.

                   

                  I guess I should differentiate between indexing an AF Attribute vs. PI Indexed Search. The former will speed up the AF SDK static method AFElement.FindElementsByAttribute, where an AttributeValueQuery[] (i.e. 'attribute' > 100) is passed in as an argument. PI Indexed Search is newer, indexes AF object properties (name, description, category, template, etc.) and PI Points, and does not use an auxiliary SQL table to store the index. It also doesn't require the AF Attribute to be marked as indexed, but it does not index the attribute's value. The PI Indexed Search is accessed via PI Web API. As far as I know, there is currently not a way to perform equivalent AF SDK FindElementsByAttribute queries via PI Web API, which is what we probably need.

                   

                  A PI Indexed Search using GET search/query can be used to get the list of all attributes, such as CustomerID_Attribute, but the value will have to retrieved in another query. For 100,000+ attributes, this would probably be too slow with PI Web API if each attribute is a roundtrip to the server.

                   

                  If a bulk call can be made to get all the values at once, then I'm thinking it's faster if the filtering can be done with client side logic. If there are not many attributes left after filtering, then it should not be too expensive to get their elements' webID's as well. In PI Web API 2015, bulk data retrieval can be done via the StreamSet controller but the limitation is that all attributes must belong to the same element, which we don't have here. In later versions of PI Web API, we should be able to make bulk calls against an adhoc list of attributes, which would suit us better here.

                   

                  I think the best option here would be to test for 100, 1,000, etc. attributes and see how it scales. I would try to use PI Indexed Search via GET search/query whenever possible, make bulk calls if it's suitable (for example, one roundtrip to get CustomerID_Attribute and InstallDate_Attribute), and try chunking/parallel queries on the client side. Feel free to also open a Tech Support case to request an enhancement for attribute value queries with PI Web API, since access to FindElementsByAttribute via Web API seems to be what we really need here.

                  3 of 3 people found this helpful