• State Not Answered
  • Replies 1 reply
  • Subscribers 3 subscribers
  • Views 271 views
  • Users 0 members are here
Options
Related

Knowledge Share: A PI System Explorer solution for searching for PI Points used by PI AF Attributes

BrahmNeufeld

Knowledge Share: A PI System Explorer solution for searching for PI Points used by PI AF Attributes

Over on the feedback portal there's a request dating back to 2017 (if the comments are accurate) to give users the ability to search for PI points used by AF attributes, in other words, addressing the issue, "I have this PI tag but I have no idea where it's used in AF!!!" The feedback item is still not included in System Explorer - or I wouldn't be writing this post!

 

Deep in the comment thread user taterhead247 offered up a great SQL query that looks into the PI AF SQL Database (PIFD table) to generate an answer; it returns a list of Elements and Attributes where a specific tag is used.

 

We had an insight to make this solution (slightly) more user-friendly and available to our PI power users within PI System Explorer.

 

First, PI power users were granted read-only access to the PIFD table.

 

Next, the PIFD table was configured as a Table Connection in PI System Explorer, with Security set to "impersonate client".

 

We then set up a Table called "AF Tag Lookup Utility" configured with a modified version of taterhead247's query:

  • Connection: PIFD (or whatever you named the reference to this table)
  • Query: SELECT ep.path As 'Element Path', ep.name As Element, ta.path AS 'Attribute Path', ta.name As Attribute, adr.configstring, 'Yes' As Template, CASE WHEN ta.fkparentattributeid IS NOT NULL THEN 'Yes' ELSE 'No' END As 'Is Child Attribute' FROM AFElementAttributeDR adr INNER JOIN AFElementTemplateAttribute ta on ta.rid = adr.fkattributetemplateid INNER JOIN AFElementVersion ev on ev.rid = adr.fkelementversionid INNER Join afelementpathA0 ep on ep.elementversionid = ev.id WHERE adr.fkattributetemplateid IS NOT NULL AND ep.primarypath = 1 AND adr.configstring like '%\' + @PIPoint + '?%' UNION SELECT ep.path As 'Element Path', ep.name As Element, ea.path AS 'Attribute Path', ea.name As Attribute, adr.configstring, 'No' As Template, CASE WHEN ea.fkparentattributeid IS NOT NULL THEN 'Yes' ELSE 'No' END As 'Is Child Attribute' FROM AFElementAttributeDR adr INNER JOIN AFElementAttribute ea on ea.attributeid = adr.fkattributeid INNER JOIN AFElementVersion ev on ev.rid = adr.fkelementversionid INNER Join afelementpathA0 ep on ep.elementversionid = ev.id WHERE adr.fkattributetemplateid IS NULL AND ep.primarypath = 1 AND adr.configstring like '%\' + @PIPoint + '?%'


af_lookup_01.png

af_lookup_02.png
Since power users can interact with linked tables in our ecosystem, they can open the table link properties with the Link button and update the Default Value for the PIPoint substitution parameter. They can then click OK and shift from the "General" tab over to the "Table" tab to see all of the Attributes where that PI tag was located in the database:


af_lookup_03.png
The use cases we can think of are:

  • A calculated tag populated by an AF Analytic stops working or needs updating, but it's not immediately know where in the hierarchy the Element, Attribute, and Analysis are located.
  • A user wants to add a tag to a hierarchy, but first wants to check on where else it might exist.
  • A PI tag's properties are about to be changed (e.g. exception/compression), but we want to verify that won't impact any analytics or calculated tags.

 

This is as "user friendly" as we could make things for most power users in AF, but we also extended the functionality of this idea for super-duper-power users:

  • A sharp co-op student wrote a script that takes any number of PI tags as an input and spits out an Excel spreadsheet that lists all references for all tags.
  • We recognized that the adr.configstring like '%\' + @PIPoint + '?%' clause (that appears twice in the SQL query above) could be tweaked to identify not just PI tags but more types of attributes. A super useful use case for us is generating a list of Attributes referencing a specific linked table. So, by modifying the query clause to something like adr.configstring like '%SELECT%FROM%My_Production_Table%' you can quickly generate a list of all Attributes referencing a linked table.

 

Would love to hear feedback on how this could be improved for our users or other use cases - or if we have duplicated something that is well-known and obvious to others! Cheers Beers

  • 0

    The drawback of using the PIFD is that AVEVA absolutely does not support accessing the PIFD directly. So you are on your own, and there is always the warning that AVEVA can modify the tables in the PIFD at their descretion without any notice since you are not supposed to access it directly. What AVEVA would give notice about is the change to any related SDK or API calls.

     

    One could use AF SDK to open an AFDatabase and search for all AFAttributes using the PI Point data reference. From there you could extract the PIServer, Tag name, and even Point ID if needed. Downside to this is (1) you must be a developer and (2) performance could possibly be sluggish depending upon the size of the AFDatabase for the AFAttributeSearch call. I don't know if you could get by with a skinny search since to do it justice you may have to issue a bulk call to get the underlying PIPoints. Otherwise the full load is sluggish. And considering you may not exactly know which AFDatabase to search so you would search ALL of them just means it would be that much slower. Without a doubt the PIFD is much faster, and while I personally would probably use it for an entire AF Server (PISystem), it sill comes with warnings that you are doing something that you aren't supposed to be doing.

     

    I have done something similar for my company but cannot share it. You could dump the data to a tab-delimited text file to be imported into Excel, but I loade ClosedXml from NuGet to write the data directly to an Excel workbook. I can even format it, freeze the row and column, set filters, sort the data, etc. quite easily thanks to ClosedXml. The only downside is that it is in .NET Framework and requires another package called SixLabors Fonts which can't be upgraded. Still, I have used it just today and it is a good enough solution.

     

    UPDATED: I image below that I have obfuscated but I want to emphasis that I was able to set everything in C# code. Colors, bolding, column widths, border lines, etc. I even run this on a VM (closer to the AF Server) without Office or Excel installed, and then copy the workbook to my PC with Excel where I can review.

     

    Click on image to enlarge.

     


    ClosedXml Obfuscated.png