# Data drilling

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Data_drilling
> Markdown URL: https://mediated.wiki/source/Data_drilling.md
> Source: https://en.wikipedia.org/wiki/Data_drilling
> Source revision: 1261356753
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

{{more citations needed|date=January 2021}}

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

== Common data drilling operations ==
There are certain operations that are common to applications that allow data drilling. Among them are:

'''[Query](/source/Information_retrieval) operations''':
* tabular query
* pivot query

=== Tabular query ===
Tabular query operations consist of standard operations on data tables.

Among these operations are:
* search
* sort
* filter (by value)
* filter (by extended function or condition)
* transform (e.g., 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
    female, wilma, flintstone, bedrock
    female, wilma, rudolph, usa
    female, wilma, webb, usa
    male, fred, johnson, usa

The preceding is an example of a simple flat file table 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 important to emphasize that tabular query  operations (as well as all data drilling operations) can be applied to any conceivable [data type](/source/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, except it also allows data to be represented in summary format, according to a flexible user-selected [hierarchy](/source/hierarchy). This class of data drilling operation is formally, (and loosely) known by different names, including '''[crosstab query](/source/crosstab)''', '''[pivot table](/source/pivot_table)''', '''data pilot''', '''selective hierarchy''', '''[intertwingularity](/source/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](/source/tree_structure) or 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)''':
    female
        flintstone, wilma
        rudolph, wilma
        webb, wilma
    male
        chopin, fred
        flintstone, fred
        durst, fred
        johnson, fred
    
    (Dimensions = gender; Tabular fields = lname, fname;)

'''Summarize by home, lname (Fig 001)''':
    bedrock
        flintstone
            fred
            wilma
    Poland
        chopin
            fred
    usa
        ...
    
    (Dimensions = home, lname; Tabular fields = fname;)

==== Uses ====
Pivot query operations are useful for summarizing a corpus of data in multiple ways, thereby illustrating different representations of the same basic information. Although this type of operation appears prominently in [spreadsheet](/source/spreadsheet)s and desktop [database](/source/database) software, its flexibility is arguably under-utilized. There are many applications that allow only a 'fixed' hierarchy for representing data, and this represents a substantial limitation.

==Drillup==
'''Drillup''' is the opposite of drilldown. For example, if you drilldown to see the revenue of one product, then you might want to drillup to see the revenue of all products.<ref>
{{cite web
| url         = https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cr_qstd.doc/t_drill_up_or_drill_down.html
| title       = Drilling up and drilling down
| date = 4 July 2018
| publisher   = [IBM](/source/IBM)
| location    =
| accessdate  = 2020-05-05
| quote       = 
}}</ref>

==References==
<references/>

Category:Hierarchy
Category:Information science

{{Comp-sci-stub}}

---
Adapted from the Wikipedia article [Data drilling](https://en.wikipedia.org/wiki/Data_drilling) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Data_drilling?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.