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

Let’s Push Access Services

The last year and a half hits on my Blog have steadily increased (despite my postings steadily decreasing ).  There is a simple reason for this….  People are reaching out to find out about Access Services and yet, frankly, there doesn’t seem to be a lot of good solid info or honest testimonials about this technology, espevcially about the Hybrid concept.  This is really pissing me off …

I have outlined ad nauseum here the advantages of this technology with regards to deployment, performance distribution, even the fact that most Hybrid applications in Access 2010 have limited impact on SharePoint Server operations.  And yet nothing (or next to nothing).

I was stunned when MS decided to include Access Services in Office 365 but the absence of Reporting Services capability pretty much completely destroys that idea.  I have touted AccessHosting.com (owned by Larry Strange who I like very much) and there have been some people heading to him thanx to this Blog.  Please keep in mind that Access Hosting has made a BG commitment to this technology, YOUR technology, and is willing to work wth you on making your experience the best it can be (including Reporting ).

In the interest of fairness I would be glad to review any other offering in this vein of course.

But regardless who you use as your Access Server supplier, I believe that there should be pressure to get more info out there about this technology and perhaps even a little hint of future plans for Access Services so we can go to our clients and make a better case for using Access with SharePoint.

Thoughts?

Dick

What I Did This Summer

I have now returned home to Canada after an extended stay in the Netherlands over the summer working on an Access Services project.  Beyond the fact that there are much worse places to hang out than Holland, I also learned a lot about myself, about my technology and about the entire “Consulting” business, especially for us small-app developers.

About myself I learned that I was capable of discipline .  I could get up at 6:30 and get to the office by 7:30 or 8 and work steadily on one thing until 5 or 6 pm.  First I was amazed that I had no problem with energy or attention.  As a work-at-home developer for 25+ years the draw of the bed for a nap or the dog-walk or the break watching tv is strong and sucks productivity out of me.  I can feel it happening already in the 2 days I’ve been back.  As much as the idea of going to an office every day has always looked to me like going to jail for 8 to 10 hour a day I can now see some value from it.  It also helps that I worked with a great bunch of characters inside a cool business for that time, so maybe I was just lucky.

So my big challenge now is to try to apply this new-found discipline in my regular environment of flitting from project to project within a day.  It’s going to be a challenge but it’s gotta be done (if nothing it will mean making more money actually – what a concept ??).  But it will be hard to do with Grandkids, dogs, wife, friends and generally with the interference of a life.  Frankly I don’t know how people can have a life and work in an office every day.  I have just spent 2 1/2 months doing nothing but work, eat and sleep (no tourism either) and I found I was finally getting enough done in a day … but how do you lay that over your life and not lose out on both the work and the life?  It’s got me thinking to be sure.

Secondly I discovered that Access Services is the real thing.  3 years ago I bet on Access Services and I have had some real successes (and no failures) with the Hybrid version of Access 2010 and Access Services.  Once again the Hybrid has come through strongly in this latest project but I also was forced (against my will frankly ) to include a lot of Browser-only content and I found it didn’t take me long to get the hang of pumping out some compelling and functional Browser-only Access 2010 Forms and Reports.  I have reached the point where I can manipulate the Web Form design almost as quickly and easily as as a client form (but with a lot less functionality of course).

Sure the Macros in WebForms are a little bizarre … and I’m not willing to say it’s just because they’re different, but I have found a set of techniques that seem to get the job done (BrowseTo anyone?) and I have to always keep in mind the limitations of this 1.0 technology.

Thirdly, having been allowed to work on one project 40+ hours a week for 9ish weeks I have once again been overwhelmed by howpowerful Microsoft Access really is as a rapid development tool.  Frankly what we accomplished in that time cost a fraction of alternative solutions and provides a great user-environment for a serious production application ina  real serious business.

I just hope that Microsoft doesn’t drop the ball on Excel and especially Access with their drive to convert Office 15 to a “Google-Killer” while reducing their internal resources and costs.  In the end something will probably have to give and it just might be the Access that we know and love.

The jury’s out on that for me and I will be keeping a close eye on the progress in Office 15 while arguing for Excel and Access as loudly as I can without getting myself turfed out of the “club” …

In the end it’s good to be home and I promise to Blog more … thanx for sticking with me.

Dick

p.s. I can’t possibly thank Mark White enough for the “lead” on this Holland project.  Thank you my friend and I will do whatever I can to return the favour somehow.

p.p.s  I am having serious probems with Access after installing Office 2010 SP1.  It keeps corrupting my VBA code and forcing me to run Access with the DECOMPILE switch to get it back.  I am not sure whether this will be an issue for users who are not editing the code but it is enough for me to be rolling back my SP1 install and suggesting my clients not to upgrade either until MS comes up with a fix.

To run Access in DECOMPILE mode until I get the SP rolled back (which hopefully works) I created a Desktop shortcut for it with the following command-line:

“C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE” /decompile

Be warned !!