Guest post by David Churchward
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:
- Our executive team is open-minded to progressive ways of doing things (so long as there is a solid value proposition).
- 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 
Step Two: Start Emailing Rob Again
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 Solutionof 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!
Get in touch with a P3 team member