Pivot, PivotTable and PowerPivot
June 2, 2012
Posted by on
OK, I am not a data guy. Or let me say I am not an Excel (MS-EXCEL) guy. I have always believed that if you are good at PowerPoint (along with Visio) you probably are an architect and if you are good at Excel you are most likely to be a manager 🙂 . But not knowing Excel well, has always given me a feeling of – “something is missing”. After all, it’s the same data, but the way few people present it, they just make it look so good. While it takes time to develop those skills, understanding Pivot and related features is usually a good step in that direction.
Excel primarily is a spreadsheet tool in which you can dump some data. At times though, data is not everything. For e.g. your retail transactional system captures the sales across product lines, but if a manager needs to predict his stocks for next fiscal based on historic sales, he got to make some sense out of that data. That’s where you got to Pivot the data. The word ‘Pivot’ as Wiki generally describes it – is center point of any rotational system. You have seen them in action while helping your kids on a seesaw. Pivot table on other hand is a data summarization tool found in spreadsheet softwares like Excel. If you have to bridge this tool and the word ‘Pivot’ – you can think you are bending your data to a specific viewpoint using functions like sort, total, average, count, etc. Let’s see it with help of a trivial example. Consider below the sample data in Excel. To bend it to monthly stock view you can click on Insert tab and insert a Pivot Table. This would give you an empty pivot table and a PivotTable field list. Drag and drop your fields to specific areas to bend your data to a viewpoint (N.B. you can have multiple fields for each area and control them via expand / collapse buttons and all your charting knowledge works as is with Pivot tables).
PowerPivot as the name suggests elevates the Pivot table feature to next level making it full-fledged self-service BI tool. An Excel Add-In, PowerPivot connects to almost any external data sources including SQL Server Analysis Services. Once fetched, data stays inside the workbook and using excellent compression techniques the file size on the disk is still relatively small. You can share your PowerPivot workbooks via SharePoint and configure refresh cycles for your workbook to ensure your team always makes their decisions based on recent data. The experience of pivoting though remains same as shown earlier.
Hope that helps 🙂 .