Post by Rob Collie

Power BI Designer:  A Good Thing for the Power Pivot Revolutionary in Your Life

This is Power BI Designer, a New Product from Microsoft, and It’s Relevant to ALL of Us.

Major Seismic Activity out of “Mount Redmond”

Some of you may have seen it already, but just in case you haven’t:  in recent months, Microsoft has been touting a preview of what it calls Power BI Designer.  You can download it here for free, and install it right next to all of your other tools, TODAY.

I’ve held off, a LONG time, in writing about Designer.  Because I wasn’t yet sure what to make of it.  I’ve been wary of it, critical of its existence.  It’s taken many, MANY off-the-record conversations with my former colleagues at Microsoft, and a lot of reflection, but now I am ready to talk about it, and even endorse it – with a few caveats near the very end of this post.

The Many Desktop Applications of Power BI

My Start Menu is Getting Crowded with Data Tools:
Excel 2010, Excel 2013, Power BI Designer, and Power Update

Designer is Clearly…  Familiar to Us

Once you install it, you immediately start noticing some similarities to things we know and love:

Power BI Designer Basically Contains Power Query, It's Just Not Called That

Same Basic List of Data Sources We See in Power Query

And then the ribbon has some old friends for us as well…

 

Power BI Designer Basically Contains Power Pivot, It's Just Not Called That (And Isn't 100% There Yet)

Familiar “Faces” from Power Pivot and Power View on Designer’s Ribbon

So is this a…  Competitor to Our Beloved Excel-Based Power BI Tools?

Great question.  Let’s see if I can answer it:  Yes.  I mean, no.  Well, um, maybe.

Let’s skip the “competitor” question for the moment, as it’s a bit too black-and-white compared to reality, and instead focus on what it IS…

I think we can safely say that, at minimum, Designer is an alternative to Excel-based Power BI.  Which, of course, leads to more questions than it answers, so let’s dive in.

We need…  a Diagram!

This was my first cut at explaining how Designer compared to Excel*** :

Comparing Excel Power BI vs. Power BI Designer

Excel and Designer:
Two Different Environments for the Construction of Power BI Models, Reports, & Dashboards
(Click for larger version)

(***Note:  from here out, I’m just going to say “Excel,” but when I say “Excel,” I mean the Power BI aspects of Excel, and not ALL of Excel, OK?)

That diagram is meant to convey a few things:

  1. Both tools have an “Environment” layer – think of this as the container, the thing you select from your Start Menu in order to get started.  Obviously this is quite different for each tool, but there is still an important similarity between them, which is…
  2. Each Environment is primarily intended to be the place where you CREATE stuff.  You CAN use that same environment to share your work (ex:  by emailing a Power Pivot workbook to someone else), but sharing via the web and/or mobile apps is better (YouTube for Data, as I call it)
  3. Both tools have an Engine layer.  This is the “heavyweight” part of each tool.  The heart AND the brain combined – crucially important, but invisible to most people.
  4. The Engines are the same!  More on this below.
  5. Both tools have a Visualization layer – this is the place where consumers of your work actually SEE it.  Unless they have the Data Gene, and most people don’t, they don’t care about your queries, formulas, or links between tables.  They just want to see the answers, essentially.
  6. The Visualization layers are quite different.  Essentially zero overlap here.  Excel has the usual suspects – pivots and cube formulas, plus a somewhat half-baked version of Power View starting in 2013.  Designer “only” has Dashboards, which is a complete “reboot” of Power View plus a bajillion brand-new visualizations (ex:  Tree and Funnel, both of which are pictured at the top of this post).  The list of visualizations is expected to constantly (and rapidly) expand.

That’s all important, but I think it may help to draw it another way, too, so here goes:

The Engines and Languages are the Important Thing!

Comparing Excel Power BI vs. Power BI Designer

If you’re reading this, chances are good that you’re not a “Visualista.”  As carriers of the Data Gene, around here we know that the numbers are the important thing, and numbers come from the “hard” stuff:  formulas, relationships, data, queries, etc.

