A pivot table is a table that summarizes data from other tables, and is created by applying operations such as sorting, averaging, or summing data in the first table, typically including data groupings. Trivial example is the number column as the first table, and the average column as a pivot table with only one row and column. A somewhat less trivial example is a table with columns sex and high , each row gives a gender and a height of a person, and a pivot table is created from it with two equal columns but only two line, with "male" and "female" in the sex column and the mean height of each gender in the high column. A third, more complex and more general example adds another dimension to the pivot table: the data table has columns months , the seller , the product and the sales , each row gives the total sales of some products by some salesperson within a given month, and the pivot table created from it has the first column the seller the name of the salesperson and the other column the name is the product name and contains the total sales by the seller of the product in all months.
The pivot table is used in data processing and is found in data visualization programs such as spreadsheets or business intelligence software. Such programs can automatically sort, calculate, total or averaged data stored in a single table or spreadsheet, displaying the results in the second table - pivot table - showing the data being summarized. Users often organize and change the summary structure by dragging and dropping fields graphically. This "rotation" or rotation of the summary table gives the concept of its name.
The pivot table is associated with the contingency table (also known as cross tabular or crosstab ), but the pivot table is considered more dynamic, and can have operations performed on it, while the contingency table is static display of data.
The term pivot table is a generic phrase used by many vendors. In the United States, Microsoft Corporation has trademarks of certain compound words, PivotTable .
The pivot table can be viewed as simplifying the more complex and complex concept of OLAP (online analytics processing).
Video Pivot table
History
In their book Pivot Table Data Crunching , Bill Jelen and Mike Alexander refer to Pito Salas as "the father of the pivot table". While working on the concept for a new program that will eventually become a Lotus Improv, Salas notes that the spreadsheet has a data pattern. Tools that can help users recognize these patterns will help build advanced data models quickly. With Improv, users can specify and save sets of categories, then change the look by dragging category names with the mouse. This core function will provide a model for pivot tables.
Lotus Development released Improv in 1991 on the NeXT platform. A few months after the Improv release, Brio Technology publishes a stand-alone Macintosh implementation, called DataPivot (with technology eventually patented in 1999). Borland purchased DataPivot technology in 1992 and implemented it in their own spreadsheet application, Quattro Pro.
In 1993 a Microsoft Windows Improv version appeared. Beginning in 1994 Microsoft Excel 5 brought a new function called "PivotTable" to the market. Microsoft further enhances this feature in higher versions of Excel:
- Excel 97 includes 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 Macro Applications to create and modify pivot tables
- Excel 2000 introduces "Pivot Charts" to represent pivot-table data graphically
In 2007, Oracle Corporation created the PIVOT and UNPIVOT operators available in Oracle Database 11g.
Maps Pivot table
Mechanics
For data entry and data storage, data is typically displayed in a flat table, meaning it consists only of columns and rows, as in the following sections of the spreadsheet example that displays data on the type of shirt:
Although such tables can contain many data items, it may be difficult to get summary information from them. The pivot table can help summarize data quickly and highlight the information you want. The use of pivot tables is very broad and depends on the situation. The first question to ask is, "What am I looking for?" In the example here, let's ask, "How many Unit we sell in every Region for each Ship Date ":
The pivot table usually consists of the row field, columns and data (or fact ). In this case, the column is Delivery Date , the line is Territory and the data we want to see is (number) Unit . This field allows for some types of aggregation, including: number, average, standard deviation, count, etc. In this case, the total number of units sent is shown here using the sum aggregation.
Implementation
Using the example above, the software will find all the different values ​​for Territories . In this case, they are: North , South , East , West . Next, it will find all the different values ​​for Ship Date . Based on the type of aggregation, sum , this will summarize the facts, the number of Units , and display them in a multidimensional chart. In the example above, the first datum is 66. This number is obtained by finding all records where both Region is East and Date of Ship is 1/31/2005 , and add the Unit of the collection of recordings ( ie , cell E2 to E7) together to get the final result.
The pivot table is not created automatically. For example, in Microsoft Excel one must first select all data in the original table and then go to the Insert tab and select "Pivot Table" (or "Pivot Chart"). The user then has the option to insert the pivot table into an existing sheet or create a new sheet to place the pivot table. A list of pivot table fields is assigned to a user that lists all of the existing column headers in the data. For example, if a table represents a company's sales data, it may include the Date of sale, Sales person, Item sold, Color of goods, Units sold, Price per unit, and Total price. This makes the data more accessible.
The field to be created will be visible on the right side of the worksheet. By default, the pivot table layout design will appear below this list.
Each field from the list can be dragged to this layout, which has four options:
- Filter report
- Column label
- Line label
- Summation value
Report filter
Report filters are used to apply filters across tables. For example, if the "Item Color" field is dragged into this area, then the constructed table will have a report filter inserted above the table. This report filter will have a drop down option (Black, Red, and White in the example above). When an option is selected from this drop-down list ("Black" in this example), then the table to be visible will only contain data from rows that have "Item Color = Black".
Column label
A column label is used to apply a filter to one or more columns that must be displayed in the pivot table. For example, if the "Reseller Person" field is dragged into this area, then the constructed table will have values ​​from the "Sales Staff" column, ie , someone will have the same number of columns as the "Salesperson" number. There will also be an additional Total column. In the example above, this instruction will create five columns in the table - one for each sales person, and Total Grand. There will be a filter above the data - column label - from which one can choose or not select a specific sales person for the pivot table.
This table will not have a numeric value because no numeric fields are selected but when selected, the value will be automatically updated in the "Grand total" column.
Line label
Line labels are used to apply filters to one or more rows that should be displayed in the pivot table. For example, if the "Reseller Person" field is dragged in this area then another created output table will have values ​​from the "Salesperson" column, ie , someone will have the same number of rows as the "Person Seller". There will also be an additional "Grand Total" line. In the example above, this instruction will create five rows in the table - one for each sales person, and Total Grand. There will be a filter above the data - line label - from which one can choose or not select a specific sales person for the Pivot table.
This table will not have a numeric value, since no numeric field is selected, but when selected, the value will be automatically updated in Row of "Grand Total".
Summation
This usually requires fields that have numerical values ​​that can be used for different types of calculations. However, using text values ​​also will not go wrong; instead the Sum will give a count. So, in the example above, if the "Units sold" field is dragged into this area along with the "Sales staff" line label, then the instruction will add a new column, "Number of units sold", which will have value against each sales person.
App support
Pivot tables or pivot functions are an integral part of many spreadsheet applications and some database software, as well as found in data visualization tools and other business intelligence packages.
- Spreadsheets
- In addition to Microsoft Excel, competing software programs such as Apache OpenOffice Calc provide similar functionality; implementation in Apache OpenOffice and LibreOffice until release 3.3 is called DataPilot. In version 3.4 of LibreOffice and OpenOffice, DataPilot is renamed to "Pivot Table".
- Google Docs initially allowed the creation of basic pivot tables via a pivot table gadget from Panorama named Panorama Analytics, but in 2011 it provided limited and very slow functionality with large data sets. In May 2011, Google announced the launch of a privately hosted pivot table feature in Google's spreadsheet editor.
- Database support PostgreSQL, a relational database management system, enables the creation of pivot tables using the tablefunc module.
- MariaDB, MySQL fork, allows pivot tables using CONNECT storage engines.
- Microsoft Access supports pivot queries with the "crosstab" query name.
- The Oracle database supports PIVOT operations.
- Some popular databases that do not directly support pivot functions, such as Microsoft SQL server and SQLite can usually simulate pivot functions using embedded functions, dynamic SQL or subqueries. The problem with rotating in such cases is usually the number of output columns must be known at the time the request starts to run; to rotate this is not possible because the number of columns is based on the data itself. Therefore names must be hard coded or queries that must be executed to be dynamically created (that is, before each use) based on the data.
- Web app
- ZK, the Ajax framework, also allows embedding pivot tables in Web applications.
- Programming languages ​​and libraries
- The programming language and the corresponding libraries for working with table data contain functions that allow the creation and manipulation of pivot tables. Python panda data analysis toolkit has the pivot_table function, and the xs method is useful for obtaining sections
- Aggregate function
- Business reporting
- Office suite comparisons
- OLAP Server Comparison
- Contingency table
- Data excavation
- Data excavation
- Data warehouse Bold
- Extract, change, load
- Fold (high order function)
- Group by (SQL)
- OLAP cube
- Relational algebra
- Full Guide to PivotTable: A Visual Approach (ISBNÃ, 1-59059-432-0) Ã, (in-depth review at slashdot.org)
- Excel 2007 PivotTable and PivotCharts: Visual blueprint (ISBN 978-0-470-13231-9)
- Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
- Starting the Pivot Table in Excel 2007 (ISBNÃ, 1-59059-890-3)
pivot table.
Online analytical process
Excel pivot tables include a feature to directly request an OLAP server to retrieve data instead of retrieving data from an Excel spreadsheet. In this configuration, the pivot table is a simple client of an OLAP server. Excel PivotTable not only allows to connect to Microsoft Analysis Services, but for OLAP standard XML server for Analysis (XMLA).
Other OLAP clients are JPivot, Dundas, IcCube (Client Library).
See also
References
Further reading
Source of the article : Wikipedia
0 Comments