Drill Down / Drill Up
A multi-dimensional data cube stores the metrics at various levels of detail. This makes it easy to navigate the data by drilling up and down from the current level of detail. It is also possible to drill all the way down to the detail rows that are used for data aggregation.
Note that SQL Frames already has the capability to automatically display these drill/detail DataFrames accessible from the menu bar of the DataFrame UI so there is no need to write any code. However, below examples use the simple APIs to present the UI as master-detail interface.
First lets setup the data for the rest of the examples on this page.
Drill Downs
For a given aggregated DataFrame, it is possible to drill-down the current level of detail of the selected aggregate row. Drill-downs show data by adding one additional level of detail dimension. For example, when viewing data by Region, data for that region broken down by Item Type or Sales Channel are the associated Drill-downs.
Use df.getDrillDowns() API to access the drill down rows of the selected row in the df DataFrame.
Click on different rows from different levels of detail in the top DataFrame and see how the bottom DataFrame changes by showing the associated drill-downs.
Observe how the generated SQL changes along with data as you select different aggregate rows.
Drill Ups
Drill-ups allow viewing the data in the context of associated higher levels. For example, this allows understanding the percent of contribution of the selected level of detail with respect to the other higher levels.
From a given aggregated DataFrame, it is possible to drill up the current level of detail of the selected aggregate row. For any given aggregate row, drill-ups are all the related rows that are above the level of detail of the aggregate row. A Data Cube created from CUBE operation on 3 dimensions results in 8 levels of detail. Hence, looking at an aggregate at the lowest level of detail will show 8 rows as drill-up rows showing all possible higher levels of detail.
Use df.getDrillUps() API to access the drill up rows of the selected row in the df DataFrame.
Click on different rows from different levels of detail in the top DataFrame and see how the bottom DataFrame changes by showing the associated drill-ups.
The P.drillgdf is displayed twice once in the Drill-downs example and once in the Drill-ups example. Notice how selecting a row in one automatically selects it in the other. If multiple views with separate selection is required, simply project the DataFrame.
Details
It is possible to access all the detailed data that went into creating an aggregate row.
Use df.getDetails() API to access the details of an aggregate row.
Click on different rows from different levels of detail in the top DataFrame and see how the bottom DataFrame changes by showing all the detail rows that made up the aggregate row.
Notice how the number of rows in the details DataFrame match with the Count metric of the selected aggregate row.
Default Sort Order
It is possiible to specify default sort order for the details view using detailsOrderBy()
API of the gdf prior to calling the getDetails().