THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Thoughts on Power BI for Office 365

Three months ago I published a fairly scathing attack on what I saw as some lacklustre announcements at Microsoft’s BI conference. In that blog post, Geoflow? Is that it, Microsoft? I opined that their announcement of Geoflow wasn’t particularly earth-shattering and what they really should have been talking about was a mobile BI story. Apparently I wasn’t the only one who thought that too judging by the majority of the 52 comments that that post received. Here are a few few choice quotes:

Agree wholeheartedly JT.  Given Sharepoint is the BI collaboration tool I would expect Geoflow to be integrated. I am also not a fan of separate tools for everything.  PV + Geoflow would be good/Better. – Allan Mitchell

Given the hype, I'd have expected a lot more from MS on spatial visualization. Between pie charts on maps in PowerView, no HTML5 support for PowerView, and the whole "PowerView can't be used with the other SharePoint components, it only really works fullscreen and can't accept parameters" story, I really think MS has lost their way on this. – Mark Stacey

Two weeks after the Data Explorer release, I did a demo at a SSUG with "teaser trailers" of GeoFlow and machine learning. I mentioned #passbac and boldly voiced that I expected mobile to be announced because I was so sure that PASS could not launch a new conference without an announcement of this scale and impact ... GeoFlow does not measure up. It's a "cool" product, but doesn't help the MS platform anywhere close to the gaping hole called mobile. – Toufiq Abrahams

In the time it's taken Microsoft to release nothing useful that's mobile BI related (Geoflow looks cool but its practical use is within the BI stack is virtually nil) and at the same time manage to piss off the Excel community by effectively restricting the adoption of PowerPivot/Power View, most of the competition have not only released BYOD-friendly mobile BI apps but the most forward thinking have completely re-written their offerings in HTML5 (QlikView .Next for example). So still we wait, and at every pre-sales meeting where the "Can our execs use MSFT BI on their iPads?" question comes up (80% of the time now), I have to shift uncomfortably in my seat and say "No, and what's more, even as a MSFT partner, we have no clue when we will be able to say Yes!" Shambolic. – Will Riley

And so having spent 2 years pushing the SharePoint message, despite huge reservations from the partner community, here we have a release of a front end tool that isn't supported through SharePoint. You want to put the fun back into BI? It might be fun producing flashy but ultimately pointless demos for serial conference attendees but it certainly isn't fun trying to position a Microsoft BI strategy to enterprise customers. And how many different but overlapping tools do we need? I now need two monitors side by side to view my Excel ribbon. CONSOLIDATION please. – Mick Horne

Another Partner here agreeing that it does not help us sell a coherent enterprise BI strategy.  This is what we need to see from MS far more than yet another tool. – Calvin Ferns

You get the idea!

Well, better news cometh. Three months after that post and Microsoft have finally announced something that is intended to alleviate many of the concerns expressed above – its called Power BI for Office 365 and it was announced at Microsoft’s Worldwide Partner Conference. Power BI for Office 365 brings together the existing disparate tools PowerPivot (now renamed Power Pivot), Power View, Data Explorer (now renamed Power Query) and Geoflow (now renamed Power Map) into a single offering, incorporates an online collaboration portal and also (finally) mobile apps for iPad and Windows 8/RT (no word on Android or Windows Phone yet).

As a BI/data integration practitioner in the Microsoft space I’m excited about this, I really am. I’ve had some illuminating Twitter conversations in recent days with people that clearly have a different opinion, and that’s OK. By the end of this post I hope I’ve explained exactly why I’m excited about Power BI.

Branding and focus

The first important take away is that they’re coalescing under a single moniker, a single brand – “Power”. Power BI, Power Pivot, Power Query, Power View, Power Map (not Power Maps as some commentators would have you believe). Whether or not you like the word and its connotations its great to see consistency in the naming. Also, as confirmed by Kasper de Jonge, there is now a space between all the words (i.e. Power Pivot not Powerpivot) which I think is also a good thing – consistency is important.

Above all I sense some focus around a coherent offering here whereas beforehand Microsoft’s BI offering seemed rather rudderless and haphazard – that should be good news to those Microsoft partners  quoted above that lament the current state of affairs. Will Power BI for Office 365 have frustrating limitations and bugs? Sure it will, but I have optimism that it has sufficient priority that those things will be addressed and not left to fester.


