Transposition of data tables and TVFs

Built-in data tables such as Snapshot, Archive, and so on, return attributes and their values in rows.

For various use cases, such as reports or OLAP cubes, attribute values need to be returned in a way so that each column represents an attribute. This is contrary to a typical relational representation, where each value of each attribute is normally represented in consecutive rows. To represent multiple attributes in this "one-column-per-attribute" format, you can join data tables with itself multiple times, but the resulting query string would be rather large and complex. To help with this, we provide a way to create custom Table-Valued Functions (TVFs) and derived function tables, to get "transposed" result sets of the related data tables.

PI OLEDB Enterprise supports custom Table-Valued Functions (TVFs) based on AF element templates. In this case, the element template and the attribute templates it contains, defines the metadata for the TVF. Typically, TVFs are incorporated into queries that use the CROSS APPLY keyword. For details, see Compendium of PI SQL statements for PI AF.

For certain use cases where the environment does not allow the use of TVFs, the provider also supports the creation of function tables derived from these TVFs. These function tables have the same metadata and simply require that parameter columns be constrained (like a function). Use this as an alternative to the CROSS APPLY keyword and syntax.

Note: This user guide refers to parameter columns as columns that have the same name as the parameters in corresponding TVFs.

You can create transposed TVFs and corresponding function tables through a wizard in PI SQL Commander Lite.

1. Open PI SQL Commander Lite.

2. Click View > Object Explorer.

3. Select an AF server and open a connection by double-clicking or right-clicking and Connect.

4. Expand an AF database under the Catalog node.

5. Right-click the DataT node and select New Transpose Function.

6. Select one of the following:

   • Dynamic Transpose Function (Asset)

   • Dynamic Transpose Function (Event Frame)

   • Custom Transpose Function (Asset)

   • Custom Transpose Function (Event Frame)

See PI SQL Commander Lite Help for details about the Transpose Function Wizard.