Pivot table

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, 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 graphically represent pivot table data.

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:

While those data can contain a lot of information, it can be difficult to get summarized information. 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 data we would like to see is 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 data point 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 together to get a final result.

Application support

Pivot tables are an integral part of a spreadsheet application. In addition to Microsoft Excel, 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'. Other companies such as numberGo and 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^[update] 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

• Comparison of office suites
• Data drilling
• Comparison of OLAP Servers
• OLAP cube
• Group by (SQL)
• Contingency table

References

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)

External links

• A quick, free Pivot Table Tutorial
• An explanation of pivot tables with ZK
• PivotTable video for Excel 2007, including compatibility mode
• Introduction to pivot tables by Microsoft Business Solutions
• Video Tutorials by DataPig Technologies
• Pivot Table FAQs by Contextures
• Web-based pivot table example
• Free introduction to pivot tables video in Excel 2007