Most of the articles I’ve seen relating to Power BI up to now seem focused on the four aforementioned Excel add-ins but, to me, the most interesting aspect of this suite is the BI portal portion. Frequent readers of my blog may have noticed a theme running through a lot of my posts down the years, that of sharing pointers to data as opposed to a copy (I hate getting email attachments for example, and I always espouse subscribing to calendars rather than importing them). The BI portal portion of Power BI for Office 365 speaks to this theme by hosting queries that are built in and published from Power Query so that they can be consumed by other people. The obvious point here is that by sharing a query one is not sharing a copy of the data, but actually a pointer to the data and that is very exciting to me. Moreover its only the data that is being pointed at, presentation and further manipulation thereof is left as an exercise for the consumer and again, that’s something I really like. This is not a panacea to the proliferation of data silos (mostly in Excel and Access) that are littered throughout organisations but I am hopeful that its a step in the right direction.

I am also hopeful that the BI Portal becomes a focal point for related offerings. For example, I would love to see SQL Server Master Data Services (MDS) offered through the BI Portal and here’s an anecdote that explains why.

Only yesterday on my current project we were discussing a data hierarchy that is an important dimension of the organisation – stock status (my client is an online retailer so stock status is rather crucial). It transpired that the hierarchy that the business used to classify stock status was not actually stored anywhere other than in peoples’ heads and hence if we were to provide data against that stock status hierarchy we would need to store the hierarchy somewhere. This is a SQL Server shop so the idea of using MDS arose but was quickly discounted as the provisioning of new infrastructure was considered too big an undertaking to hold what amounts to a lookup table (let’s be honest, whatever you dress MDS up as its still basically a bunch of lookup tables).

This is a perfect scenario for MDS and I am hopeful that Microsoft are considering offering MDS as a hosted service because if it were it would have been a no-brainer for us to use – we would not have to provision hardware and do installations, all that would be required would be to walk up with a credit card. MDS as a software-as-a-service (SaaS) offering through the Office 365 BI Portal would be, in my opinion, compelling (my current client is an Office 365 customer by the way). The same applies to Data Quality Services (DQS) as well, that’s another service that would benefit from being offered on a SaaS basis.

What else might benefit from being offered through a single BI Portal? Well if we look outside the four walls of Microsoft there are plenty of companies that might welcome the opportunity to provide their wares through Office 365 – Tableau, Microstrategy, Business Objects (SAP) and Predixion are some that spring to mind. This is rather future-thinking I admit but I do think there’s real potential here.

I’ve discussed Power BI for Office 365 with some folks who opine that this Power BI announcement is little more than a renaming of existing Excel add-ins but in my opinion the provision of a BI Portal for collaboration is the big story here.

Data Governance

Another big take away (which has not been apparent from some of the early demos but which I have gleaned from early exposure to Power BI) is that data stewardship is a big piece of this puzzle. Data stewards will be relied upon to:

  • grant/revoke access to data sources, reports and queries
  • curate metadata/annotations on the Power Pivot model and Power Query queries in order to drive the Natural Query capabilities and surfacing in Power Query search results
  • Monitor usage via the Data Steward Portal and thus allow stale and/or unused data to be expired
  • Manage data refreshes (including installation of the Power BI on-premises agent that provides refreshes from on-premises data sources)
  • Evangelising the existence and use of data available through Power BI

amongst other things.

It is clear to me that in sufficiently large organisations this "data steward" role is going to be a full-time role and, again in my opinion, Power BI implementations could live or die by the diligence of the nominated data steward(s). I view this as a good thing – I’ve seen countless organisations where there is a chronic lack of governance applied to data and consequently data quality suffers as a result – anything that brings the issue of data governance to the fore is a boon. I envisage that in a few years organisations will have a dedicated "Data stewardship" function; data stewardship will become a discipline all of its own and there will be an ecosystem that grows up around this (tooling, forums, training courses, conference tracks etc…). As I alluded, I think this is a good thing – organisations that properly govern their data will be leaders in the digital economy. Its also a great opportunity for people like myself that provide consultancy services – helping organisations to make better use of their data is where I want to add value.

Microsoft will not of course talk about this explicit need for a Data Steward as it detracts from the self-service mantra that they are espousing – at the end of the day Microsoft want to sell licenses, and I’m fine with that. The community can fill the void here with appropriate messaging and, again, I see this as an opportunity. Power BI itself is not going to solve the data governance problems that proliferate in organisations today (only human beings can do that) but I harbour hopes that it can raise awareness of the issue and enable change.

Office 365

Ah, the Office 365 conundrum. If you’re not using Office 365 then there’s no Power BI for you. Yet. Personally this doesn’t bother me, my most recent two clients are both Office 365 customers (as am I) and the notion of SaaS (which Office 365 quite clearly is) resonates with me a lot better than on-premise notions of procuring software/hardware then having to install/configure it all. Here’s my money, now get out of the goddamn way – I much prefer that way of working. Evidently a lot of folks disagree and would rather run their own commodity services like email & IM. I can understand that and if people feel the need to do so, good luck to ‘em – like it or not though its clear that Microsoft are pushing hard on Office 365 and I don’t imagine that changing any time soon.

HTML5 Power View

