MS Business Intelligence Consulting: Excel Pivot Tables
Pivot tables are most often created directly using Microsoft Excel. Using Excel’s user-friendly Pivot
table wizard, it is easy to pivot, and re-pivot columns so that the columns can be organized
any way the user deems useful for collecting critical business intelligence.
Pivot tables are a simple introduction to the power of business intelligence, and
form the foundation for the design of powerful Analytics Cube solutions.
Pivot tables summarize data into rows and columns of summed up (aggregated) data.
Excel spreadsheets are most often designed as flat, one-dimensional tables,
consisting of data elements organized under column headers. This often makes it
difficult to get summarized business intelligence, particularly when data needs to be grouped
and sub-totaled into any number of categories or dimensions.
PCA's Business Intelligence Consultants overcome this obstacle by quickly aggregating (grouping)
and summarizing large amounts of flat data, giving it depth and the desired aggregate values.
A simple form of a Pivot
table is Cross Tabulation (Cross Tab), which is a two-dimensional view of data. For example, a "Monthly
Sales by Region" Pivot table might show the different sales regions as columns across the top, and the time
dimension e.g. Months, Quarters, Years along the left column. The aggregate sales unit figures for each region for each time period
display in the individual cells. For Example:
The data could also be represented as three columns in a traditional spreadsheet view e.g. Month, Region, Amount,
but the table would be tall and narrow, difficult to read, and thus potentially obscure important business intelligence.
The name pivot derives from the fact that
if you pivot, or rotate one of the fields (in this case the region), up to make them column headers, you get a
much more readable output. By adding
another column to the pivot table e.g. "Channel" one could easily view the units
sold each quarter through various points of sale.
New columns can also be dragged
onto (and off of) the pivot table from a list of available fields. Pivot tables allow
several different types of aggregations for business intelligence including: sum, average, standard deviation,
count, etc. In the example above, sum aggregation is used to show the total number of units
shipped by region and channel over Q1 and Q2.
Pivot Charts
Pivot Charts provide a visual representation of a Pivot Table’s aggregated data,
and a wide range of charting and graphing controls are available including, Microsoft
Excel’s built-in charting functions. Pivot charts are frequently used to view historical
data trends and to measure key performance indicators, providing critical business intelligence. Any number of Pivot charts
and Pivot tables can be combined to provide an Executive Dashboard.