The Case For Hierarchies in PowerPivot

It’s interesting to me how the definition of “Business Intelligence” (BI) seems to have evolved over the last few years as the various providers of data analytical technologies try to differentiate themselves from their competition. I think some companies have been playing fast and loose with the acronym “BI” to the point where it has muddied the waters quit e a bit – and that’s probably their plan.

I really don’t care what they want to call what they offer, but one thing I do know is that the PowerPivot Add-In for Excel and SharePoint is a “Traditional” OLAP tool designed strictly for the “Slicing and Dicing” of numeric data sourced from relational On-Line Data Processing (OLTP) database that live on corporate networks. PowerPivot takes that data and produces a virtual “Cube” within an Excel file (and ultimately up on SharePoint) of the data it imports. All this is exposed in Pivot Tables on Excel worksheets with the Outline form of presentation typical of Cube-based OLAP analysis.

I personally first got involved with OLAP data in a project 20 years ago wherein I used a Sybase OLAP server as the source for an Excel VBA-driven model for financial analysis. What struck me as the most significant feature of the OLAP database was how it intrinsically knew not only the structure of the business organization (corporate, region, country, district, city, location) I was analyzing, but it also knew the relationships between aggregate time periods from month to quarter to year) in such a way that I didn’t need to ever worry about what axis of my data was a parent for or a child of. In fact it was physically impossible for me to mistakenly lay out my hierarchies in an illogical way. In addition my users only needed to know that there was or was not a parent or a child for the level I was at in the database’s structure.

There are those who will say that this is a bad thing because it limits the user’s flexibility but that misses out on the fact that:

1. Hierarchies are just an additional feature of an OLAP cube and do not restrict users from doing free-form analysis of any kind and

2. By defining Hierarchies in an OLAP database or PowerPivot data model “The Business Model” across organization, products, time and date, etc. can be controlled, managed and most importantly changed at source and reflect immediately in your BI data summarizations and queries in a safe and consistent way.

2. If a user (or developer) takes advantage of hierarchies in their OLAP database they will be able to get the information out of the database much quicker, much easier and with consistency in reports. Believe me, not only is consistency what senior manages want but that is what they really need. If I’m an EVP or Director and every month the report I’m getting is designed differently because somebody decided to produce some innovative new report using free-form PowerPivot I’m going to freak right out and get someone else to get me the report I REALLY need. It is legitimately important to compare apples to apples every day or month.

So it is my opinion that Hierarchies are a key capability of PowerPivot and that every user or developer needs to know how to use them.

I will discuss this more in the future but in the meantime give Hierarchies and Star Schemas a look …   You will be better for it :-).

Dick