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. He is based out of Seattle, WA.

Power Pivot: a revolution in slow motion

By Avichal Singh

The first time you truly experience Power Pivot – not a demo, not some random public data set or someone else’s data – your own business data all lit up using Power Pivot. It is a transformative experience. And you know there is no turning back.

I remember that moment, I remember thinking this is HUGE, this gets BI in the hands of people who really need it. Power Pivot is surely is going to explode.

Sadly, it does not feel like that is happening 🙁

As I have become more involved with the Modern Excel User Group (also on LinkedIn), I have realized that there are way too many people in buckets a) and b) below and not nearly enough in c).

a) Sad smile Unaware. Have not heard of Power Pivot or Power BI
b) Disappointed smile Aware but have not truly experienced the capabilities of Power Pivot
c) Red heart In Love with Power Pivot

Note: Are there stages between b) and c)? Between experiencing Power Pivot and falling in love? Perhaps. But guided by the right hands that path should be really short 🙂

Unaware: Don’t know what they are missing

For the unaware group, I do my part; talking to anyone who would lend me an ear, about Power Pivot and Power BI at any event or gathering. But overall we would need to trust the higher powers 🙂 within Microsoft with that task. However I will make the offerings below to the powers that be:

Power Pivot should be a free add-in for all versions of Excel 2013, just the way it is for Excel 2010. I feel it even makes good business sense for Microsoft. Power Pivot has the potential to attract a large user base and would solidly anchor Office and Office 365 in the rapidly changing world. These users can then be up sold services like Power BI.

Power Pivot add-in should be easily discoverable. Both Power View and Power Map have buttons as part of the standard Excel ribbon, which can be used to easily activate these tools. Not so for Power Pivot.
It takes 8 mouse clicks to activate the add-in so you can launch Power Pivot.
File > Options > Add-Ins > Click Dropdown > COM Add-Ins > Go > Select ‘Microsoft Office Power Pivot for Excel 2013’ > Ok
As the first born in the Power BI suite, it is time for Power Pivot to claim it’s rightful place on the standard Excel ribbon and it would fit perfectly under the Data tab.

Currently in Excel 2013 Desired in Excel 2013
Currently in Excel 2013: Powe Map, Power View Desired in Excel 2013: Power Pivot or Data Model

Note: There was a shift in Excel 2013 to weave in Power Pivot so closely with the tool (and call it Data Model) that potentially many users would not even need to learn or use the Power Pivot window. IMHO, that is just keeping people away from all the goodness and richness of Power Pivot. Let them come, let them explore and experience.

Aware but not in Love: Mind the Gap!

While I may feel helpless in making a dent in the Unaware group, I feel impassioned and even obligated to help the Aware but inexperienced group.

I have spoken to many Excel users and noticed that there is a gap between knowing about Power Pivot and actually trying it out yourself to experience its true power. There is a step there that many users simply cannot take. Being on the other side, it baffles me a bit. I feel like yelling “Hey, come on over, the grass IS greener on the other side!”

From Excel to Power Pivot: Mind the gap!

Read the Rest

Compare product performance after launch

By Avichal Singh

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model


View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model
Read the Rest

Forecasting in Power View and Power BI

Guest Post by Avichal Singh

Intro from Rob:  Never fear, last week’s series is still slated for completion, and in a special way.  Watch this space on Thursday for some fireworks.  For now, please enjoy Avi’s thoughts on the new forecasting component of Power View / Power BI.

PASS Business Analytics conference saw the announcement of a pretty cool Power View feature: Forecasting. I felt lucky to have been there and also to have had the opportunity to attend both of Rob Collie’s sessions (Data Revolution, Industrial Strength Excel). The Data Revolution session, I must say, was unlike anything I expected. No DAX formulas, no bullet points; just a path to data nirvana Smile

The Power View forecasting feature was cool enough that I just had to play with it! I wanted to try it out with a few real world data sets. I ended up using Climate Data and Stock Market performance.

– First a quick look at the Power View forecasting functionality
– Then I show you how I built the files using Power Query (The more I use that tool the more I like it)

You can find the link to the finished Excel file here. You can also watch me walk through the whole process in the video below:

[youtube http://www.youtube.com/watch?v=-rqQBJFMofw&hl=en&hd=1]
Video Walkthrough: Forecasting in Power View and Power BI

Power View Forecasting in a Nutshell

In the ‘cloud first’ spirit that Microsoft has been following, the forecasting feature is only available in the online Power BI site (See microsoft.com/powerbi for more and to sign up for a free trial). To enable the forecasting feature, after opening your file on the Power BI site, you need to switch to the HTML5 mode by clicking on the icon at bottom right.


Click on this icon to enable the HTML5 mode with forecasting functionality

Power View Forecasting in a Nutshell

Read the Rest

Guest Post: Our Power View Story (and Power Pivot Settings Cheatsheet)

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

You read about my Power Pivot journey in my first blog post and in my subsequent blog post I elaborated on migration to Analysis Services Tabular Model (SSAS Tabular). I realize now though, that I did things out of order and need to address that in some way. As my journey outlined, before we switched over to SSAS Tabular, we moved our Power Pivot workbooks to SharePoint and started using Power View Reports. And Power View has been a key element of our success. For this post I’ll go back to the future and speak about

– Our success with Power View

– All the settings in Power Pivot related to Power View

p.s.: When I refer to Power View I am referring to Power View on SharePoint. I am not referring to Power View functionality built in to Excel 2013, since that is a fairly different experience than Power View on SharePoint.

Power View Success Story

I love Power View, except when I don’t. It can feel limiting at times and frustrating, especially to an excel user (which is all of us Smile). After demonstrating a really slick Power View report with all the bells and whistles (check out a sample from Microsoft BI at Power View Demo. Mine don’t look as good as this), the first question I often get from the user is, “Great, now how can I export this to Excel?” And my answer is – you can’t Sad smile
“Export to Excel is the third most common button in data/BI apps…after Ok and Cancel” (click for a real fun post!), and Power View does not have it. Yet! If the powers that be are reading, I think it’s feasible that an icon appears when you hover over Power View report elements, to export the underlying data in excel in a simple table format. Please consider that for the next release. Now that I am in begging mode might as well ask for – ability to re-label measures/column names in Power View Report and show numbers as Percentage of Total (like in Excel Pivots). The latter is doable using DAX but not easily so.

Okay, now let’s move on to some love ©©©

  • Power View reports are easy to build, maintain and use
  • Shared Power View report give you a Single Version of Truth (kinda)
Read the Rest

Guest Post: Migrating From Power Pivot to Analysis Services Tabular Model

Intro from Rob

At a conference three years ago, I saw my former colleagues at Microsoft announce something very significant:  Power Pivot was getting a bigger brother.  Power Pivot’s formula language (DAX) and the overall “tables/relationships/calc columns/measures” modeling experience was going to be “ported” into Microsoft’s flagship, industrial-strength BI platform – SQL Server Analysis Services (SSAS).

At the time, that led me to post the following diagram:


Power Pivot Broke the First Wall – and We’re Still Getting Accustomed to Our New Powers.
But Power Pivot’s Bigger Brother (SSAS Tabular) Breaks a Future Wall Too.

Years have passed and that has already become the new normal.  Microsoft rolled out Power View and had it only work against Power Pivot / Tabular (and not against traditional SSAS) – a decision they later had to dilute, but still a clear signal of their future intent.  BI luminaries like Chris Webb and the Italians wrote a book on Tabular.  As revolutions go, this one was quite non-eventful.

Not that Excel Pros MUST move upstream and start using SSAS.  Quite the contrary – I still personally have not!  But it is VERY good to know that it’s there, for several reasons:

  1. Microsoft is betting heavily on “the Power Pivot way.”  You don’t “infect” your flagship product with something new unless that new thing is awesome.  Power Pivot – that thing running on your desktop – is good enough for the heavyweight BI pros.  Digest that thought.
  2. There’s an “upgrade path” for important Power Pivot models.  This is a great selling point for IT if they are nervous about Power Pivot.  Unlike regular Excel workbooks, a Power Pivot workbook that becomes business critical CAN be “taken over” by IT, and made into something centralized and blessed, without having to rewrite it.
  3. There’s an “upgrade path” for Excel Pros.  With very little effort, an established Power Pivot pro can “change hats” and label herself a Business Intelligence Pro, a Tabular Modeler – even if she were “just” an Excel Pro a couple years ago.  Again, not that she has to, because Power Pivot itself offers practically limitless power.  She just can.  Exciting huh?

Avichal Singh is one such Excel Pro who has been making that transition.  He’s been so kind as to write up his experiences so far, and MAKE US A VIDEO SHOWING US WHAT THAT LOOKS LIKE!  (Holy cow I had not realized there was a video in this post until just this moment.  I am Jack’s Awestruck Amazement.)

Take it Away, Avichal…

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

This is a follow up to my original post – My Power Pivot Journey. In this post I would like to detail the migration to Analysis Services and its implications we experienced. For general comparison of Analysis Services versus PowerPivot read Comparing Analysis Services and PowerPivot (look for table under Analysis Services Offerings). Our main reason for switching to Analysis Services was scalability. We were reaching 100MB with Excel+PowerPivot and after migration to SSAS Tabular our database has grown to 700+MB with 25+ Million rows overall.

My (Non) Background as Developer

If you are like me, you might have opened up Visual Studio in the past in a misguided attempt to develop something or opened up someone else’s project and felt somewhat lost. Do not let that fear stop you.

Developing SSAS Tabular Model in Visual Studio is not that much different than doing it in Excel PowerPivot. Authoring your model will feel surprisingly similar, but you will find more changes around deploying and using your model.

In the video below and the rest of the article I will show you, exactly – how to migrate, what changes to expect and how to use the new interface.

PowerPivot to SSAS Tabular: Migration and other tips

Read the Rest

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…


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.

My Power Pivot Journey: Personal > Team > Organizational BI
Read the Rest