Introducing Power Update!

Post by Rob Collie

***Update:  check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2:  a Free Version of Power Update is now available.  More info here.

***Update #3:  There is now a forum for Power Update questions, located here.

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint or Otherwise)

A brand-new software utility designed from the ground up as
a “Companion” to  Power Pivot, Power Query, and the entire Power BI stack.

Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Common Problems with Power Pivot and Power BI Scheduled Refresh

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

OK, a few things:

Read the Rest

Insight Center: Azure-Based SharePoint / Power Pivot Server (Joint Solution with Microsoft)

Post by Rob Collie

Everyone Needs a Power Pivot Server.  The Flowchart Just Got an Exciting New Option.

Your Server Options:  Power BI Online, On Premises / Do It Yourself,
Third Party Cloud, and Now…  Insight Center!

Our Continuing Mission:  A Power Pivot Server in Every, Um, Driveway

Everyone, sing it with me:  “I’ve been wor-king on The Flow-chart, aalll the live-long daaay…”  Yes, The Flowchart.  The “yellow brick road” that helps lead your org to the Power Pivot server option best-suited for you.  (Why do you need a server?  Because it’s YouTube for Workbooks.) 

Drawing the flowchart is, of course, the easy part.  Making sure that it “ends” in a variety of dependable options – that fit varying budgets, infrastructures, and org sizes – well, THAT is the reason why the flowchart is taking this much time.

But said flowchart is getting closer – MUCH closer – to being ready.  We have been busy little beavers here at PowerPivotPro.  Crusaders for justice, as we oh-so-modestly think of ourselves, rarely get to rest.  The shortage of Power Pivot servers in the world is a humanitarian issue in our eyes.  (Yes, this paragraph was tongue-in-cheek.  Well, partly anyway.)

Until now, you’ve basically had three classes of option:  1) Install and Run Your Own Servers, 2) Subscribe to Power BI Online, or 3) Lease Space From Third Party (non-Microsoft) Cloud Providers.

All three of those options are good, viable choices, and remain so.  The Flowchart will soon help you choose between those, depending on Best Fit.  But recently it’s ALSO become clear that some organizations would benefit greatly from a Fourth Option.

And with that realization…  Insight Center was born.

Already Gaining Traction with Microsoft’s Enterprise Customers

Read the Rest

5 Interactive Chart Techniques Come Together

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from, from searching the Web, as well as some of my own ideas.  While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated.  I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user.  There is a link to this workbook at the end of this post.

The techniques I have used are:

  1. Disconnected slicers used to create interactive chart series
  2. Cube formulae and standard Excel to make an interactive chart title

I love these 2 tips I learnt from Rob – so user friendly.  However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. Excel VBA to change which Axis the series appears on.

I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below.  I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.

Now let me call out the key techniques I have used to make this workbook rock.

Read the Rest

Leverage SharePoint for Rich User Interaction

Guest Post by Scott at Tiny Lizard

Hold onto your hats, my friends.  We have some pretty advanced stuff for you today!

If you host your workbooks on SharePoint, you are about to read some powerful techniques, and hopefully give you some “brain-fodder” for related ideas.  Even if you aren’t using SharePoint today… it’s worth reading to see the types of things possible with SharePoint, then you can refer back when SharePoint enters your life.

I am going to show two techniques to allow end-users to have some level of interaction outside the bounds of the workbook… say, to drive data into the underlying data sources.  The first technique is not nearly as fancy as the second…Smile

Read the Rest

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

By Avichal Singh

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

Monday Bonus: Last Week’s Big Data NYC Slides

 Last week’s post proved to be very popular, and I received many requests for the slides. Well the slides didn’t really capture everything – so much was covered purely in the demos or my talking points. So I just invested an hour or so in capturing some of that extra stuff into the slides themselves.  Still not perfect of course, but…  closer. View and download the slides here.

Read the Rest

Big Data is Just Data, Why Excel “Sucks”, and 1,000 Miles of Data


***UPDATE:  Slides uploaded here.


One of My Slides From Last Night – Equally Relevant to Excel, BI, DB, and Big Data Pros

Had a great time last night at the NYC MSBIgData group.  I’ve never spoken to a group quite like last night’s, but I struggle to explain how they were different.  It’s easier to explain what they were not.  Even though the user group is a Big Data and BI user group, they were not a Hive/Hadoop crowd, which shouldn’t have surprised me – there aren’t enough Hive/Hadoop people in the world to really have crowds of them laying around, at least not yet.

But there also wasn’t critical mass of seemingly any other discipline – not BI, not Excel, not DBA, not SharePoint, not programmer.  There were some people from each of those backgrounds but no more than 10% of each.

I think my best assessment is that they were simply a group of people who DO things.  A very pragmatic collection of flexible people.  People who happily use different tools to solve different problems.  I find that fascinating all on its own.

(If you were at last night’s talk, please replace every instance of the word “they” above with the word “you.” Smile)

Big Data is Just Data, and Hadoop is Just a Way to Store Lots of It

Read the Rest

Early Tuesday Post: The “Hero Report”



A Post That Just Refused to Wait for Tuesday

I’m Looking for a Few Good Pivot Pros

If you’re an Excel Pro (which I define simply as “one who creates PivotTables”), and you’ve been using PowerPivot, I want your help for a semi-radical side project I’ve been thinking about.

I want to ask you a few questions, either in email or on the phone.  That’s it – basically I need a focus group off of which I can bounce a few ideas before making those ideas public.

If you’re selected to participate, there WILL be compensation.  That will either be a $50 gift card, some free Pivotstream services, and/or a direct line to me for some PowerPivot questions.

So if you’re interested, please drop me a note at the following address:

***UPDATE – the survey program is closed to new participants at this time, the response was overwhelming!

What’s a Hero Report?

I love this term, but I didn’t invent it.  Credit goes to John, one of my colleagues at Pivotstream.  He talks to a lot of Excel Pros every day, even more than I do, and he tells stories like the following all the time:

Read the Rest