Recently, I have been playing around building some data cube for BI analysis with PowerPivot and PI OLEDB Enterprise, and one of the feature of PI OLEDB Enterprise that I feel that is really useful through this process is Transposed Vector Function (TVF). There were some discussions in the past that TVF is brought up, like this one. Steve basically has made a brief and concise explanation of what TVF does. If you are still not sure, here's a quick explanation.
In our usual query in the classic PI OLEDB Provider or in PI OLEDB Enterprise's tables in the Data category like:
SELECT e.Name Element, ea.Name Attribute, i.Time Time, i.ValueDbl Value FROM [AFDB].[Asset].[ElementTemplate] et INNER JOIN [AFDB].[Asset].[Element] e ON et.ID = e.ElementTemplateID INNER JOIN [AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = e.ID INNER Join [AFDB].[Data].[ft_InterpolateRange] i ON ea.ID = i.ElementAttributeID WHERE et.Name = N'Refinery' AND e.Name LIKE N'Refine%' AND i.StartTime = N'*-1h' AND i.EndTime = N'*' AND i.TimeStep = N'30m' OPTION (FORCE ORDER, EMBED ERRORS)
and a snapshot of the result set:
This get the values of the different attributes, but all are presented in a single column. In my case a more ideal case is to have the different attributes presented in different columns. This is where TVFs is helpful. Using TVF, we can create a function in PI OLEDB Enterprise that returns the result set like:
Now in PowerPivot (of cause, you can be using other tools for your BI analysis as well), I can grab the attribute values that represent the KPI that you want from the respective column directly.
What you have to take note for this
- TVF must be created before use (using PI SQL Commander, refer to the PI OLEDB Enterprise manual)
- TVF is created for a specific Element Template in PI Asset Framework (AF)
While I am at this, there is a new white paper, titled "Business Analytics with your PI System Data using Microsoft PowerPivot" that was recently updated in the vCampus Library, under the "OSIsoft - Microsoft Accelerator" category. It contains step-by-step walkthrough of the processing of bring PI System Data to PowerPivot using PI OLEDB Enterprise. The sample AF database is included in the supporting files, hence you can walk through the paper easily.