Archive for the ‘Power BI’ Category
Seems like another new bit of Power BI functionality got released today: the ability to optimize your data model for Q&A in the browser. Here’s the link to the docs:
Previously, the ability to add synonyms to your model to improve the results you got from Q&A was only available in Excel on the desktop, inside the Power Pivot window. Now you can do this, as well as new stuff like add phrasings (described here) and view usage reports, in your Power BI site.
I won’t repeat what the docs say about the actual functionality, but this seems to be yet more evidence that Excel on the desktop is no longer the central hub for Power BI. If this is the case, this is a massive strategic change, and I can understand why it has happened: the need for the ‘right’ version of Excel on the desktop is a massive roadblock for Power BI adoption, especially in enterprise accounts (see also Jen Underwood’s comments on this from yesterday). Maybe now it’s BI in the browser instead?
OK, so I’m not at WPC this year but I have just watched this video of Scott Guthrie’s session “The Cloud for Modern Business”. If you’re interested in seeing some new Power BI features take a look at the demo by James Phillips, general manager for Power BI, starting at 21:20:
Some of the new things I noticed:
- 21:40 – a nice shot of one of the new Power BI dashboards first announced at the PASS BA Conference earlier this year. You can see several new types of visualisation such as treemaps, radar charts and gauges (gauges? GAUGES? Shhh, don’t tell Stephen Few).
- 22:33 – a list of out-of-the box data sources is shown from which new models can be created. They include: Salesforce, MS Dynamics, Facebook, Google Analytics, Twitter, and Upload Excel.
- 22:50 – data is imported from Salesforce in the browser. This isn’t happening in Excel on the desktop, folks, it’s in the browser. This is significant!
- 23:10 – another new visualisation shown, a doughnut chart (if that’s the right term). I see names of people from the Power Query team in the data.
- 24:50 – a Q&A analysis is pinned to the dashboard
- 25:50 – much is made of the fact that the dashboard is touch-enabled
- 25:55 – “Partner Solution Packs” are announced. This sounds important! It seems to be referring to the Salesforce demo earlier, and these solution packs are said to include: data, connectivity to the data sources, visualisation and interactive reports. So it sounds like Microsoft are going to encourage data vendors (or other sources of data) to build these solution packs on top of Power BI as pre-packaged analytical apps. Probably a good idea.
- 26:15 – editing a dashboard in the browser and swapping one visualisation for another. Again, the HTML 5 browser based editing experience – we haven’t seen Excel once in this demo.
- 27:55 – “If there was ever a partner opportunity, this is it”. Again much emphasis here. Seems like these new Power BI features, especially the solution packs, are aimed at giving partners incentives to sell and customise Power BI (something which they have not had up to now, to be honest).
Oh, and you probably already heard that Azure Machine Learning is now in public preview. Check out the docs and samples here. I wouldn’t be surprised if there was some integration between this and Power BI to come too.
Looking for some summer holiday (or winter holiday, depending on which hemisphere you live in) reading? If so, may I suggest my new Power Query book? “Power Query for Power BI and Excel” is available now from the Apress site, Amazon.com, Amazon.co.uk and all good bookstores.
It’s an introductory level book. It covers all of the stuff you can do in the UI, it has a chapter on M, and it goes into a reasonable amount of detail on more advanced topics; it is not a 500-page exhaustive guide to the product. I’ve focused on readability and teaching the fundamentals of Power Query rather than every looking at every obscure M function, but at the same time if you’ve already used Power Query I think there’ll be plenty of material in there you’ll find interesting.
Now for the bad news: the book is out-of-date already, although not by much. One of the best things about Power Query is the monthly release cycle; unfortunately that makes writing a book on it a bit of a nightmare. I started off writing in January and had to deal with lots of added functionality and changes to the UI over the next few months; I had to retake pretty much all of the screenshots as a result. The published version of the book is based on the version of Power Query that was released in early June rather than the current version. Hopefully you can forgive this – the differences are minor – but it’s a good reason to buy the book as soon as you can! I want to do a second edition in a year’s time once (if?) the release cycle slows down.
I’ve been teased a bit for blogging and teaching so much about Power Query recently, so the final thing I want to say here is why an old corporate BI/SSAS guy like me is getting so excited about a self-service ETL tool. Well, the main reason is that Power Query is a great piece of software. It does what it does very well; it does useful things rather than what the marketing guys/analysts/journalists think is hot in BI; it is easy to use but at the same time is flexible enough for the advanced user to do really complex stuff; it is updated regularly based on feedback from its users. I only wish all Microsoft software was this good… Honestly, I wouldn’t be able to motivate myself to blog and write about Power Query if I didn’t think it was cool, and even though it hasn’t been hyped in the same way as other parts of the Power BI stack it is nonetheless the part that people get excited about when I show them Power BI. It’s not just me either – every day I see positive comments like Greg Low’s here. I think it is as important, if not more important, than Power Pivot and I think it will be a massive success.
Oh, and did I mention that I’m also teaching a Power Query course in London later this year….?
Last week someone asked me whether it was possible to do the equivalent of a SQL LIKE filter in Power Query. Unfortunately there isn’t a function to do this in the standard library but, as always, it is possible to write some M code to do this. Here’s what I came up while I was waiting around at the stables during my daughter’s horse-riding lesson. At the moment it only supports the % wildcard character; also I can’t guarantee that it’s the most efficient implementation or indeed 100% bug-free, but it seems to work fine as far as I can see…
Using the following test data:
I can run the following query:
And get this output:
You can download the sample workbook here.
I know the Power Query team have been asked for this several times already, but it would be really useful if we could package up functions like this and make it easy to share them publicly with other Power Query users…
My last post on the new Power BI features announced at the PASS BA Conference was not much more than a list of bullet points written during the keynote. Now that the conference is over and I’ve had a bit more time to reflect, I thought I would try to come to some conclusions about what they all mean. And, of course, indulge in some wild speculation too.
Microsoft’s new-found enthusiasm for working on multiple platforms is clear from two things: first, the announcement that the iOS Power BI app will be coming soon (though we should not forget that this was promised a long, long time ago and is very late), followed by native apps for other mobile platforms; and secondly the work that has gone into the HTML 5 version of Power View. Indeed, the latter was demonstrated using Google Chrome to underline the point. Of course this is the only commercially sane direction to take but it’s very welcome nonetheless.
Power View new features
The cool new Data Exploration features in Power View, which allow you to edit existing Power View reports – creating new graphs and tables and merging existing ones – are I think only going to be available in HTML 5 Power View running inside a Power BI site. The same goes for the new time series forecasting functionality. The demos also seemed to make a point of the touch-friendly interface. Now I can’t imagine that Excel Power View will move from Silverlight to HTML 5 any time soon (the Office team are notoriously conservative when it comes to big changes like this) so maybe we should assume that, going forward, the main focus will be the use of Power View inside a Power BI site to build reports and dashboards rather than Power View inside Excel on the desktop? Maybe the HTML 5 version of Power View will be what is used in the touch-optimised version of Office that is slated to appear this summer? Who knows. I liked what I saw though and these additions will go a long way towards boosting Power View’s credibility as a client tool.
Time series forecasting
It seemed like Microsoft had abandoned ‘data mining for the masses’ after SSAS data mining failed to take off, but clearly not. The time series forecasting functionality seems very easy to use (you can read more about it here and here) and I got the impression that other algorithms might be added soon – maybe Project Sage is relevant here? Another question to ask is whether ease-of-use was the real reason why ‘data mining for the masses’ failed to take off the first time around? It might have been part of the reason but another factor must surely be that business users don’t trust predictions when they don’t understand how they are made, while the data scientists and statisticians are already using other tools that give them a lot more control for forecasting.
The first reaction of several people at the BA Conference (me included) to the new dashboard and KPI creation features in Power BI sites was that this was the replacement for PerformancePoint. Personally I never liked PerformancePoint much and rarely used it, and it doesn’t seem that MS has had much enthusiasm for it in recent years. I don’t think we’ll ever see it in the cloud either. Killing it off would have the added benefit of removing a client tool from a stack that has a confusing number of client tool options right now. However I got the impression that the dashboard and KPI creation features in Power BI sites, as demoed, were fairly basic and they may not be much more than widgets that can be placed on the Power BI home screen with nothing to tie them together, but I don’t know enough to judge properly. I think it might be better to think of Power View as the place to build dashboards.
Integration with old-school SQL Server BI
The ability of Power BI sites to host SSRS reports, and for Power View to connect back to SSAS on-prem, is an important bridge between what most of Microsoft’s BI customers are actually using and the new world of cloud-based Power BI that Microsoft is promoting. For me this was the biggest announcement of all. Will these customers be interested in buying Power BI licences for their users if SSRS and on-prem SSAS is all they want to use though? I don’t know, but I assume that this will also enable mobile access to SSRS and SSAS via the Power BI mobile apps, so that will be a plus for some customers, and all the new Power View functionality makes it quite an attractive web-based reporting tool for SSAS users. The per user cost of a Power BI licence might make it too expensive to buy if all you want are web-based dashboards and mobile support but customers who are already checking out Power BI for self-service will be more likely to buy because of this.
New features in Q&A were not mentioned in the keynote on Thursday, but on Friday afternoon I saw an interesting session that detailed some of the new functionality coming in Q&A later this year including the support for phrasing that is mentioned in this blog post. It’s clear that Q&A is getting a lot of love at MS and technically it is very impressive. I’m still not totally convinced that this is something people actually want or will use but I’m less cynical than I was. I also smell a lot of consultancy money in building and tuning models for Q&A if it does get popular (the session showed that there are a lot more features coming that will help with tuning). If I understand correctly, the output from Q&A is basically a Power View report which can then be edited manually if you wish; this means that Q&A should not be thought of as a standalone tool but as one of the ways that you can start to build a Power View report, and Q&A will benefit from all of the new features that are going to be added to Power View.
Apart from the time series forecasting, which is available now, ‘this summer’ was the most common response to all questions about when the new stuff would be available. Maybe all of these features will be released when the introductory pricing period for Power BI ends? Or in time for the Worldwide Partner Conference in mid July, as Jen Underwood suggests? Hopefully it won’t be in the middle of my summer holidays.
This morning I was wondering whether we’d see any cool new stuff announced at the PASS BA Conference this year, or whether we’d see the same old Power BI demos yet again. It turns out there were a whole load of announcements, some of them very cool indeed – and here’s a brief summary:
- A native Power BI mobile for iOS will be available by the end of the summer. Other platforms (which means Android I guess) will come soon after that.
- SSRS will be available in Power BI sites and will be able to connect back to on-prem data sources. This is big, in my opinion – it will be very attractive for a lot of existing MS BI customers. Also the way to get SSRS reports on mobile?
- Power View in Power BI will be able to connect back to SSAS on premises (just Tabular though, or Multidimensional too?)
- A new KPI editor in the Power BI site will allow you not only create KPIs but arrange them to create dashboards. This looks like the replacement for PerformancePoint.
- Time series forecasting is available in Power View online now. It’s available in charts and you just forecast by dragging the chart forward; outliers can be corrected easily. Will need to check this out later.
- Power View is getting a treemap visualisation.
- Power View is getting a new Data Exploration mode that allows you to edit reports in the browser. This has a lot of cool new stuff, such as the ability to drag data points out of existing charts to create new charts.
Lots to follow up on there… more blog posts on this coming soon, I promise!
In my recent post on web services in Power Query I mentioned that while the Power Query Web.Contents() function generates a GET request by default, you can make it generate a POST request by specifying the Content option. Since this is a useful thing to be able to do I thought I’d put together a detailed example of how this works.
For my example I’m going to use the Bing Maps Elevations API, which allows you to get the elevation in metres for a set of geographic locations. You can read the documentation here:
As it says at the bottom of the page, if you have a large number of locations to pass to the web service you can do so by passing them as a comma delimited list of latitudes and longitudes using a POST request. Here’s the code for a Power Query query that generates a list of latitudes and longitudes that stretches across the UK from North Wales in the west to the Wash in the east and finds the elevation for each point:
It’s quite easy to edit the code so that it generates a list of latitudes and longitudes across the country of your choice…
Two things to point out:
- To get this to work you need to insert a Bing Maps API key in the first step where indicated. If you don’t have one, you can get your own at https://www.bingmapsportal.com
- The important step is GetElevations. The code is:
"http://dev.virtualearth.net/REST/v1/Elevation/List?key=" & BingMapsKey,
You can see here how the list of locations is passed to the Web.Contents() function (documentation here) via the Content field; notice also that I’ve had to use Text.ToBinary() on the text that I’m passing in.
Here’s the output in Power Map:
You can download the sample workbook here.
As I mentioned the other day, the public voting round of the Power BI competition has now started and the entrants are now competing for a place in the top ten and a whole bunch of cool prizes including XBoxes and Surface Pros. Having entered a demo myself I’m naturally very keen to see how I’m doing, but unfortunately doing this is a bit of a pain – you have to go to www.powerbicontest.com, click on the Entries tab, sort them, find your entry, and so on… So I thought to myself, isn’t there a better way? And of course there is… using Power BI!
Power Query is of course the tool to use to scrape the voting data from the Power BI site. It wasn’t straightforward to do but on the other hand it wasn’t impossible: there’s no single table of results, and indeed the results are spread over four different pages. To load the data into the Excel Data Model I:
- Created a Power Query function to scrape the data from the first page
- First I created a query to scrape the data from the first page using the “From Web” button
- I then did a *lot* of searching around in the HTML to get the data (for this reason I’m not going to paste the code of the function here because it’s ugly)
- I then deleted a lot of columns, created some new custom columns, and pivoted the data until in was in a nice tabular form
- Finally, once I was sure the query was working properly I turned it into a function that could get data from any page
- Next I created a table with five rows in it, called the function five times, once for each row, to get the data from each page
- Then I merged all the data into a single table of results
- I created another function to calculate the rank of an entry (which itself was an interesting challenge) and added a rank column to the merged table
- Last of all, I created some Power View sheets to analyse the data
You can download the entire workbook here. I’ll warn you, the code isn’t pretty but it does the job and it’s got a few interesting features in for all you Power Query fans.
Now let’s have a look at what the data shows.
At the time of writing this I’m currently in 16th place – and not in line for an XBox alas. You can of course change this by
and that’s even if you’ve voted for me already – you can vote once every 24 hours! But I digress…
The obvious first thing to do was to create a leader board sorted by rank:
As you can see, at the moment Carlos Costa is way out in front with a massive 180 votes; after that there’s a group of people fighting for a top five finish; and after that there’s a large group of people who might just squeeze into the lower reaches of the top ten.
Here’s a column chart showing this more clearly:
What are the top-ranked guys doing to get so many votes? Is it something to do with the number of people seeing their entry? If you put votes and views next to each other, it’s not easy to say what’s going on:
I’ve got the second largest number of views, but I’m in the middle of the pack. The problem for me is that I was one of the first people to enter and most of my views were well before voting opened; unfortunately there’s no way of knowing how many views each entry had after voting opened so I can’t say for sure what’s going on here. Certainly Alexander Pinkus submitted his demo “Few Facts About Dinosaurs” around the same time as me, has around the same number of views, and he’s currently ranked number 5. From that I can only deduce that his demo is better than mine (and having watched it I can say it is very good); he certainly has more exciting subject matter. Who doesn’t love dinosaurs? There are several other demos that have got particularly eye-catching subject matter and/or titles and they’re also doing well.
Here are views and votes plotted on a scatter chart, with the top 10, 20, 30 and so on shown in different colours:
I think this makes it a little easier to disregard outliers like me, and it’s probably fair to say that there is some kind of link between views and votes, at least for those in the top 20.
Now here’s one last column graph, showing the average number of views per vote for the top 20 ranked entrants:
You can see how badly I’m doing in this respect, despite my begging and pleading both here and on Twitter; conversely you can see that Mike Tetreault at the other end is obviously very good at getting his vote out.
I would like to say at this point that it’s tempting to moan and complain that people have ‘cheated’ and got their friends, family and colleagues to vote for them. To be honest, I think that everyone who’s got any significant number of votes will have ‘cheated’ like this to some extent. I certainly have – I’ve used my blog and other social media to try to get as many votes as possible. Indeed this post itself is a ruse to try to get more votes! At the end of the day the whole point of this competition is for Microsoft to get as many people as possible to see Power BI, so more people want to buy it. Therefore this mad scramble by entrants to get as many votes as possible will benefit all of us in the end.
Anyway, by the time you read this post there’s a strong chance that the patterns here will have changed completely. It’s a shame there isn’t more data available to play with – it would be great to have the time and date of each vote cast, and even the location of the person casting the vote. Given that you have to have a Facebook account to vote I suspect that someone somewhere does have all of this data, and more… so I wonder if they’re using Power BI too?
So the Power BI competition has entered the
shameless self-promotion public voting round and I would like to direction your attention to my entry, which can be found here. Please follow the link and vote for me! Your support is much appreciated.
In the video I take a look at road traffic accident open data released by the UK government and answer questions such as:
- Are British roads getting safer or more dangerous?
- Which days of the week do most accidents occur on?
- Which age groups are most likely to be involved in accidents?
- Which is the most dangerous section of the M4 motorway? (Spoiler: avoid Port Talbot when visiting South Wales)
The video itself is more thrill-packed than Star Wars, more epic than the Lord of the Rings trilogy and more intellectual than any film Ingmar Bergman ever made. It is guaranteed to win several Oscars and recommended viewing for the whole family.
Here are some screenshots to whet your appetite:
There are plenty of other very impressive entries that deserve to be seen, so head over to www.powerbicontest.com to take a look.
Incidentally, if you’re planning to attend the PASS BA Conference this year you can get a $150 discount by using the discount code:
One feature that was released in the December update for Power BI but got lost in all the fuss over Q&A was the ability to favourite a report and have it appear in your own “My Power BI” site. In fact there are several interesting things to see here, and while they are documented I thought it would be useful to show some screenshots.
On the main Power BI page, you can now click on the ellipses at the bottom right hand corner of a report to favourite it.
This means it will appear in your own “My Power BI” site. I hadn’t really noticed this page before; you can find a link to it in the top right hand corner of the screenshot above.
With no reports favourited your “My Power BI” site looks like this:
With a report favourited it looks like this:
So, it’s a nice place to find a user’s top reports. There’s more though. Clicking on the ‘data’ link shows you a dashboard with your own usage stats on:
Plus a list of all the Power Query queries you have shared:
And finally metadata for all of the OData data sources (this includes data sources made available through the Data Management Gateway as well as public data searches) you have used in Power Query on the desktop, even if you have never saved the workbook they’re used in to Power BI:
You can find more information on what’s going on in this last screenshot here. I quote:
When users either connect to various external and internal data sources or share queries using Power Query, metadata for the connected data sources or the underlying data sources for the shared queries respectively gets created in the cloud-based metadata repository of Power BI for Office 365. The metadata of such data sources become available in the Manage Data portal.
The Manage Data portal displays all the data sources available in the cloud-based metadata repository of Power BI whether or not you have access to the data in the data source. The data source listing displays the name, location, description, the user who last modified it, and the date when it was last modified.
I have to admit that this was a bit of a surprise to see, but I probably read something about this and forgot about it. I can understand why it’s necessary: Power BI is doing this is to help data stewards manage requests for permissions to access these data sources. Here’s the metadata that gets stored:
So, lots of interesting stuff there. I have to say that SharePoint Online and Power BI are a bit of a rabbit warren and some of the functionality in there is very well hidden, however good the docs are…