Pivot Tables As A Business Intelligence Tool

pivot table iconIf you want to explore Business Intelligence (BI) tools further, you may want to find out how you can use pivot tables in your data analysis. This is a summarization tool that will allow you to manipulate raw data into a more visually appealing and simple yet comprehensive report. The whole purpose of this tool in BI is to simplify the process of sorting, totaling and averaging data without compromising the whole report that you need to show.

A pivot table is best used with a spreadsheet. As any user sets up the data, they can easily drag and drop the necessary structure that will complete their report. This pivoting or rotating of the summary table is where the name of this tool comes from.

While Microsoft Corporation trademarked PivotTable for their own use, pivot tables are actually used by a lot of vendors.

When you are using this tool for data analysis, you usually begin with flat tables. This refers to tables that only consists of columns and rows. To summarize the information, you need to ask yourself first – what do you want to look for in this raw data? Once you know the data that you want to look at, you can create the pivot table.

Now if a flat table is composed of columns and rows, pivot tables are composed of columns, rows and data fields (sometimes called as fact fields). A pivot table will allow you to automatically get the sum, average, count or even the standard deviation of the data that you want to see. It will be displayed in a multidimensional chart that accurately goes through the whole data and gives you only what you need. You do not have to go through rows and rows of information to tally the results. The pivot table will do that for you.

Take note that a pivot table, although it can automatically get the data from you, will still require you to select the exact data that you want it to look for you. It will still need you to prompt what it will automatically summarize for you. When you start, for instance in MS Excel, you usually prompt the program to insert a Pivot table. You will then be taken into a new table where you will find a list of column headers that came from your data source. Any field that you will create will be visible on the worksheet’s right hand side. There is a default pivot table layout that will appear at the bottom of the list that will give you four options. You can either drag a field as a report filter, column label, row label or summation value.

By placing the right field in the right option in the layout, you can create your pivot table easily. Once you run the table, you will see the data that you need – whether you want it to be in total, average, etc.

Pivot tables may look intimidating to create but if you concentrate on the step by step process, you will find that it actually simplifies your data analysis in ways that you never thought possible.

Image courtesy of PivotTables.com