My Power Pivot Journey: Personal > Team > Organizational BI

Guest Post by Avichal Singh

Intro from Rob:  an exciting guest post for sure…  a Power Pivot “adoption story” from inside Microsoft itself!  Over my years in Redmond, I met MANY Excel pros who were working at MS as analysts in the financial and accounting units.  Today, card-carrying Excel Pro Avichal Singh shares the progression of one such team, and how he helped “grow” the usage of Power Pivot all the way to the border of “traditional BI.”  It’s a fascinating glimpse at potential futures – both for Excel pros and their organizations.

Take it away, Avichal…

Intro

by Avichal Singh www.linkedin.com/in/avichalsingh/

If you have seen any of the promotional material for SQL Server/PowerPivot, it’s likely you have come across the slide which speaks to the spectrum of Personal BI, Team BI and Organizational BI. My journey started at personal BI, and I didn’t quite know then, how far it would take me.

Source: Analysis Services & PowerPivot Blog

UPDATE Jul 2014: Now you can also watch the video version of my story.

Personal BI:  Where Excel Pros “Start”

In one of our email exchanges Rob asked me about my background – did I come to PowerPivot as an Excel person or a BI person? Even though I have an old MCSDcredential and a Masters in Computer Science, my role has been of an Analyst. And I came to PowerPivot, as a dyed in the wool Excel guy. My job was to pull data, crunch it to gather insights and present it in a visually effective manner (dashboards galore!). I had used a few different tools but none worked quite as well as Excel. (Why is Excel so good? Read about Excel’s Internal Network Effect)

Along came PowerPivot, and till that point I didn’t quite know what I was missing. Being able to load millions of rows of data was liberating. Ability to link tables and banish Vlookup was fantastic. But I felt the revolution was in DAX Formulas! Once I had the data and lookup tables in place and linked via a relationship, I could write one DAX formula and slice and dice the measure any number of ways. It was as if, I could make the data speak to me!

It was bit of a struggle getting started though. How I wish I had Rob’s Bookback then. I expected relationships to work like a SQL inner join, which they didn’t. (See Chapter 10- Thinking in Multiple Tables; where Rob uses a beautiful analogy that PowerPivot ‘Relationships’ flow downhill). But by reading online (that’s how I found the P3 Adaptive blog) and trial and error I was able to move along. Quickly creating sophisticated models and drawing insights which I shared with my team.

Team BI

I happened to switch to a different team but carried on my work with PowerPivot. I had closely watched Project Crescent (now Power View), and finally after it was rolled out, I had access to it via our SharePoint site. I leveraged that – uploading my PowerPivot models to SharePoint and setting up automatic refresh. Then creating Power View reports I could share with my team. I love Power View but have found it to have the rough edges you would expect with a v1 product. However, it’s encouraging to see the pace of improvements (See What’s new in Power View, Power BI). I agree with Rob, that Power View in Excel 2013 feels grafted, and does not participate in the network effect. But Power View on SharePoint feels much more natural.

Whether you set up your own SharePoint infrastructure or rely on a cloud provider, that is indeed the way to go. It magnified my impact at least 10x. Finally I could easily share my work, and the automatic refresh were a gift from heaven. Meanwhile I had also switched to using a Database to feed PowerPivot (SeeWhy PowerPivot is Better Fed From a Database) and upgraded to Excel 64-bit to allow PowerPivot more room to play.

Organizational BI, Further Resources, and Next Steps

From that point on, we grew like gangbusters. But easily could have been a victim of our own success, if we didn’t learn a few lessons fast! The following helped:-

a) Rob Collie’s Book and blog. Let’s just say, I’m a fan! Tip of a hat to all other bloggers and forum posters who have helped me as well.  (Note from Rob:  Thanks!  Glad it helps!)

b) Dimensional Modeling from Ralph Kimball
I had heard the terms star schema, dimensions and facts, but never lived in that world. My world had either been Excel Sheets or SQL tables. Power Pivot masquerades as a set of excel-like sheets, but as you get better at it, you are unknowingly learning the art of “dimensional modeling.”   (Note from Rob:  hey Excel Pros, consider adding “Dimensional Modeling” to your resume for triple word score!)

Reading up on these principles in Ralph’s Book The Data Warehouse Toolkit helped guide us a great deal. Although I would confess, that I still struggle with applying some of these in practice.

c) Release Management, Bug tracking
These concepts are the bloodstream of any typical software development team, but usually alien to Excel guys, like me! Luckily, I did have some exposure to these from my previous roles, and was able to apply these elements as the need arose.

– Bug Tracking: I used a simple SharePoint list to track all the changes made to PowerPivot model

– Developer & Production Environment: We separated development and production environment. This allowed me to freely and rapidly make changes in the development environment while keeping the production version stable

– Release & Production Testing: I attempt to bundle changes together, and deploy to Production when ready. After deployment I also test all existing Power View reports. It’s a little tedious, but a lesson I learnt the hard way (simple changes can at times catastrophically break your model or reports L).

d) Evangelism & User Collaboration

As much time as I have spent crafting my PowerPivot models, I have spent even more, sitting down with business users. It’s a two-way street. I try to act like a sponge, learning as much about the business as I can. I try to put myself in their shoes. On the other hand, I educate them about the tools (teach basic Power View functionality) and my model (measure definitions etc., data dictionary kind of stuff). But also about the finer points of using the data – why averages can be misleading (helps to also check the distribution of your data), why pie-charts are not as effective as simple histograms, etc.

I spend time, one on one with users, in walkthroughs and brown-bags, in virtual calls with users across the globe and we now also have a catalog of video tutorials to help our users (don’t have a fancy recording studioyet 😉 but I do use Camtasia which is a great tool).

Upsizing to SSAS Tabular

We also switched from using Excel PowerPivot files uploaded to SharePoint to using SQL Server Analysis Server (SSAS) to host our tabular model. Interestingly the big reason for the move was a self-imposed file size limit on our SharePoint site, which the Excel/PowerPivot models ran into. However I have learnt to appreciate some of the other benefits of this move (e.g. scalability, finer grained control).

(Note from Rob:  I would LOVE to hear more about this.  When you take your Power Pivot skills to South Beach Visual Studio, an Excel pro truly becomes a full-on BI Pro.  I would love to hear about how that transition “felt,” whether it was natural or intimidating, etc.)

Road from here…

I wish this was the happy ending, where the good guys rode off to sunset. Not quite. Power Pivot/Power View has certainly arrived in our organization. But we still have ways to go, before we can claim outright success. Challenges remain, one being, finding the place of this new BI amongst the pantheon of traditional/old BI.

But I have thoroughly enjoyed the journey, and the company of the people I have been lucky to have. A big thanks here, to our resident DB expert, an “anthony of our own”!  I would love to hear from all of you how your PowerPivot journey has been, realizing that it may not even move through the Personal/Team/Organizational phases. Someone could stay in Personal BI space but go really deep as well.

Disclaimer: The opinions and views expressed in this post are those of the author and do not necessarily state or reflect those of Microsoft