There was a rumour flying around on the day of the Power BI announcement that Power View had been rewritten to use HTML5 instead of Silverlight. Well, that’s partially true, HTML5 variants of HTML5 are coming but until you hear any different Power View in SharePoint and in Excel is still going to be using SilverLight. I’m looking forward to the day when Power View runs everywhere without SilverLight as this presents a great opportunity for Power View to proliferate. I recently tweeted:

PowerView needs to be ubiquitous. Wherever there's data(SP, PerfMon, SSMS, Dynamics, Eventvwr, etc) should be a "view in PowerView" button

I really hope this happens. Power View is a great ad-hoc visualisation tool – I hope it spreads like a weed throughout Microsoft products. Moreover I hope it spreads through the web too, Microsoft already provide the Excel interactive button that can turn any HTML table into an Excel workbook, how about a similar button that turns any HTML table into a Power View report? (you can have that idea for free, Microsoft. You’re welcome! Smile )

I have quibbles as well

Not all that’s been announced for Power BI for Office 365 is quite as I would like it to be. I spoke in glowing terms above about the ability to share Power Query queries via the BI Portal, the downside to this is that the consumer also needs to have Power Query (and Excel) installed – Power Query is both the query builder and the query viewers. In the tech preview of Data Explorer that was made available two and a half years ago that wasn’t the case, queries could be shared and were then available as an OData feed which meant that anything that could consume OData (which, these days, is rather a lot) could make use of it. Indeed I built my own query that made Google Reader subscriber counts available as an OData feed. I am passionate about making data available in a manner that doesn’t require the consumer to be using any particular software so I harbour high hopes that this feature returns to Power BI for Office 365 in the future and Power Query can live in the feeds ecosystem that I believe is coming.

I’m still not happy that Power Map is a separate thing – I am still of the opinion that it should simply be built into Power View.

There is currently no developer story, Power BI for Office 365 isn’t extensible. That would change if queries were exposed as OData.


That’s my take on what has been announced for Power BI for Office 365. I see this as a more focused approach to BI from Microsoft after years of shooting in the dark (anyone remember ProClarity??). I’m looking forward to exploring this when it becomes available next month.


Published Friday, July 12, 2013 6:36 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Frank Szendzielarz said:

I agree. As a systems developer/architect, I begin to see Office as the platform, rather than '.net' per se. It begins to make less and less sense building bespoke apps from scratch in C# if the business can be automated within Office entirely.

There are wider reaching implications. What about Dynamics? Is there any case for a customisable CRM system, for example, if Outlook is  extensible with html and cloud-connected? What about grid and form based intranet apps, standard business systems? Is there any case for all those 3rd party grid controls and Office look-a-like components any more?

Office could be a huge opportunity for anyone wishing to just give new businesses the functionality they need without having to leave their Office 365 SME platform.

Ironically, one or two organisations I have dealt with seem to think that working within Office entirely is something of a return to how they used to do things anyway, before they were sold [a] 3rd party system[s], and before the 3rd party systems disappointed them.....

July 12, 2013 4:30 PM

SSIS Junkie said:

LinkedIn profiles are always a useful place to find out what's really going on in Microsoft. Today I

July 19, 2013 6:44 AM

Scott MacDonald said:

I have gotten nothing but runaround from MS trying to figure out what version(s) of Office 365 include both Power View and DQS. As I'm studying for SQL Server Certification - I need both to practice with.

I see Power BI is coming out later this Summer - unfortunately I need these Add Ins *now*. I too think the SaaS nature (and surprisingly, the pricing) is the way to go.

Can you confirm that I need Office 365 ProPlus or Office 365 Small Business (they seem somewhat the same)?

Thank you.

Scott MacDonald

Tucson, AZ

July 21, 2013 10:36 PM

chiburi said:

It's annoying there is no way to skill up without shelling out.

BI and data discovery on the presentation and data exploration seems to have been a complete blunder since MS bought ProClarity. That clearly should've always been an office product. The fact they canned it was odd since it didn't seem to be replaced with anything, nothing that was left could do what ProClarity did. It left a massive gap in the market that Qlikview and tableau stepped into. tableau is awesome when it comes to data discovery and doing some basic on the fly stuff that should be easy but are impossible in the Microsoft space (without processing an entire dimension). Excel was always the right place for business users to handle data though SSAS browsing in excel is poor and restrictive compared to ProClarity.

I hope now they can retain their focus on maturing these products before getting distracted and going off another tangent. Customers just want a cohesive BI platform that integrates well, performs well and does some basic functions that you would expect it do.

February 12, 2014 8:36 AM

Jamie Thomson said:

This week a SharePoint conference took place somewhere and I took more than a passing interest because

March 7, 2014 4:20 AM

Leave a Comment


This Blog


Privacy Statement