Pivoting
Pivot is another important operation supported by Data Cubes. SQL Frames provides both the API to compute PIVOT and also provides Pivot table component for visualization.
Pivot tables organize data into cells by placing the values of the dimensions along both the vertical and horizontal axis. The lowest level of detail in the multi-dimensional cube occupies these pivot cells.
Pivot tables can be enhanced by providing sub-totals along one or more dimensions both vertically and horizontally. Sub-totals are nothing but the rollups of a data cube. A cube with N dimensions can have at most 2N levels of detail.
Grand-total is a special case of Sub-totals where all the dimensions in the data cube are rolled up.
Pivot table with grand totals
In this Pivot table only the grand total value is displayed at the bottom left corner. This grand total
level of detail is explicitly diced below using [] level in the groupingSets.
Pivot table with sub-total rows
In SQL Frames displaying pivot table with sub-total rows is as simple as using a Group By using ROLLUP (or CUBE) for the row grouping fields, Region and Country below.
Pivot table with row & col sub-totals
In SQL Frames displaying pivot table with both row and column subtotals is as simple as using a Group By using ROLLUP (or CUBE) for both row and column fields or a single CUBE for all the grouping fields. In the following example, ROLLUP is used for the geography hierarchy to provide sub-total rows and CUBE is used for Item Type and Sales Channel to provide sub-total columns.
Hierarchical pivot table with sub-totals
The pivot columns by default have hierarchical display. The pivot rows can also be displayed hierarchically
using the Hierarchical DataFrames API, df.hdf().