Data drilling 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 designed specifically for data analysis.
Consider the following example: Fred and Wilma table : 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 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, except it also allows data to be represented in summary format, according to a flexible user-selected hierarchy. This class of data drilling operation is formally 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 . 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 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 : female flintstone, wilma rudolph, wilma webb, wilma male chopin, fred flintstone, fred durst, fred johnson, fred
Summarize by home, lname : bedrock flintstone fred wilma Poland chopin fred usa ...
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 spreadsheets and desktop 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 off drilldown. For example, if you drilldown for to see the revenue of one product, then you might want to drillup to see the revenue of all products.