Numbers come from Models, in other words, and Engines are what run the Models.  The DAX engine (Power Pivot) and the M engine (Power Query) are the real gamechangers.

Visualization is the critical last mile, and an important selling point.  But no business ever became more efficient by adopting a new charting engine.  (That sentence is best pronounced with a disdainful, dismissive sneer.  Try it, it’s fun!)  Visualization has been sold to the world as if it’s an answer in and of itself.  And that’s total bullshit.  If you introduce yourself to me as a Visualization Expert, our relationship is gonna start on the wrong foot, but if you say Visualization is important, I’m 100% with you.

So, the engines, the languages, are the most important things, and they are the SAME between the two environments.  That was worth underlining – few points warrant this much “over-communication,” but this one does.

Why Did Microsoft Build a Second Environment?

Aha!  The crux of it all.  This one is relatively easy to explain, in four primary reasons:

  1. Designer removes an important deployment and adoption barrier:  64-bit Excel is hard to get, and 32-bit Excel is unreliable for Power BI.  (Unless, of course, you are intrepid enough to run The Patch.)
  2. Designer can be rapidly improved via monthly releases.  Power BI originally tied itself to Excel for good reasons that remain valid, but that also meant tying itself to the complex and difficult-to-extend Excel codebase (it took a team of ten engineers TWO YEARS to add Slicers to Excel, for instance), AND the Office release cycle has traditionally been measured in years.  Power BI needs to improve rapidly, on a monthly basis.
  3. Web Excel is/was not particularly friendly to mobile devices.  The original Excel Services, aka Web Excel, was built for the Age of Desktop/Laptop Browsers, not for mobile devices.  The Office team is rapidly working to change that of course, but their focus for that is not necessarily 100% aligned with the needs of the “BI Publish and Consume” strategy.  So the BI team decided to build a rendering/publishing environment that is completely separate from Web Excel…  and that, in turn, necessitated a new tool in which to Create/Author those visualizations.  Designer was born.
  4. Designer avoids the “Excel stigma.” Microsoft has struggled to sell Excel-based BI to the BI audience –  a group that regards Excel as “amateur hour.”  This, in my opinion, is the saddest reason of all.  Excel is THE most-loved and most-used data tool in the world, and its users are overwhelmingly “pro-Microsoft” people (this is because Excel is so great, BTW – their love of Excel leads to a love of Microsoft, not vice versa).  Microsoft could – and should! – go on the offensive, change the conversation, AND directly reach the Excel crowd.  Allowing this stigma to stand is a perfect example of the siloed org structure at Microsoft, but I remain optimistic that we WILL see them take up this fight.

Can I Use Designer and Excel in Parallel?

Yes, this is where the good news continues, especially if you are using Microsoft’s Power BI Online service as your sharing mechanism (this is MS’s preeminent “YouTube for Data” experience.)

Whether You Use Designer or Excel, Your Power BI Creations Can Be Uploaded to Power BI Online

1) You Create Using Excel or Designer on Your Desktop, then 2) Upload to Power BI Online, and then 3) Consumers can view and interact with your results via web browser or mobile device.

Wait, Excel Visualizations are “kosher” in the Power BI Online Service???

Yes, Web Excel *IS* coming to Power BI Online.  I’ve been assured numerous times, on and off the record, that it’s in the works.  And it’s coming very soon – this is not some distant fantasy where they say “we’re aware of it and it’s on our roadmap.”  It’s been described to me as Almost Done.

So, your Designer Dashboard visualizations AND your Excel-based visualizations will both be able to live side by side in the Power BI Online service.  One stop shopping for consumers, as it were.

Where that leaves “Web Excel is not optimized for mobile devices” is unknown to me.  Maybe Web Excel is getting better, and WILL be good on mobile devices?  I honestly don’t know, but it’s safest to assume, for now, that for mobile devices, Power BI Dashboards (aka Designer Visualizations) are the clear choice.

