Jump to content

Pivot table

From Wikipedia, the free encyclopedia

This is an old revision of this page, as edited by Numbergo principal (talk | contribs) at 20:12, 22 January 2013 (→‎Application support). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.

The term pivot table is a generic phrase used by multiple vendors. However, Microsoft Corporation has trademarked the specific form PivotTable.[1][2]

Pivot tables can be seen as a simplification of the more complete and complex OLAP concepts.

History

In their book Pivot Table Data Crunching,[3] authors Bill Jelen and Mike Alexander refer to Pito Salas as the "father of pivot tables". While working on a concept for a new program which would eventually become Lotus Improv, Salas realized that spreadsheets have patterns of data. A tool that could help the user recognize these patterns would help to build advanced data models quickly. With Improv, users could define and store sets of categories, then change views by dragging category names with the mouse. This core functionality would provide the model for pivot tables.

Lotus Development released Improv in 1991 on the NeXT platform. A few months after the release of Improv, Brio Technology published a standalone Mac implementation called DataPivot (with technology eventually patented in 1999[4]). Borland purchased the DataPivot technology in 1992 and implemented it in their own spreadsheet application Quattro Pro.

In 1993, at the time when the Windows version of Improv appeared, Microsoft Excel 5 was already on the market with a new functionality called a “PivotTable”. This functionality was further improved in later Excel versions:

  • Excel 97 included a new and improved PivotTable Wizard, the ability to create calculated fields, and new pivot cache objects that allow developers to write Visual Basic for Applications macros to create and modify pivot tables.
  • Excel 2000 introduced “Pivot Charts” to represent pivot table data graphically .

Explanation of a pivot table

For typical data entry and storage, data usually appear in flat tables, meaning that it consists of only columns and rows, as in the following example showing data on shirt types:

File:Pivottable-Flatdata.PNG

While tables such as these can contain a lot of data, it can be difficult to get summarized information from them. A pivot table can help quickly summarize the data and highlight the desired information. The usage of a pivot table is extremely broad and depends on the situation. The first question to ask is, "What am I looking for?" In the example here, let us ask, "How many Units did we sell in each Region for every Ship Date?":

A pivot table usually consists of row, column and data (or fact) fields. In this case, the column is Ship Date, the row is Region and the datum we would like to see is (sum of) Units. These fields allow several kinds of aggregations including: sum, average, standard deviation, count etc. In this case, the total number of units shipped is displayed here using a sum aggregation.

How a pivot table works

Using the example above, software will find all distinct records for Region. In this case, they are: North, South, East, West. Furthermore, it will find all distinct records for Ship Date. Based on the aggregation type, sum, it will summarize the fact and display them in a multidimensional chart. In the example above, the first datum is 66. This number was obtained by finding all records where both Region was East and Ship Date was 1/31/2005, and adding the Units of that collection of records (i.e. cells E2 to E7) together to get a final result.

Application support

Pivot tables are an integral part of a spreadsheet application. In addition to Microsoft Excel and numberGo Publisher, competing software programs such as Apache OpenOffice Calc provide similar functionality; the implementation in Apache OpenOffice and LibreOffice up to release 3.3 is called DataPilot. In version 3.4 of both LibreOffice and OpenOffice, DataPilot is renamed 'Pivot Table'.

In 2010 numberGo launched their numberGo Publisher 2.0, an updated application with enhanced pivot and charting capability and simplified interface. Numbergo releases version upgrades throughout the year with new functionality driven primarily from their user base. Other companies such as Quantrix provide similar implementations.

Pivot functionality also operates in other data visualization tools, including business intelligence packages.

Google Docs initially allowed the creation of basic pivot tables via the pivot table gadget from Panorama called Panorama Analytics, but as of 2011 this gadget provided limited functionality and was extremely slow with large amounts of data. In May 2011, Google announced the roll-out of a natively hosted pivot table feature in the Google spreadsheets editor.[5]

ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications.

As an OLAP client

Excel Pivot Tables includes the feature to directly query an OLAP server for retrieving data instead of getting the data from an Excel spreadsheet. On this configuration a pivot table is a simple client of an OLAP server. Excel's Pivot Table not only allows for connecting to Microsoft's solution (Analysis Service), but to any XMLA (OLAP standard) compliant server.

Other OLAP clients are JPivot, Dundas, IcCube (Client Library).

See also

References

  1. ^ Microsoft Trademarks[failed verification].
  2. ^ "Unite States Patent number US 6,411,313 B1". 2002-06-25. p. 10. Retrieved 2010-02-16. PivotTable is a trademark of Microsoft Corporation.
  3. ^ Jelen, Bill; Alexander, Michael (2006). Pivot table data crunching. Indianapolis: Que. p. 274. ISBN 0-7897-3435-4.
  4. ^ Edholm, Yorgen H.; Edholm; McNall, Kristen N.; Lew, Karl M., Patent #5915257, retrieved 2010-02-16 {{citation}}: |first1= missing |last1= (help)
  5. ^ http://googledocs.blogspot.com/2011/05/summarize-your-data-with-pivot-tables.html

Further reading

  • A Complete Guide to PivotTables: A Visual Approach (ISBN 1-59059-432-0) (in-depth review at slashdot.org)
  • Excel 2007 PivotTables and PivotCharts: Visual blueprint (ISBN 978-0-470-13231-9)
  • Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
  • Beginning Pivot Tables in Excel 2007 (ISBN 1-59059-890-3)
Retrieved from "https://en.wikipedia.org/w/index.php?title=Pivot_table&oldid=534373932"