To Pivot or not to Pivot

So, after the first proof of concept I am now zeroing in on the 2.0 release and I have to say it is looking pretty gosh darn good. Please remember that the journey of PowerPivot means that while I think it’s great now in a few posts no doubt I’ll be saying it looks like something a misinformed chimpanzee would produce.

The key to understand with PowerPivot is the storage model, this is a big thing and really is central to how Microsoft have devised a tool that enables you on your desktop to crunch the kind of data sets that previously you needed hugely powerful servers to process. As a caveat to this I will remind you all that even a low end desktop these days has more processing power than a high end server of 10 years ago, but that’s honestly not the point!

Microsoft have turned the data storage component on it’s head to ultimately align the tool to usage. “Hang on what do you mean align the tool to usage?”

Simple – when you write a query and trust me basically do that when you set up a pivot table you start with “give me the rows that match some column criteria from a big table of data”. Please don’t tell anyone, but that’s all SQL queries are! Practical example “Select IncidentID from Incident where status = ‘New’ will provide a list off all Incident ID’s in your Cherwell System that have a current status of New, so the columns I am asking for are just “IncidentID”, the rows is all that match the criteria and the criteria is Status = ‘New’. Is everyone still with me? Excellent

So now we’ve got a basic query you can start to see the traditional way a database works, rows are everything. You have to ask for columns because rows are always there, I will always get every row that matches my column criteria either for display (select part) or filtering (where part). A traditional database works like this think of layers of cake or if you’re a Garfield fan layers of lasagne – please don’t overthink it databases don’t have layers of fruit or mince, maybe I should have gone Shrek and said onions, databases have layers just like onions mmmmm Lasagne…. sorry I’m getting off topic. The layers of a database table, the rows are designed to be accessed together and stored together, so space is allocated on Hard Drives ahead of time and then used. This is great for write it and occasionally read it. The challenge now though is thaternest-ted even a basic laptop these days has more power than a database server of 15 years, the internet has also given everyone access to vast quantities of data. The result is that we want to be able to analyse it and to quote one of my favourite lines from Ernest Borgnine “People today have attention spans that can only be measured in nanoseconds.” – Basketball 1998.

We want instant gratification, you ask google (other search engines are available) and Basic Pivotinstantly you get an answer (not necessarily The Answer). Business has the same challenge so a tooling methodology change was needed. A query that we need to understand of crunch large quantities of data is more like show me [arithmatic (count or total of) something] for [these areas]. If that seems dumb let me spell it out, show me total sales by region per quarter. You’ve probably guessed art is not my strongest subject.

Here are a couple of links from PowerPivotPro to further help if my explanation sucks (doesn’t work for you). I’ll continue below…

http://www.powerpivotpro.com/2010/02/surprising-example-of-powerpivot-compression/

http://www.powerpivotpro.com/2015/12/compression-with-power-pivot/

Leave a Reply

Your email address will not be published. Required fields are marked *