Introducing Power Update!

February 3, 2015

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 of this entry »

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

October 28, 2014

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 of this entry »

5 Interactive Chart Techniques Come Together

October 2, 2014

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 of this entry »

Leverage SharePoint for Rich User Interaction

September 25, 2014

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 of this entry »

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

December 12, 2013

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 of this entry »

Monday Bonus: Last Week’s Big Data NYC Slides

October 15, 2012

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.

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

October 9, 2012


***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 of this entry »

Early Tuesday Post: The “Hero Report”

August 6, 2012



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 of this entry »

Mini-Post 1 of 2: Sharing Creative-Font Workbooks

July 12, 2012

In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

Question is:  what happens when you send the resulting workbook to someone else, or publish it to SharePoint?

Here are the quick results of my investigation so far.

Read the rest of this entry »

“Drill Across” in PowerPivot – Live Demo

May 24, 2012

Hyperlinks in a Pivot

“I’m telling you there are monkey-fighting hyperlinks in this Monday-to-Friday pivot!”

(Seriously this is how they cleaned up his line for TV, with “monkey-fighting” and “Monday to Friday”)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.





Retailer Competitive Overlap Application – New and Improved Live Demo

PowerPivot Retailer Competitive Overlap Application With Drill Across

Revamped/Simplified “Retailer Competitive Overlap” Application
(Note that the Row Labels Area of the Pivot Contains Hyperlinks!)

Clicking an Item to Get More Detail

The retailer overlap application is one that I’ve covered before, in my post announcing our live PowerPivot demo site, but I’ve recently spent some time improving it based on customer feedback and requests.

Specifically, our retail customers have asked the following:  “It’s great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?”



But WHICH Stores?  I Want to See the Addresses!

Hyperlinks in a Pivot!?

Let’s zoom in on the row area of the pivot pictured above:


Read the rest of this entry »

Cloud PowerPivot: Free Trials for the Public

April 26, 2012



Actual Web Browser Screenshots of PowerPivot Sites:
Example of Homepage (top) and Report Page (bottom)

***UPDATE:  I am no longer working at Pivotstream and do not endorse their services.  All links are removed from this article but feel free to look them up if you are interested.

Want to see (and share) your workbooks on the web?

For a few months now we’ve been running free 30-day free trials where you can:

  1. Upload workbooks to the Pivotstream cloud
  2. Interact with them in the browser
  3. Securely share them with colleagues, even if they don’t have PowerPivot or Excel 2010 installed

Read the rest of this entry »

My Experiences with Hosted PowerPivot, Part One

February 21, 2012

Guest post by David Churchward

Pivotstream Dashboard Application

One of Azzurri Communications Ltd’s PowerPivot
Applications Running in the Browser

Six Months Ago:  The “Lightbulb” Moment

Almost exactly six months ago, after being a long time reader of this blog, I emailed Rob and asked him a question regarding something that I just couldn’t get my head around in DAX – Banding!  He kindly responded, and his answer solved my problem, so I asked him if I could return the favour somehow.  He asked if I’d mind writing it up as a guest post, which I did.

Now, double-digit guest posts later, I’m amazed at how far I’ve come in short order.  Something definitely “clicked” for me that day, and my grasp of PowerPivot’s capabilities expanded rapidly.  It felt like that moment that I imagine Pianists reach where they can suddenly play by ear, because whilst I could conquer most things in DAX, it didn’t seem to quite “flow” – until that day!

Up until that point I had viewed PowerPivot as a “private” tool – something that was useful for me in my work, a supplement to other tools and methods.  But starting six months ago, I started to understand that PowerPivot could, and SHOULD, be used to improve or replace most of our existing Business Intelligence and Analytics tools.

Step One:  Azzurri Deploys its First “On Premise” SharePoint Server

At Azzurri, I am fortunate to enjoy two critical flavours of support:

  1. Our executive team is open-minded to progressive ways of doing things (so long as there is a solid value proposition).
  2. My tech team is a crack outfit who will bring me the moon if I ask for it, but tend to make reasonable alternative suggestions such as building data warehouses.

In other words, Azzurri is the perfect sort of place to deploy PowerPivot for SharePoint, bordering on a textbook example.  There aren’t many companies of Azzurri’s size where I could explain the benefits of a PowerPivot server, win people over, and have a server deployed two weeks later.  But that is precisely what happened Smile

Step Two:  Start Emailing Rob Again Smile