OK OK OK…  What Does This Mean to US?

Yes indeed, let’s get selfish! I think this means a few things for those of us who currently use the Excel-based version of Power BI:

  1. If we’re honest with ourselves, our primary “investment,” as professionals, has been learning the Power BI languages (DAX for sure, and more recently M for some of us).  Given that those languages are very much the core languages (the only languages, really) of the Designer, well…
  2. …we were just “gifted” another place where our professional investments pay off.  This, in itself, is a Good Thing.  Imagine running into the dreaded Excel Stigma with some newly-interfering executive at work, and then just…  pivoting to the side and saying “oh, ok no worries, we’ve got this dedicated BI environment…”  and that saves the day.  This WILL happen in many of our lives.
  3. We get a whole new slew of visualizations.  No more trying to “fake” a funnel chart using Excel bar charts for instance.  Good maps, too.  And really, this list will grow forever and rapidly.  A gift that keeps giving.
  4. We gain increased mobile device relevance.  A big barrier to executive buyoff just falls away.
  5. We sidestep the 64-bit issue.  Another buyoff / deployment barrier falls.
  6. We get to choose the right environment (Excel or Designer) for the job.  Primarily, this will be determined by visualization type – I don’t think Designer will every quite match the network effect of the Excel grid, or the amazing things you can do with cube formulas.  But I also don’t expect to see Tree decomposition charts in Excel any time soon, so this goes both ways.
  7. Compatibility and Portability become our primary concerns.  All of the work we do in one environment…  we want that to “count” in the other environment.  Can I take a model that I built in Excel and import it into Designer?  (Not yet, but supposedly this is coming).  Will the formulas and relationships all keep working the same way?  (See more below).  And what about the reverse – can I build a model in Designer and then “front it” with Excel visualizations?  Depending on how that plays out, #6 – we GET to choose – becomes instead “we HAVE to choose.”  That’s all the difference in the world.  Don’t screw us on this one, Microsoft, or you screw yourselves too.

You Still Listening, Microsoft?

I have two pleas for any Microsofties who are reading this…

1) The Excel Pro is STILL Your Biggest Growth Opportunity, By FAR

I’ll keep this brief and visually-oriented:

image

I’m NOT saying you should give up on people who can spell BI, nor is it necessary that you stop chasing the casual crowd.  But holy heck folks!  Let’s rank the following three funnels in terms of market opportunity:

image

Who Represents the Largest Addressable Market for Power BI, and Yes, Even for Designer Itself?
(This is not a trick question.  It’s not even difficult.  Let’s DO this.)

This leads me to…

2) Make the Excel/Designer Boundary as “Permeable” as Possible

image

This Needs to Be MORE than Just “We’re Not Going to Prevent It.”
It Needs to Be an Explicit Strategic Goal.  A Guiding Principle.
(Otherwise it will be lost in the dust of a million tiny product decisions.)

We need “open borders” between Excel and Designer.  Open borders that are based on those wonderfully-compatible engines, DAX and M.  Those borders are partially open today, but they need to be blown wide open, and kept open, forever.

Every conversation I’ve had with a member of the Microsoft Power BI team recently has gone like this:

ROB:  “Please please PLEASE don’t give up on Excel!”

MICROSOFT:  “We’re NOT giving up on Excel.  Have you heard anyone at Microsoft say that we are?”

ROB:  “Well, no, not exactly…”

MICROSOFT:  “So what are you worried about?  We’re still putting new Power BI features into Excel, we’re about to add Excel Services to Power BI Online…”

ROB:  “Yes, and thank you for all of that.  VERY good stuff!  But for some reason, I am still uneasy.”

Let me make my concern a bit crisper:  it’s not the PRESENCE of some statement that worries me.  It’s the ABSENCE of a different statement.

