Data Drilldown and Drillup

To get more detailed or general information about data, use data drilldown or drillup. There are drilldown and drillup of aggregated and disaggregated data:

• Disaggregated and aggregated data drilldown and drillup. It is available for all cube dimensions. One should set up data source.

• Data drilldown based on an SQL query. It is available only for cube fact dimension. One should set up data source.

• Drilldown of aggregated facts. It is available only for cube fact dimension. One should set up data source.

NOTE. Aggregation is the procedure of aggregating data from several elements in one. When aggregated data is used, drilldown enables the user to view dimension elements and relational data used for aggregated data.

Data drilldown and drillup are available in the tools:

• Reports in the desktop application.

• Analytical Queries (OLAP) and Dashboards in the web application and in the desktop application.

For details about data drilldown and drillup in the web application see the Data Drilldown and Drillup article for reports and the Data Drilldown and Drillup article for information panels.

Drilldown is unavailable 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 Drilldown and Drillup

Data drilldown and drillup is an operation that enables the use of navigating data levels: from general to particular ones and vice versa. Available drilldown depth is determined by the hierarchy of the data source dimensions. To drill down aggregated fixed dimensions one can view elements used for data aggregation.

During data drilldown or drillup, parent or child elements of corresponding dimension level are selected.

To display the source selection specified on the Selection side panel tab, for example, using controls, select the Go Back to Original State item in the table cell's context menu after applying data drilldown/drillup.

If data drilldown/drillup has been resulted in the level without data, one should select the Go Back to Original State item in the table cell's context menu.

NOTE. The Go Back to Original state item is available in the Analytical Queries (OLAP), Dashboards tools when selection is changed and then data is drilled down/drilled up. If data drilldown/drillup is not executed after selection change, the item will be unavailable.

Drilldown

Drilldown 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 dimension detailing is set up, the set action will occur on clicking the hyperlink.

After drilldown, you move to the more detailed level of dictionary hierarchy. The selection is rebuilt: all child elements of more detailed dimension level are included.

Drilldown 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.

If there is no hierarchy or the lowest hierarchy level is reached, dimension elements can be drilled down with replacement of the dimension with the fixed dimension or dimension in source filter.

Drilldown by Elements of Several Dimensions

To drill down data by elements of several dimensions:

• Select the Drill Down item in the context menu of the selected table cells.

NOTE. In the Dashboards tool the item is named Table > Drill Down.

• Click the hyperlink in the cell while holding down the CTRL key. It is available for the cell.

Drilldown will be executed by all dimensions intersected in one cell if they have child elements. If one dimension have child elements, drilldown will be executed by this dimension.

If there is no hierarchy or the lowest hierarchy level is reached, data cells in dimension can be drilled down with replacement of the dimension with the fixed dimension or dimension in source filter.

Drilldown with Replacement

Dimension elements and data cells can be drilled down with replacement of the dimension with available fixed dimensions or dimensions in source filter:

• If the selected sidehead/heading dimension does not have hierarchy or the lowest hierarchy level is reached, the following happens when the Drill Down item is selected:

• Fixed dimension or dimension in source filter, depending on the set order, will be moved to sidehead/heading.

• Selection of replacement dimension will be saved or will become full if it was not set before.

• Sidehead/heading dimension with saved selection will be moved to fixed dimensions or source filter, depending on the position of the replacement dimension before drilldown.

• If sidehead/heading dimensions do not have hierarchy or the lowest hierarchy level is reached for the selected cell or cell range, the following happens when the Drill Down item is selected:

• Fixed dimension or source dimension, depending on the set order, will be moved to sidehead.

• Selection of replacement dimension will be saved or will become full if it was not set before.

• The rightmost dimension with saved selection in sidehead will be moved to fixed dimension or source filter dimension, depending on the position of the replacement dimension before drilldown.

Drilldown of Aggregated Dimensions

If aggregation of fixed dimensions one can execute drilldown 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:

1. Select the Drill Down item in the cells' context menu.

NOTE. In the Dashboards tool the item is named Table > Drill Down.

2. Select fixed dimension, which elements should be drilled down.

After executing the operation the dimension will be moved to rows and will contain elements used for cell range aggregation.

Example of drilldown and drillup of aggregated data

Drillup

Drillup is available for hierarchical elements of data source that have parent elements.

Drillup is used to move to a higher level of dictionary hierarchy. The selection is rebuilt: all elements of dimension parent level are selected.

Drillup by Dimension Elements

To drill up data by dimension elements:

• Click the hyperlink in dimension element name in the table sidehead or heading while holding down the SHIFT key.

• Select the table sidehead/heading cells, which data should be drilled up, and select the Drill Up context menu item.

If there is no hierarchy or the highest hierarchy level is reached, dimension elements can be drilled up with replacement of the dimension with fixed dimension or dimension in source filter.

Drillup by Elements of Several Dimensions

To drill up data by elements of several dimensions:

• Click the hyperlink in the data cell while holding down the SHIFT key.

• Select the Drill Up item in the table cell or cell range's context menu.

NOTE. In the Dashboards tool the item is named Table > Drill Up.

Drillup will be executed by all dimensions intersected in one cell if they have parent elements. If one dimension has parent elements, drillup will be executed by this dimension.

If there is no hierarchy or the highest hierarchy level is reached, data cells in dimensions can be drilled up with replacement of the dimension with fixed dimension or dimension in source filter.

Drillup with Replacement

Dimension elements and data cells can be drilled up with replacement of the dimension with fixed dimensions or dimension in source filter:

• If the selected sidehead/heading dimension does not have hierarchy or the highest hierarchy level is reached, the following happens when the Drill Up item is selected:

• Fixed dimension or dimension in source filter, depending on the set order, will be moved to sidehead/heading.

• Selection of replacement dimension will be saved or will become full if it was not set before.

• Sidehead/heading dimension with saved selection will be moved to fixed dimension or source filter dimension, depending on the position of the replacement dimension before drillup.

• If sidehead/heading dimensions for the selected cell or cell range do not have hierarchy or the highest hierarchy level is reached, the following happens when the Drill Up item is selected:

• Fixed dimension or source dimension, depending on the set order, will be moved to sidehead.

• Selection of replacement dimension will be saved or will become full if it was not set before.

• The rightmost dimension with saved selection in sidehead will be moved to fixed dimension or source filter dimension, depending on the position of the replacement dimension before drillup.

Drillup of Aggregated Dimensions

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 the selected cell or cell range, for which drillup was executed.

To drill up the cells, which data was obtained by aggregation of fixed dimensions:

1. Select the Drill Up item in the cells's context menu.

NOTE. In the Dashboards tool the item is named Table > Drill Up.

2. Select the dimension, which elements must be drilled up.

SQL Query-Based Drilldown

When cube fact aggregation is not set up, use SQL query-based drilldown. Drilldown is available if a query is selected in a cube, the query will be used to get source relational data. Main work principles and restrictions will be the same as when server aggregation is set up for fact dimension.

Drilldown of Fact Dimension Aggregated on Server

If server aggregation of fact dimension is set up, one can drill down to relational data on the last hierarchy level. After the data is drilled down, the records 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 fact dimension, use the Drill Down item in the cells' context menu.

NOTE. In the Dashboards tool the items are named Table > Drill Down.

Constraints of analytical functions in the relational mode

Example of drilldown

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.

Example of grouping

To ungroup data, select the grouped column and select the Ungroup context menu item.