We didn’t just deploy the server, we immediately began USING it for serious work.  And that led to questions – questions about performance.  Questions about hardware.  Questions about customisation.  Questions about refresh.  Questions about “core and thin” workbooks.

Rob and I had a friendly correspondence going at that point, so I started sending those questions over.  I even looped him into email chains with our tech team, and we talked through a number of issues and optimisations.

Step Three:  Try Out Hosted PowerPivot in Parallel

Everything I do in Systems Development, especially with my Finance background, is about Cost V Benefit, ROI, IRR and payback.  With this in mind, I started wondering whether it made sense for us to develop PowerPivot for SharePoint expertise of our own.

We had originally decided to go with our own SharePoint deployment because we had the required licences and a particularly clever team who I had every faith could deliver.  This seemed obvious as SharePoint was already in operation at Azzurri.  My initial view was that it must be relatively straight forward to bring all of the BI tools into the equation.

Two weeks into the process, however, I was already seeing that things might not be as straight forward as I had first hoped.  Performance was the first major barrier that I hit and I couldn’t be entirely sure what kind of investment in hardware might be required to alleviate this.  Out of nowhere, PowerPivot gallery started playing games which turned out to be an IE9 issue and then I was introduced to Kerberos which, it turns out, isn’t a breakfast cereal that I was yet to encounter!

I knew about the Pivotstream Hosted Solution of course, and I still wasn’t ready to commit to hosting, but I decided that running a trial in parallel made a lot of sense, especially since I was particularly aware that my tech team needed to be doing other things.

I’m very glad that we decided on a trial, because step four was to switch over full-time.

Goodbye “Do it Yourself”, Hello SaaS

The journey I’ve been on as a customer of Pivotstream has validated for me that the SaaS model together with the capability of PowerPivot makes for a more compelling business solution for reporting and BI than any alternative that I can find.

I’ve been particularly conscious of making sure that my tech team spend their time where they can really drive business value – building Data Warehouses, ETL and efficient business processes.  It was clear that time spent developing SharePoint Server was time not spent adding value elsewhere.  There’s no doubt in my mind that they would have delivered, but I knew that they could deliver more value elsewhere to more than offset any cost of hosting.

Summary of Our Experience

Once I had taken the decision to try out Pivotstream’s hosted solution, it became clear that “elapsed time” taken was no longer going to be a constraint to the project.  On that same day, Azzurri had it’s own Pivotstream site in full working order with admin and consumer accounts setup for the trial.  It was now down to me to start making this a fully functional dashboard.

Naturally, I had workbooks at the ready and I loaded a few up immediately.  I started sniffing some of the additional features that I could now start playing with.  Before I knew it, I was canvassing Pivotstream for direction on Query String URL filtering (an awesome attribute to drill across to other dashboards).  A handy guidance document found it’s way into my inbox and I was away.

I was supplied with a program to split core and thin workbooks, another gem that just saves time and aggravation.  I served up a query with web part layouts and, next day, I get a new page layout deployed straight to our site.

Immediately, the focus of what I was delivering was about end user usability as opposed to finding ways around potential (and in some cases very evident) performance issues.  Performance was immediately apparent on the Pivotstream solution, as could be immediately seen by some of my more “chunky” analysis that didn’t even make it flinch.  My in-house SharePoint Server could take upwards of a minute to open these workbooks whereas the hosted solution barely registers seconds.

Within a matter of days, I realised that the limits of this solution only existed to the extent of the limits of my imagination in creating dashboards.  All of a sudden, my focus was turned on making sure that full value was derived and, to that end, I started spreading the word within Azzurri.  Some initial training took place and I immediately recognised that these clever individuals that I was working with had even more insight bursting to get out and the fact that we were playing in Excel meant that they could immediately relate to what they were being shown.  I had hit that fantastic point in the project where momentum starts taking over and this is probably less than two weeks after starting the trial.

Speed (both of implementation and application), elimination of complexity and additional value adding applications delivered in a scalable data-centre model with an OPEX cost model sums it up for me.  Now, it’s just about making the dashboards deliver the real benefit to the business – insight!

As I’ve been writing this, Rob’s reminded me of a comment I made back towards when this whole thing started:

“My key driver is laziness so I’m always looking for quicker and better ways to do things. In doing so, I find myself working non-stop so I may have my driver wrong or I’m failing miserably!”

The reality is that Hosted PowerPivot does do it quicker and better.  I’m working non-stop because the results speak for themselves and I fundamentally “get it”.  The reality is that my driver was wrong!