I’ve been to the software rodeo.  I know how it goes.  Projects just…  drift.  It’s the nature of entropy.  I think Bill Gates (or maybe it was a former Windows VP, I forget) once described Microsoft as such:  “We’re not really a software development company.  We’re a software testing company.”  Because that’s where most of the time and money were spent:  on finding and fixing problems.

Yes, the age of Agile development and unit testing has changed that to an extent, but still, a crucial fact remains:  if a scenario isn’t front of mind for everyone making those thousands of daily decisions, and if it isn’t in your test plans and automation suites, that scenario dies.  It just does.

But hey, good news!  It CAN be in your plans, front and center!  Free passage in either direction across the border, with no breakage, no unexpectedly different numerical results.  Simple!  Smile

(And hey, if this is ALREADY the plan, so much the better, with my apologies.)

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 37 Comments

  1. Excel Services for PowerBI.com will change EVERYTHING for me. This is the single missing piece of the puzzle. I can’t wait. I will go from a bemused spectator to an enthusiastic sponsor and user.

    1. I 100% agree Matt. Me too! It would be really interesting to see the usage statistics of the Power BI platform once this becomes available. Rob you can get your hands on this data can’t you?!

  2. I especially agree with your second #2 which starts with, “…we were just “gifted” another place where our professional investments pay off.”

    PowerBI.com finally gives us an easy way out of the dreaded “Excel stigma”. We can continue to use our Excel skills, but charge twice as much by putting “BI” instead of “Excel” on the invoice!!!

  3. At last someone finally express the concern of excel pros…this is gold…Microsoft, you should place really close attention to the ideas expressed here…as a Financial Analyst trying to promote Microsoft BI to a major Corporation, the more communication and transferability between Excel and designer, the easier will be for people like me to push this service…we are NOT against designer, but this new tool have to work closer with excel to have an oportunity to really be considered In our company…

  4. Tree maps and funnels are not enough to tempt me to touch Power View again. I tried multiple times to like it, but I don’t have time to beat my head against that wall any more. It is too limiting. I’m sticking with Excel. Most of our best reports are simple Excel charts off of cube formulas.

  5. I don’t know, tried PowerBI designer several times but seriously speaking – not a big fun of it yet. With Excel I feel more control in hand of data behind the scene. I don’t believe MS will overlook Excel.

  6. *****^2

    Isn’t this article living proof of what a difference good visualizations make?

    …provided that you:
    – Have story to tell
    – …that has an impact on your readers
    – Know what you’re talking about
    – Did you homework and checked you knowledge again before publishing
    – Invested some time in evaluating which way to tell would be best
    – Invested more time in putting it all together
    – Are passionate about it
    – & fuelled by the smile of your readers that your work will conjure 🙂

    Thank you so much for this invaluable article!

  7. Thanks for providing some clarity. Excel still being part of Power BI is a big deal although I’m slightly worried as it’s no longer THE environment where all the Power parts come together. In my opinion it would have been much better if the new designer visualization features were part of Excel as objects which you can add to a normal worksheet in stead of being in available in a seperate tool.

    Another concern is how the whole self service BI strategy seems to change in a short timespan; first it’s a free add-in to Excel, then it’s restricted to the pro plus version of Excel, now it’s split up between two tools… What’s next? It’s hard to promote this stuff when changes like this happen and there’s a lack of clarity about the future.

    Hopefully a new strategy will emerge with the Excel pro being the focus.

  8. Having not used Power BI Designer yet (it requires IE10 and that hasn’t been rolled out in our organisation still for various reasons) could someone clarify where people who can’t\won’t\never will allow their data outside the boundaries of their own network stand?

  9. Thanks Rob. Again you managed to write and visualize 🙂 a topic in a way that I can easily use in my demo’s trying to explain to my customers what the differences are between PowerBI Office 365 (v1) and PowerBi.Com (V2). Don’t worry, already mentioning your website as a source in my PPTX.

    I also read some blogs saying that V1 will is deprecated.. don’t /won’t really believe that.

    Anyhow: Also like to know how you think about the Query Catalog and DMG as missing features in PowerBI.Com.

    Last but not least..any news, as you know of ( still in contact with MS I read), about the integration of Reporting Services with PowerBI (V1 and/or V2)

    1. Oké., I beter start believing……The Power BI business analytics service (“Power BI 2.0”) will exit preview status, becoming a generally available service on July 24. This supersedes the Power BI for Office 365 service (“Power BI 1.0”), which we will continue to operate during a transition period as users migrate to the updated service

      Also just found out that Azure data catalog will integrate with powerbi..Nice…

      ..still not sure of the personal datagateway is the way to go..

  10. Thanks for a great summary!

    Excel is excellent as a build layer but not as a presentation layer.

    I miss two major things in Excel:
    1. a brilliant PowerPoint-like slides and a slide-language to decide what to place on which slide.
    2. a brilliant Xcelsius-like interactivity.

    Print-like defined slide-areas, not for printing but for ‘presentation’. Dynamic – table built – slides.

    Let’s imagine I select slide 1: Here I select Country: Europe and all of a sudden I have all 25 slides of the European countries, easily ready for printing/presentation. Simple. Like a table filter.
    + a language to add individual slide notes; Ex. “IF UK THEN ‘Due to the weather conditions’ “.
    I’d like to ‘build Slides’, the way I ‘build Tables’.

    So a ‘Power Presentation addin’ – like Power Query – to build slides.
    For me, Power BI and Power view is just not there yet.

  11. “The Engines are the same!” — Well, in as much as they are both DAX/Vertipaq/xVelocity… Yes.

    But I have no real reason to believe they will ever been the SAME version, ever again. And that is going to directly hinder your ability to “choose” between the platforms. I love that the Power BI folks are going to iterate rapidly and I’ll happily pay my money… for Excel to keep up.

  12. Hey there Rob, great article.

    I have found a way to get your Power Pivot Model (which could get it’s source data from Power Query also) in Excel to be exposed in Power BI.

    It is quite simple.

    All that you have to do is to create on Power View sheet within your Excel file, save your Excel file and then upload it into Power BI directly or via OneDrive.

    Once it opens and you go into creating your report, you will see the entire Power Pivot Model exposed!!

  13. Anyone who has used Tableau, Spotfire, Microstrategy, Qlik Sense, Birst, etc. knows why Microsoft created PowerBI designer *and* why they bought datazen.

    All the other vendors in this space:

    1) Have one dedicated tool for development, including all aspects of BI – data ingest, cleansing, transformation, integration, modeling, calculations, presentation, publication, security, administration, governance, etc.
    2) Have a compelling web and mobile story.
    3) Don’t make any distinction between “self-service” BI and “enterprise” BI. They just scale at all levels.

    Microsoft’s solutions are:

    1) PowerBI Designer
    2) DataZen + PowerBI.com
    3) Integrating all the PowerBI tool features into their Enterprise BI platform (Power Query in SSIS, Power Pivot in SSAS Tabular, etc.), then offering a push-button scale-up from a PowerBI designer model into Enterprise BI world, probably backed by Azure

    And the reasons Excel isn’t the solution to #1 should be obvious:

    it’s a legacy platform with legacy requirements;
    it’s not made for agile development, and BI development needs to be agile;
    it’s not web-friendly;
    it’s supposed to be a client tool, not a development tool.

    Rightly or wrongly when PowerBI designer reaches feature parity with Excel’s presentation layer (it’s biggest defect by far – hence the DataZen purchase) it will replace Excel (in Microsoft’s view) as the de facto self-service BI tool.

    Which is at should be, Excel shouldn’t be hosting BI development tools, any good software architect would tell you to build a proper standalone BI application and just have it integrate with Excel – which is exactly what PowerBI Designer is aiming for.

  14. Great article Rob … I would echo several others above … can you please offer your crystal ball look as to how Datazen may or may not help the Excel “presentation layer” in the near term?

  15. Prasannaprabhu: I believe the comment “64-bit Excel is hard to get” really means “inconvenient or unsanctioned by IT”. While it is an easy download + easy install via O365 subscription, you cannot mix 32 and 64-bit versions of Office. Some other apps such as Access may rely on third-party add-ins that are only available as 32-bit components. Therefore the need for 64-bits for reliable, robust Excel BI has a number of unwanted side-effects.

    I am completely satisfied with 64-bit Office myself, but I own the company so IT buy-in or assistance is never an issue

    1. Bingo Tom. That’s exactly the reason why my company doesn’t do 64 bit. An Office add-in that we use isn’t supported. And it’s all or nothing. You can’t do 64 bit Excel but 32 bit for the other Office apps. You have to do all 64 bit or all 32 bit.

  16. i just load it onto my computer and i am litle disappointed. That is quite rigid to use. I cannot put value on graphs, change title, load PP models actually used. I think i can do much more with PV on hand. What do you think?

  17. I wonder if Microsoft every figured out how many people got onto PowerPivot and the entire suite just because it was latched onto Excel. That in itself was a big crowd puller to the self-service BI environment that Microsoft was hedging against as it’s offering to compete with the likes of Tableau. But that being not available with the new designer model, I wonder if Microsoft can still attract the same amount of NEW users to it’s self-service BI environment?!

    The BIG QUESTION: Is Microsoft going to latch the PowerPivot add-in with Office 2016?

  18. From Power BI Designer, can we create the drill down reports/dashboards similar to the drill downs can be created from Excel 2013 pro using Adhoc hierarchies or data model hierarchies?

  19. Thanks Rob for trying to clear out the heavy mist around this issue. I know you’ve done your best to keep it clear but, still, I don’t feel like having a true decision about it. The way I see it, any decision I take with my customers solutions is a clear gamble on a success of a horse called ‘Microsoft Marketing Division’.

    1. Power BI Designer was bad. Power BI Desktop is the real deal. Microsoft totally re-architected the product to move from Silverlight to D3. This is a big deal. Microsoft completely abandoned its proprietary standard for an open source one between preview and GA. Let that sink in. How many times have you wished Microsoft would ditch Silverlight, and the Power BI team just delivered it. I just spent a week at the Microsoft Technology Center in Chicago using Power BI along with Azure Data Lake and Azure Data Catalog. Power BI Desktop is not a flash in the pan. It is a strategic piece of a full suite. Microsoft doesn’t want to be your personal data tool. It wants to be your enterprise big-data tool. It needs Power BI, not Excel to pull that off. There is a reason that they were doing weekly releases of Power BI Desktop last year. You may never see weekly or even monthly releases of Excel. This is a significant area of investment and focus for Microsoft. It is worth your time to get familiar with Power BI. I believe the integration with Azure Data Catalog and the Power BI Q+A function are both game changers you will not want to miss.

  20. I just got on site at a new consulting gig that is converting as I write to Office 365. Waiting for our Robotic Process Automation software to get here, it is a month late, I told the client I could help them build some cool analytics that find automation opportunities for the bots in the mean time. I built an automation opportunity finder using Sql Server and Power Pivot two years back. Unfortunately is was 32 bit version. It worked okay over the Net, via VPN until new security was implemented that kill that Tool and getting 64 bit took an Act of Congress in my former Enterprise. Anyways, this current client is a much small organization; no bandwidth problems anticipated. So somewhere over the past year, I got the idea that Power Pivot was part of the Power BI suite, which I associated with Office 365? I ordered both Power BI and Power Pivot just to be safe. Needless to say, went I saw the Power BI icon on my desk top today, I wondered what it was? Navigating around in it, I recognized most of the features as those of Power Pivot. I can tell you, after watching a few training videos today, and am really happy about the mistake: better graphic, some intelligence, far greater data source selections, and you can publish it! An automation opportunity finding tool in Power BI should score some big points with my new client!

Leave a Comment or Question