AnsweredAssumed Answered

PIOLEDBENT Query Parent & Child Event Frames

Question asked by DonKiser on Jun 30, 2020
Latest reply on Jul 1, 2020 by DonKiser

We have PI as part of our FactoryTalk Historian, so it's Pi but without the online access benefits. I can get here but that's about it. Everything else is blocked.

 

Programs: PIEFGEN, PIOLEDBENT, PI System Explorer, PI SQL Command Lite

 

All software is set up, configured, running. 

I am creating parent and child event frames.

Attribute data is being stored.

 

I will simplify naming conventions, to protect I.P.

 

The Parent Event Frame uses template Procedure_Parent

Attributes: Name, Age, Hobby

Pulse trigger with several attributes

 

The Child Event Frames uses tmeplate PhaseStep_Child

Attributes: Name, Age, FavoriteColor

Step Trigger with Attributes

 

 

I can view the Event frame and child events in Pi system Explorer. I can query various Parent event frames via SQL Commander. 

 

I would like to do the following but I'm having trouble figuring out all the Joins, fields, to transpose or not to transpose, etc.

 

Given a Parent Event Frame by Name (unique identifier), show the associated Child event frames (Start Time, End Time), the attribute name and values for child event frame. The Data will go into a SSRS report so Attribute names and value pairs are needed.

 

Given the example query below I know I'll be querying at least these tables:

[AFDB].[EventFrame].[EventFrame]

[AFDB].[EventFrame].[EventFrameAttribute] 

[AFDB].[Data].[EventFrameSnapshot]

 

but I'm not sure how to format the query and whether or not I need to transpose the data?

 

Desired Output data (formatted for this post)

 

Dad

   Becky (Age/12, FavoriteColor/ Blue)

   Susie (Age/6, Favorite Color/Yellow)

   Child n+1 (Attribute Name/Value,.....)

 

This example query will give me the attributes of the Parent but doesn't return the attribute name or the children

 

SELECT  ef.Name, ef.StartTime, ef.EndTime, s.*
FROM
(
    SELECT TOP 100 ID, Name, StartTime, EndTime
    FROM [AFDB].[EventFrame].[EventFrame]
    WHERE Name =N'Dad'
    ORDER BY StartTime DESC
) ef
INNER JOIN [AFDB].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID
INNER JOIN [AFDB].[Data].[EventFrameSnapshot] s ON s.EventFrameAttributeID = efa.ID
OPTION (FORCE ORDER, EMBED ERRORS)

 

This gets me roughly (there's a whole lot more data because of s.* which I fix later)

Dad, 52, Football

Mom, 50, Sewing

No Attribute Name/ Value pairing

 

How do I do this?

 

Outcomes