PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MAY 15 - 16

Foundations: Power Pivot & Power BI

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
PowerPivotPro Logo

 
A little bit of food for thought today, delivered in a pictorial/humorous style but with a serious point.

imageA Running Joke That Never Gets Old

I’m quite fond of the following one-liner that I’ve been using in presentations for awhile now:

“Export to Excel is the third most common button in data and business intelligence apps…  after OK and Cancel.”

That generally brings laughs.  It’s ironic of course, since so many business intelligence suites are marketed in part on the principle of “stamping out spreadsheets.”  But even the proudest BI suite succumbs to that ever-present force:  customer demands.

And so they find themselves, over and over, opening a door to the “dark side.”  Let’s take a tour shall we?

Crystal Reports

image

And I really like this marketing point here, about how version 9 added richer support:

image

Business Objects

image

Cognos

Cognos doesn’t mess around.  Why settle for simple export when you can write an entire addin to push data into Excel?

image

Et tu, Qlikview?

(There is no extra charge for rhymes of such quality)

image

I like that they use the XL icon.

PivotLink

I’m including them because a few members of our team came from PivotLink, and because Pivotstream was using PivotLink until, um, yeah…  until I showed up.

image

One-upping QlikView, they not only use the XL icon, they give it top billing for the entire export dropdown.

Microsoft

Hey, in fairness, Microsoft products do it, too.  Here’s Reporting Services:

image

And PerformancePoint:

image

Even Excel Services itself offers “Download Snapshot” which is really just Export to Excel…  from Excel!

image

Why So Prevalent?

Why does everything have this button?  Why is export to Excel such a “must have,” even for tools that claim to “break” an organization’s dependence on Excel?

  1. Excel is widely known.  Everyone knows it, or at least when compared to any other BI tool.  But everyone knowing it isn’t quite enough to explain why it is so important to take data out of other tools and land it in Excel.  It has to provide capabilities lacking in the other tools, which brings us to reason 2…
  2. Excel is more flexible than any of these tools.  Excel isn’t really a tool so much as it is a cleverly disguised programming language.  Its capabilities are insanely broad, and those capabilities are all “composeable” with one another – it is not a collection of distinct features, but of building blocks that can be combined in myriad ways.
  3. Excel is results-oriented and agile.  Even when the other tools CAN do something, and the user DOES know how to achieve that, they still often export to Excel because it’s just a lot faster to get the result they want.

My Conclusions

First, this reinforces for me is that Excel remains the common denominator in business analysis and reporting.  If that sounds controversial, try building and selling a BI or reporting tool that lacks export to Excel, and let me know how that goes.  The companies above all produce good products, and they are smart companies.  They implicitly acknowledge what I am saying  (with their feature decisions) without explicitly endorsing it (there’s no upside in them doing so, for sure).

Export to Excel also implies an undeniable “drift” away from “one version of the truth.”  When I export from a BI tool to Excel, and perform some calculations “in the margin” of the exported data, that is a forked, one-time analysis.  It is not captured in any public data model.  It lives on my desktop.  It represents a “drift” away from the rightly-coveted single version of the truth.

PowerPivot, of course, is not immune to that problem of drift.  As I showed above, even server-based Excel has the ability to export snapshots.

But there is an opportunity here, with PowerPivot, that the other tools will always lack.  With PowerPivot, an analyst with proper permissions can build their “in the margin” calcs directly into the PowerPivot workbook itself.  All the flexibility and familiarity of Excel can be harnessed, but then shared and maintained centrally on the server.

Just as with the other tools, it is undeniably better to build that incremental business logic into the PowerPivot model itself (in the form of new measures, tables, etc.).  But sometimes that just isn’t practical.  Often that is true, in fact.  If it wasn’t true, these other BI tools wouldn’t have the export button!

And once we acknowledge that truth, that “last mile” calculations and similar extensions are common and inevitable, a tool that embrace them directly holds an advantage.  I find that pleasantly and rebelliously ironic, that an Excel-based BI tool can hold an intrinsic advantage in “one version of the truth,” which a few years from now we will refer to as “a former weakness of Excel-driven BI.”

  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 8 Comments

  1. Corporate IT BI gurus will read this and use doublethink to say that high-level Excel skills are not needed as part of the process.

    1. David, that may be true. I kinda doubt it will be as common a reaction as it would have been 5 years ago though. I’ve been seeing a very distinct change in attitudes toward Excel, especially in the last 2 years.

      The most devout Excel haters, of course, don’t read this blog, unless they are merely researching the enemy 🙂

      1. The phrase “self-service” is unfortunately construed as “amatuer work” by most IT, meaning that they see no role for themselves outside of SharePoint.

  2. Rob…great article, you missed out the ERP’s – SAP, ORACLE , BAAN etc etc – also have the “evil” E2E (Export 2 Excel) 🙂

    Moral of the the story.
    MS after all these years has still not got it…They just have 2 1/2 “real” products
    a) OS : Windows
    b) Database + Analysis Tool + BI : Excel + Addins + DB (Access/SQL Server)
    c) File Storage : Outlook

    so they should just stop wasting resources on Word, PPT, Publisher, OneNote, Infopath, IE, etc …etc and concentrate on their core competence.

    1. Sam, thank you. Your second comment is timely – just yesterday I was talking to a former Microsoft executive who reflected that “Excel may even be the best product Microsoft ever made.”

      And your first comment gives me an idea. Can you get screenshots of the ERP’s? Let’s do a post of reader submissions of everyone’s “favorite” export to Excel buttons. I think that would be a blast. Would you be “in” for that?

  3. Yep. Screenshots of EXCEL EVERYWHERE!
    I have an “unofficial” shot you might like. I just don’t know how to upload it to you.

  4. I find it extremely frustrating that one of Microsoft’s own flagship tools “Power View” does not have ‘Export to Excel’ or ‘Export to Table’ button. After I am done showing a flashy new Power View report to any of my customers, the first line is always – “Great, how can I get that in Excel?” And I have no response to that. As stated by Rob Collie “The world runs on Excel” and I believe that is true. There is a good reason all of the BI tools have the ‘Export to Excel’ button. About time Microsoft built it for their own tool – Power View.
    In our scenario we are using Power View reports on On-Premise SharePoint connected to SSAS Tabular Model. Here an ‘Export to Excel’ button is a near necessity for us. But even for Power View sheets embedded in an Excel sheet, an ‘Export to Table’ button would be useful for many users.

Leave a Comment or Question