Dynamic and custom transpose function types

Dynamic and custom transpose function types are both based on element templates. All elements derived from a certain template provide data to the corresponding transpose function. All attribute templates of a given element template potentially turn into columns of the transpose function where data comes from derived elements and their attributes.

Dynamic transpose functions

Dynamic transpose functions automatically expose all attribute templates as columns. If attribute templates get added, renamed or deleted, the metadata of a corresponding transpose function then gets updated automatically. Thus, dynamic transpose functions do not need to be altered when the structure of an element template changes, however, queries or views referencing columns that are not available anymore still need to be modified.

Note: For performance reasons only those columns that are needed should be used from the SELECT list.

Custom transpose functions

Custom transpose functions allow using a subset of available attribute templates and also allow to customize column names. If element templates and their attribute templates get modified, corresponding custom transpose functions may become invalid and need to be altered.

Note: A custom transpose function may return less rows than its dynamic pendant. The reason for that is because an attribute that has a value at a time stamp where no other attribute has a value causes a row, meaning that there are NULL values in other columns. That row will not exist if the column representing that attribute (attribute template) was not added to the custom transpose function.

For transpose function examples, see the 1-TransposedData.sql file in PI SQL Commander Lite:

1. Open PI SQL Commander Lite.

2. Click Help > Query Compendium.

3. In Solution Explorer navigate to PI SQL Query Compendium (Asset)

4. Open the Queries > Transposed Data folder.

5. Double-click the 1-TransposedData.sql file.