Transpose functions in PI OLEDB Enterprise are really great stuff which has been implemented. Made my life a lot easier.
But another curve ball hit me a couple of days ago - I need to get UOM and timestamp for every attribute along with its value.
Currently, my views in PI OLEDB look like the following:
SELECT e.id as ElementID, e.name as ElementName, w.* FROM IO_RM.Asset.Element AS e JOIN IO_RM.Asset.ElementTemplate AS et ON e.ElementTemplateID = et.ID CROSS APPLY [IO_RM].[DataT].[TS_tzWell](e.ID) w WHERE et.Name in ('tWell_Production', 'tWell_Injection') OPTION (FORCE ORDER)
where TS_tzWell - is a transposed function for the tzWell AF Template and its attributes.
So by running this query I get snapshot values for attributes of elements created from that template.
Is there a way, how I can get UOM without having A LOT of joins on Attribute table for each attribute to get UOM ID and then using UOM functions to get UOM names?
And what is the best way to get timestamps for the snapshot values in transpose snapshot function?