Transpose Functions and UOM - PI OLEDB Enterprise

Discussion created by aabrodsky on Mar 9, 2011
Latest reply on Mar 18, 2011 by aabrodsky

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.*
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 
et.Name in ('tWell_Production', 'tWell_Injection')

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?