PowerPivot CTP3 Hierarchies Make All The Difference

I have been around OLAP databases since 1994.  By “around” I mean that I have used them off and on for all those years without actually becoming a true BI dev – rather I am an occasional consumer.

This is because I have focused on Excel and Access and SQL Server (and lately SharePoint), developing transactional databases and budgeting and reporting documents using standard aggregations of business data.  Having the data ensconced in a Data Warehouse with OLAP dimensions and Fact tables occasionally has been a big asset for me and frankly I’ve never understood why BI isn’t more broadly used.

Of course the answer has been that BI has been the realm of the BI gate-keepers in every organization I have known and the last things they seem to want is for anyone to actually get any value out of their precious snowflake designs  without their help :-).

Also it seems that to be a BI dev you have to be an MDX guru and frankly it is your job to make the whole thing so complex and inpenetrable that the clients or their company needs them to use the technology – job security anyone?

Now with the release of PowerPivot Microsoft is trying to make BI available to the general IW marketplace but to date it seems to me to have been designed primarily for those same BI gurus with the introduction of the DAX “language” (or whatever you call it) into the PowerPivot environment.

As I have mentioned before, I have found DAX an almost impossible hill to climb.  I always thought and now I am convinced that this is because I am fundamentally a relational database guy and I believe that nearly everything I need to do in BI can be accommodated in the data sources themselves (either in SQL Views or Functions or in Access queries), but that you needed complex, convoluted DAX expressions if you wanted to do the one thing that I believe is the core of BI – Hierarchies.  Without intrinsic hierarchies I see very little value to a product like PowerPivot (other than its amazing capacity) as anything but just another way to do aggregations and to source Pivot Tables.

But Hierarchies in PowerPivot CTP3 change everything for me.  You see, I have used and plan to use BI hierarchies as the secret to a reliable, flexible accurate reporting capability.  With Hierarchies I only have to define the hierarchical order of the Dimensions of my database and I can then roll up my data at any level up and down and across my financial, sales, production or budgeting data in a flash, and as new values show up or disappears in the data it gets automatically assigned to the correct level in the hierarchy and reflects immediately in all levels of roll-ups.  This means a reporting capability that does not require the client to call me every time they add a new office or SKU or reorganize their regions and restructure their management team responsibilities.  Everything is managed from tables through the established hierarchies of the “Cube”.

A properly designed OLAP data store should be able to handle all these changes easily and consistently while maintaining the value of historical data and be automatically able to accommodate the future.  And it is Hierarchical Dimensions related to Fact tables of core data that are how most of us can get value out of BI that we can’t get any other way.  Of course there are other more complex uses of OLAP design, but for the vast majority of business users THIS is the core use of BI … to do aggregations up and down and across their business data, across time in various increments, looking for jewels of revelation, for planning and budgeting help, for tracking KPIs and for standard period-end management reporting.

Lets explain …  By assigning your corporate structure to a Hierarchy such as World, Region,Country,State or Province, County, City you can expand a level in a pivot table and it will KNOW what the CHILD level should be shown automatically by design.  Without Hierarchies those relationships will have to be defined in every Pivot Table of CUBE Formulas usage you make from the data.  This way the levels of the hierarchy can now be defined in PowerPivot  at the table level and are inherited every time you create a report.  The key is that the user does not need to know that hierarchy and they can’t screw it up as easily on a report – it is “baked in” the data source.

So now that PowerPivot will have Hierarchies I believe that I will be able to do the vast majority of the things I used to have to do using DAX functions by using Hierarchies instead.  I also believe that if I am a SQL Server or Microsoft Access Developer I can do most if not all of my convolutions (transformations?) of data there rather than by using DAX, thereby being able to apply my existing knowledge to this new capability and also it will allow me to manage my data at the source rather than in some abstract middle-ware.  I will use PowerPivot simply to manage my Hierarchies in my dimension tables to allow me to create cool new reports in Excel and/or SharePoint while managing the data at source as much as possible.  Use the right tool for the right job.

This is NOT a criticism at all of DAX or PowerPivot, this is just an alternative way to approach using this product.  Frankly it looks to me like DAX is a product for use by BI people who aren’t necessarily strong in Relational databases (which seems odd to me) and who definitely are not Excel gurus.  If you are going to use ALL these tools together (data sources in SQL or Access, etc,  Hierarchies in your Snowflake OLAP design in PowerPivot and your newly powerful and capable Excel Pivot Tables and CUBE formula-driven spreadsheets in either SharePoint or on the client) then the addition of Hierarchies in PowerPivot is the final shoe to drop.

Now we can get to work.

Dick