Data Drill Down and Drill Up
In this article:
To get more detailed or general information about data, use data drill down or drill up. There are drill down and drill up of aggregated and disaggregated data:
Disaggregated and aggregated data drill down and drill up. It is available for all cube dimensions. For aggregation, it is required to set up a data source or a report.
Data drill down based on the SQL request. It is available only for cube fact dimension. It is required to set up data source.
Drill down of server aggregated data. It is available only for cube fact dimension. It is required to set up data source.
NOTE. Aggregation is the procedure of aggregating data from several elements in one. When aggregated data is used, drill down enables the user to view dimension elements and relational data used for aggregated data.
Data drill down and drill up are available in the Analytical Queries (OLAP), Dashboards, and Reports tools.
Drill down is not available if the selected data area range contains:
Cells from different sources.
Cells with totals.
The Other cells that appear on data distribution.
Disaggregated and Aggregated Data Drill Down and Drill Up
Data drill down and drill up is an operation that enables the use of navigating data levels: from general to particular ones and vice versa. Available drill down depth is determined by the hierarchy of the data source dimensions. On drilling down aggregated fixed dimensions it is possible to see elements used for data aggregation.
Drill Down
Drill down is available for hierarchical elements of data source that have child elements.
Hyperlinks are available if the Hyperlinks as Plain Text checkbox is deselected on the Parameters side panel tab.
NOTE. If the dimension detailing is set up, the set action will occur on clicking the hyperlink.
After drill down, you move to the more detailed level of dictionary hierarchy. The selection is rebuilt: all child elements of more detailed dimension level are included.
Drill Down by Dimension Elements
To drill down data by dimension elements:
Click the hyperlink in dimension element name in the table sidehead or heading.
Select the table sidehead or heading cells, which data should be drilled down, and select the Drill Down context menu item.
Example of drill down and drill up by dimension element
Drill Down by Elements of Several Dimensions
To drill down data by elements of several dimensions:
Select the table cells, which data should be drilled down, and select the Drill Down context menu item.
NOTE. In the Dashboards tool the item is named Table > Drill Down.
Click the hyperlink in the cell with holding down the CTRL key. It is available for the cell.
The drill down will be executed by all dimensions intersected in one cell if they have child elements. If one dimension have child elements, the drill down will be executed by this dimension.
Example of drill down and drill up by elements of several dimensions
If fixed dimensions aggregation it is possible to execute drill down by aggregated elements. As a result, fixed dimension becomes a dimension in rows, displaying all elements that are used to get aggregated data. To drill down the cells, which data was obtained by fixed dimensions aggregation:
Select the Drill Down item in the cell or cell range's context menu.
NOTE. In the Dashboards tool the item is named Table > Drill Down.
Select fixed dimension, which elements should be drilled down.
After executing the operation the dimension is moved to rows and contains elements used for cell range aggregation.
Example of drill down and drill up of aggregated data
Drill Up
Drill up is available for hierarchical elements of data source that have parent elements.
To drill up the data:
Hold down the SHIFT key and click on the hyperlink in the table sidehead or heading by dimension element name.
Select the table cell or cell range, which data must be drilled up, and select the Drill Up context menu item. The drill up will be executed by all dimensions intersected in one cell if they have parent elements. If one dimension have parent elements, the drill up will be executed by this dimension.
NOTE. In the Dashboards tool the item is named Table > Drill Up.
When drilling up by aggregated elements, dimension from rows goes to fixed one. In other dimensions, in rows and columns, selection will be changed according to selected cell or cell range, for which drill up was executed.
To drill up the cells, which data was obtained by fixed dimensions aggregation:
- Select the Drill Up item in the cell or cell range's context menu.
NOTE. In the Dashboards tool the item is named Table > Drill Up.
- Select the dimension, which elements must be drilled up.
SQL Query-Based Drill Down
When cube fact aggregation is not set, use SQL query-based drill down. Drill down is available if a query is selected in a cube, the query will be used to get source relational data. Main principles of work and restrictions will be the same as when server aggregation is set up for fact dimension.
Drill Down of Fact Dimension Aggregated on Server
If server aggregation of facts dimension is set up, it is possible to further drill down to relational data on the last hierarchy level. After the data is drilled down, the entries are displayed from fact table, by which the cell value is formed as a flat table. The side panel displays the Source Fields additional dimension, which is located by columns and contains all fields from the fact table, including the fields that are not linked to cube dimensions.
To drill down the cells, which data was obtained by server aggregation of facts dimension, use the Drill Down item in the context menu of a cell or a cell range.
NOTE. In the Dashboards tool the items are named Table > Drill Down.
Constraints of analytical functions in the relational mode
Grouping of Relational Data
When data is presented in relational format, data can be grouped by columns.
To group data, select the column and select the Group context menu item.
NOTE. In the Dashboards tool the item is named Table > Group.
To ungroup data, select the grouped column and select the Ungroup context menu item.