skip to Main Content

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…


by Avichal Singh

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.

My Power Pivot Journey: Personal > Team > Organizational BI

Personal BI:  Where Excel Pros “Start”

Personal BI Excel PowerPivot Power Pivot

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 PowerPivotPro 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

Team BI SQL Database Excel PowerPivot SharePoint Power View

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

Organizational BI SQL Server Analysis Services Tabular Model SharePoint Power View

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

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 8 Comments
  1. Terrific post! Thank you so much for sharing your experience, as we have had a similar path (including viewing Rob’s book & blog as awesomeness) and also love for Sharepoint-PowerView-PowerPivot and ahhhh-to refresh! Would love to learn more about SSAS Tabular approach. Have struggled to find more info on how to implment this.

  2. +1 for more talk about Tabular!

    We’re in the process of moving there as well, but currently only have a few workbooks imported (as separate models.) I’m already noticing that the context of a measure is huge, when Power View makes it so easy to Pivot.

    1. Sorry for not making this clear. The videos that I mention are internal videos on our team’s SharePoint site. So I am unable to share those. All our videos though are specific to our Power View reports so would not be of much interest to outsiders anyway. In essence when we create a new Power View report which we expect would get wide usage, we also create a video tutorial to go along with it. It touches on Power View functionality (filtering using slicers, sorting by clicking on column names, pop-out and pop-in elements to make them bigger etc.) but mostly it talks about the filters and the context of the report, what metrics are being shown and how to interpret them. In general how to use the report. An year ago I would have thought this to be way over kill, but I have realized that users need a fair bit of hand holding in starting to use new reports (this I believe would apply even if I used Excel or another tool to create the reports and is not specific to Power View). And since I cannot sit down with each user (I do sit down and do walkthroughs with many) video tutorials really help out there.

      After this long rant, I will point you to my subsequent blog post Migrating from Power Pivot to Analysis Services Tabular Model which does have a video tutorial embedded. Check it out and at least you would get a flavor of how I am going about it. I use Camtasia Studio to generate these video tutorials and absolutely love it.

      Also check out Rob Collie’s Power Pivot courses with more than 70 hours of video tutorials here
      Enrollments reopen in Jan 2014. If you go on the site you can add yourself to the waiting list.

  3. Excellent, excellent, excellent post.

    Thank you so much for sharing your story.

    There is a lot of good content on this website that is seldom reviewed simply because it was shared a while ago.

    I believe it would be a good idea to make the posts that are most helpful to new PowerPivot users more prevalent. One way this could be accomplished is by pinning them in a place set up for newer users.

    It would have been a shame if I never came across this particular posting.


Leave a Reply

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