Data drilling

Data drilling (also drilldown ) refers to any of several operations and transformations on tabular, relational, and multidimensional data . The term has widespread use in various contexts, but is primarily associated with specialized software designed specifically for data analysis .

Common data drilling operations

There are certain operations that are permitted to be carried out. Among them are:

Query operations :

  • Tabular query
  • Pivot query

Tabular query

Tabular query operations consist of standard operations on data tables.

Among these operations are:

  • search
  • fate
  • Filter (by value)
  • Filter (by extended function or condition)
  • Transform (eg, by adding or removing columns)

Consider the following example:

Fred and Wilma table (Fig 001) :

 gender, fname, lname, home
 male, fred, Chopin, Poland
 male, fred, Flintstone, bedrock
 male, fred, durst, usa
 girl, wilma, Flintstone, bedrock
 girl, wilma, rudolph, usa
 girl, wilma, webb, usa
 male , Fred, johnson, usa

The above is an example of a simple flat file formatted as comma-separated values. The table includes first name, last name, gender and home country for various people named fred or wilma. ALTHOUGH the example is formatted this way, it is significant to EMPHASIZE That tabular query operations (as well as all data drilling operations) Can Be Applied to-any Conceivable data type , Regardless of the Underlying formatting. The only requirement is that the data be readable by the software application in use.

Pivot query

A pivot query allows multiple representations of data according to different dimensions. This query type is similar to tabular query, but it can also be used in summary format, according to a flexible user-selected hierarchy . This class of data drilling operation is formally (and loosely) known by different names, including crosstab query , pivot table , data pilot , selective hierarchy , intertwingularity and others.

To illustrate the basics of pivot query operations, consider the Fred and Wilma table (Fig. 001) . A quick scan of the data reveals that the table has redundant information. This redundancy could be consolidated using an outline or a tree structure in some other way. Moreover, once consolidated, the data could have many different alternate layouts.

Using a simple text outline as output, the following alternate layouts are all possible with a pivot query:

Summarize by gender (Fig 001) :

 flintstone, wilma
 rudolph, wilma
 webb, wilma
 chopin, fred
 flintstone, fred
 durst, fred
 johnson, fred
 (Dimensions = gender; Tabular fields = lname, fname;

Summarize by home, lname (Fig 001) :

 (Dimensions = home, lname; Tabular fields = fname;)


Pivot query operations are useful for summarizing a corpus of data in multiple ways, illustrating different representations of the same basic information. ALTHOUGH this kind of operation Appears prominently in spreadsheets and desktop database software, ict flexibility is arguably under-Utilized. There are many applications that allow only a ‘fixed’ hierarchy for that data.

Leave a Comment
