Powerful visual Query Editor to prepare data for analysis
Drag-and-drop visualisation tools including geographic mapping
Quick Insights tool
Ready-made Content Packs for popular online services
Feature split between desktop and cloud implementations can be confusing
Power BI Embedded service still in preview
Basic tools are free, with a Pro License ($9.99 per user/month) for more advanced capabilities
When it comes to business intelligence and data visualisation, Excel remains the most widely used tool around. That popularity, however, has more to do with the ubiquity of Microsoft Office than the suitability of Excel for this role, with a slew of far more capable and easy to use alternatives now available from the likes of Tableau, TIBCO, Qlik and others. Microsoft’s initial reaction to the rise of these so-called ‘modern’ BI solutions was to release specialist modelling and visualisation tools of its own, initially as add-ins to Excel. These, however, have since been set free in the guise of Power BI, which, although late to the BI table, is making waves in this crowded market.
Good to go
Microsoft has made clear its intention to put all of its resources behind Power BI with the aim of making it a flagship product that’s as ubiquitous as Excel. To this end it has made it very easy to acquire, starting with a free-to-download Windows-based Power BI Desktop application and a similarly free Power BI service hosted and secured by the Azure cloud platform. Both components are required for a full implementation, with some tools only available on the desktop and others accessible via the Power BI service. This is understandable when you get to grips with the product, but a little confusing to begin with.
A data capacity limit of 1GB per user applies to the free versions, together with performance restrictions when streaming and refreshing live data. These limits can be relaxed if you purchase a Power BI Pro subscription: for $9.99 per user per month, this gets you 10GB per user and unlocks access to additional collaboration features.
Mobile apps for Windows 10, Android (smartphones only) and Apple iOS (iPhone and iPad) are also available free of charge. These are more for content consumption than creation, although the apps do allow for a high degree of interaction within Power BI dashboards, models and visualisations.
All about the data
In terms of data sources you’re spoilt for choice, starting with Excel spreadsheets plus CSV, XML and other files along with a wide variety of databases including, as well as Access and SQL Server, databases hosted by Oracle, IBM DB2 and Informix, MySQL, Sybase, Teradata and SAP HANA servers. Microsoft Azure sources are also supported, along with data held by a growing band of other online services including Facebook, Google Analytics and Salesforce.
Multiple data sources can be selected with, in most cases, Power BI importing a point-in-time snapshot of the chosen data source. The software can then be instructed to refresh that data as required, and do this automatically for most online sources — including files held on OneDrive. To automate the process for on-premises data, however, you’ll need to install a Power BI Gateway with personal and enterprise variants both available to Pro customers. Some data sources, such as those hosted by Azure, also require a Pro licence.
We experimented with local files, web content and a number of online services, and were impressed with both how straightforward and quick the processes involved were, and how easy it was to manipulate the imported data before starting any kind of visualisation. This is very important because, as data analysts know, very few sources are structured the way you want and are often riddled with exceptions that need to be addressed before you can move on with any confidence.
With some BI tools this part of the process is poorly served, but not Power BI, which features a powerful visual Query Editor to view and manipulate data sources in an Excel-like manner.
The Query Editor is packed full of easy-to-use tools to, for example, eliminate duplicate rows, add, remove and pivot columns, change text fields to numbers (we found this very useful) and perform calculations on data prior to visualisation — and all without the need for specialist programming skills. The Query Editor also keeps a record of the steps taken, enabling it to apply the same manipulations every time the data is refreshed and provide a visual history so you can undo and further tweak the actions as required.
One small caveat is that Query Editor is part of Power BI Desktop, which means that data can’t be shaped directly from the online service. However, models created using the Desktop tool can be uploaded to the Power BI Service complete with their associated data queries, which will be applied whenever the data is loaded or refreshed.
Reports, dashboards and more
Having edited and optionally merged your queries, you can then load the datasets and move on to building reports and dashboards.
Reports can be made up of one or more visualisations, spread across multiple pages and built using either the online service or the Desktop app. The latter has more advanced modelling capabilities, and is the tool of choice for the specialist user. Dashboards, meanwhile, are the sole preserve of the Power BI Service, comprising visualisations from different reports and datasets, each displayed as a separate ’tile’, to provide a consolidated insight into an organisation’s data regardless of location.
Report building is very easy, thanks primarily to an interface that follows similar conventions to other Microsoft applications, with a few extra twists thrown in to make life simpler for newcomers and experts alike.
As well as choosing a chart type and the data to use, for example, data fields can be dragged onto a Power BI workspace to instantly create a chart with the software working out what works best. All the usual suspects are available including bar, line and pie charts in a variety of combinations and guises, along with support for custom visualisations from third parties and, following the acquisition of Revolution Analytics in 2015, the ability to run R scripts to create visuals — although only from the desktop app.
You don’t have to stick with the proffered chart, and you can also tweak both the chart settings and display options to get the required results. However, if we have one criticism of Power BI it’s the blandness of the default colours and the paucity of visual effects on offer. ‘Usable’ is the word that springs to mind rather than ‘stunning’. This also applies to map visualisations, although we have to admit that Power BI takes the crown when it comes to overlaying geographic data onto a related map, requiring no more effort than it takes to build a bar chart.
Power BI dashboards comprise a single page, which can be larger than a browser window. They can also can incorporate images, video links and other information and are very easy to build from the online Power BI Service interface.
Dashboards are also interactive: click on a tile and Power BI will drill down for more detail; you can also type in free-format queries using the built-in Q&A tool which, for users of Windows 10, can be further integrated with Microsoft’s digital assistant, Cortana. Some practice composing queries is required here, and we found the Quick Insights option a lot simpler but no less effective in spotting hidden trends in the data.
Another neat feature of the Power BI Service are the Content Packs that bundle access to online services together with ready-made reports and dashboards. These are available for a growing number of services including Microsoft and Google Dynamics, QuickBooks Online, Webtrends and Salesforce.com. You can also build your own organisational content packs and share them with others via the Power BI Service.
A work in progress
A useful collection of sharing and collaboration features are yet another benefit of the Power BI Service, delivered directly and via Office 365. However, this area does need further work and there are no real story-telling options as with Tableau, for example. That said, Microsoft is putting a great deal of effort into developing the Power BI platform with major monthly releases delivering new features for the desktop app and continuous improvement of the cloud platform with collaboration high on the enhancement agenda. The same applies when it comes to embedding Power BI analytics into other applications, with an Azure-hosted Power BI Embedded service in preview at the time of writing.
Late to the modern BI table it may be, but Power BI is gaining ground rapidly with end-users and developers alike, proving that Microsoft hasn’t lost its touch and, once roused, can be as disruptive as any of its competitors in the increasingly crowded self-service analytics market.
Those competitors may find themselves pushed aside now that the favourite has finally entered the race.
Read more on BI