I have a requirement where I need to find the top 5 bad actors (equipment's) from the list of actors available ,each actor's loss is associated with a tag. Below is the AF Hierarchy we built for this purpose ,
- The Analysis Type element defines the start time and end time to calculate the total loss by the actors.
- The top 5 bad actors should be evaluated per unit , each area will have several units and each site will have several areas.
+++ Analysis Type 1
+++ Analysis Type 2
I am planning to create a AF Table with top 5 bad actors per unit , per Analysis Type , Per Area and Per site , once the AF table is ready would like to assign the top 5 actors to the corresponding attributes at unit level.
I was able to come with a query to list down all the loss Attributes , Loss attribute a part of Actor Element and this value is calculated by a AF analysis.
SELECT eh.path as [Element Parent], e.name as [Element Name], e.description as [Element Description], ea.name as [Attribute Name], s.Value
FROM [AF_DATABASE].[Asset].[Element] e
INNER JOIN [AF_DATABASE].[Asset].[ElementHierarchy] eh ON eh.ElementID = e.ID
INNER JOIN [AF_DATABASE.[Asset].[Elementattribute] ea ON ea.ElementID = e.ID
INNER JOIN [AF_DATABASE].[Data].[Snapshot] s ON ea.id = s.ElementAttributeID
WHERE ea.name = 'Loss'
ORDER BY eh.path DESC
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
I have 2 requirements in brief:
- By using the PI OLEDB query get top 5 bad actors per unit , per Analysis Type , Per Area and Per site in to a AF table.
- Once the AF table is ready assign the top 5 actors to the corresponding attributes at unit level attribute.
Please help me on how can I achieve this and let me know if you need more information.