The OLAP Cube is a metaphor for an original method of manipulating a large amount of numeric data. One of the biggest benefits of OLAP is its capacity for data generalization and interactively requesting only those details that are necessary. Let's go through the methods employed to get details in an OLAP report.
To Drill Down means to expand a dimension value to reach the next level of detail. This is the foundation of the OLAP method and makes it possible to begin with very generalized data and then move on to more detailed data from only those branches desired, delving more deeply into the hierarchical details step by step.
Detailing means getting the detail data, which constitutes a cell in a table. This mechanism makes it possible to exclude unneeded data from the report, data for which grouping is not necessary, all the while maintaining the possibility to obtain that data at any moment by simply double-clicking the cell. Technically, detailing is implemented as a building and running detail query based on a main query. Data in the detailed query is filtered according to the parameters of the current cell.
Drill Through Data stands for the consecutive building of more detailed cubes from one cube, each based on data that constitutes a cell in a table. In other words, Drill Through is drilling down beyond current the cube into a new one.
Contour BI supports all Drill Down, Detailing and Drill Through operations for OLAP Reports.
Drilling Down is a classic operation that makes it possible to generalize or access more detailed levels of the data interactively by clicking the + and - buttons next to every dimension value. For example, if a user clicks the value 2016 of the dimension Year, the more detailed dimension Quarter will appear, and then the user clicks Q1, the next level dimension Month will be visible.
Fig.1 Drill Down: Collapsed items
Theoretically, the number of levels is infinite, although in practice every additional level makes the OLAP cube bigger and slower and the OLAP report more complicated. Fortunatelly, there are additional, advanced methods which add to OLAP Engine capacity and make it possible to work with virtually unlimited amounts of data.
Fig.2 Drill Down: Expanded item California
Detailing allows one to discharge a report of rarely used data and to drastically increase the operation speed, as long as strings are not used for data grouping.
Detailing is simple to set up. By default, all query fields used to construct the report become detail fields and double clicking a cell invokes a table with the comprehensive data that makes up this cell. With some tuning it is also possible to remove unnecessary fields from the detail reports.
Fig.3 Detailing: Source data for clicked cell California, 2015 year
For example, a Payment Order Report would be too big if it contained all transactions data, and it is therefore best to group data across time, client, etc... (numeric and short string fields), and show the transactions table by double clicking cells in the aggregated report. The transactions detail table would show long string fields that did not take part in grouping in the master report.
Furthermore, Contour Reporter supports unbalanced hierarchies, which means you can display data with different details in different branches of the OLAP tree. For example, you can show an additional analytical level for a particular expense item in the budget, while other items are left unaltered.
Drill Through makes it possible to build a system of cubes for observing very large, terabyte-sized, volumes of data, possible because at any given moment the user is working with a compact cube, containing small data sets. Drilling to the leaf level of the cube, the user passes to another cube, and the "parent" cube unloads from memory. In theory, the number of cubes in a chain is unlimited, making it possible to pay no attention to the size of the initial database. One disadvantage is the relative complexity of setting up drilling paths from one cube to another.
Contour Reporter offers a very simple mechanism of the drill through paths via linked reports. It also extends the Drill Through functionality by adding tabular reports and card reports to the Drill Through chain.
Linked Reports is one of the Drill Through implementations available in Contour BI. Any report can be connected to one or multiple linked reports, parametrized with coordinates of the current cell. The user is given a menu that lets her/him specify the direction in the data to be drilled through. Each detail report, in turn, can contain linked reports. In this case reports may display data from different databases. This makes it possible to obtaining more and more detailed data, for example, by drilling to find a single transaction from a highly aggregated yearly report of a large international bank.
Fig.4 Drill Through: Tag Cloud Chart for clicked cell California, 2015 year
This topic briefly discussed the possibilities provided by Detailing and Drill Through technologies:
Analysis of unlimited data volumes, consecutively obtaining small multidimensional or flat reports
Increasing operation speed by excluding rarely used columns from aggregated reports
Integrating data of different natures into one analytical interface
Consolidating multiple data sources in one system of linked reports
Drilling from enterprise KPIs down to transactional data
Getting pictures and photographs in your reports