Organizing pivot table elements in the Structure view

The pivot table elements (pivot table, hierarchy/dimension, fact, and measure) must be organized within the Report Structure view so that values are triggered at the desired point in the data stream. There are varying methods for organizing your pivot table elements, but some methods are more efficient than others.

For example, Figure 1 shows a table with six dimensions (HIERARCHY elements) and four measures (MEASURE elements), without any reorganization or triggers.

Figure: Pivot table elements in the Structure View

This figure shows a Report Structure where the Pivot Table node has six Dimensions and a Fact element as children. The Fact element contains the four Measures.

Defining columns

Figure 2 shows a pivot table definition on the left and a rendering of the table on the right.
  • Dimensions and measures define the columns of the table.
  • Not all defined dimensions and measures were selected for display.
  • The title of the table and the selection of the dimensions is defined in the pivot table element
  • The title of the columns are defined in the dimension and measure elements.
  • The selection of the measures is defined in the fact element.
Figure: Report columns

This figure shows a pivot table report with five columns: two columns map to Dimensions in the Pivot Table element, and three columns map to Measures under the Fact element, which is also a child of the Pivot Table element.

Defining rows

A row of a table is formed by the entity of dimension declaration followed by one fact element. Typically one row is defined for the table, and is placed in a trigger to be repeated for each input record, as in Figure 3.

Figure: Report row placed in a single trigger

This figure shows the dimensions and Fact element under the OnEveryRow trigger.

You can specify the rows literally, but it is inefficient and highly unusual. In this case, all rows must have exactly the same structure (number of dimensions and measures, types, and so on), as in Figure 4.

Figure: Report rows specified literally

This figure shows the groupings of dimensions and facts into a row.

Arranging hierarchies

You can organize the pivot table hierarchies (dimensions) to optimize the output and minimize the volume of the data stream.

For best efficiency, the hierarchies should be shipped sparsely, that is, not all hierarchy values need to be shipped for every row. Compare the hierarchy values of two consecutive rows from right to left starting with the innermost dimension, and omit any value if it is the same in both rows, until you reach the first dissimilar column.

For example, see the report structures in Figure 5 and Figure 6, where the arrows indicate the location of the hierarchies. Figure 5 uses a flat representation. Figure 6 places the hierarchies in the corresponding trigger, so the value for “shipcountry” is only shipped only on changes of “shipcountry”; the value remains the same for all orders shipped to that country. If you run the report, the output looks similar in both cases, but the data stream for Figure 6 requires less processing and is therefore more efficient.

Figure: Flat shipping of dimension values

This figure shows an example of a Report Structure where the dimensions shipcountry and catorder are both located as children of the OnEveryRow trigger.
Figure: Optimized shipping of dimension values

This figure shows an example of a Report Structure where the dimensions are arranged such that the shipcountry Dimension is under the Group shipcountry trigger and the catorder Dimension is under the Group catorder trigger.