AnsweredAssumed Answered

Select Top 5 Bad Actors (attribute values) from each category and assign to output attributes

Question asked by VijayaBhaskar on Jun 1, 2017
Latest reply on Jun 9, 2017 by VijayaBhaskar

Hi All,

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.

 

+ Site

   ++Area

     +++ Analysis Type 1

           ++++Unit1

                   +++++Actor1

                   +++++Actor1

                   +++++Actor3

                   +++++Actor4

                   +++++Actor5

                   +++++Actor6

   +++ Analysis Type 2

           ++++Unit1

                   +++++Actor1

                   +++++Actor1

                   +++++Actor3

                   +++++Actor4

                   +++++Actor5

                   +++++Actor6

 

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.

Regards

Vijay

Outcomes