Skip to content

Month: December 2013

Delete a Shared Power Query in Power BI

Published December 11, 2013

One of the very compelling new features in Power BI is the ability to share a query. Essentially what happens is that a user will build up a Power Query Query with all of the necessary transformations and filters, and when it is complete, the query itself (not the data) can be published to the data catalogue. To do so, from the query window, hover over your query to see the fly out dialog, click the ellipsis at the bottom, and select “Share”.

image

Once shared, if blessed by the data steward, the query is added to the data catalogue, and can be discovered by others simply by searching for it from the Power Query Online Search interface

image

You can also see all of the queries that are shared to the organization. To do this, from a Power BI app, first, click on the “Manage Data” link at the top of the browser window.

image

Once in the Manage Data section, you can select “my queries”, where you will be presented with a list of all shared queries. You can search for queries, or even analyze your query’s usage patterns.

image

However, one thing that you cannot do is delete the query. This can’t be done from within the management application, it must be done within the Power Query application itself. To delete a shared query, first, open Excel, click on the Power Query tab, and then click on the Shared button in the ribbon. This will open up your list of shared queries in the data catalogue. Next, hover over the query to be deleted, wait for the query fly out dialog to open, and then click the delete link.

image

Reopening the data catalogue in the manage data app will show the query to have been deleted. Pretty straightforward, but also fairly well hidden.

Leave a Comment

Power Pivot Updates to Excel 2013 – November 2013

Published December 3, 2013

Did you notice the update to Power Pivot in Excel 2013? Did you know that one was available? Neither did I until I heard about it 2 weeks ago. It certainly came without fanfare, and I’ve only finally gotten around to getting it on my system recently (more on that later). The only visible change that I can see is the support for synonyms. You have always been able to rename columns in Power Pivot, but now you can specify alternate names, or synonyms. This is to better support the upcoming natural language query feature called Power Q&A.

You’ll know if you have this update by clicking on the Power Pivot tab, clicking the manage button, and finally selecting the Advanced tab from the Power Pivot window. If you see the synonyms button in the ribbon, then you have the update.

image

Working with synonyms is pretty straightforward. I have a table of airline codes loaded, and the table has a “comments” column. I may want to refer to that column as “notes”, or “other information”. To do so,I click the synonyms button in the ribbon. Power Pivot flips to diagram view, and opens up the synonyms editor on the right side of the window. For the “Comments” field, I simply enter my alternate terms separated by commas. And that’s about it.

image

Once it is available, Power Q&A will make use of these synonyms when performing natural language queries, but for now, they’re not really used for anything. What I find interesting here though is the way that this update has been delivered. 

As I mentioned above, I was unaware of this update until I heard about it through word of mouth. It was not added through Windows update of WSUS, but it was streamed out to users that are using subscription based Office installs from Office 365. In particular, it was sent to users that installed Office using the new Click-To-Run delivery method. I was using an MSI based Office installation, so I never saw it. In order to get this update, I literally had to uninstall Office, then reinstall it using Click-To-Run. As far as I am aware, this is the only way to receive this update.

I also find it odd that while client side changes to Lync were called out in the “What’s New: November 2013” article on the Office 365 Technology blog, no mention whatsoever was made of this change. It’s almost as though this delivery system is being tested with a low impact feature first. I also find it interesting in that the new “cloud first” deployment approach applies not only to the services themselves, as you might expect, but to the clients that use those services. It makes sense, but may take some getting used to.

I’ve been one of those MSI holdouts. I’m an old dog, and Click-To-Run is a new trick, but I’ll be using it moving forward. There’s really no reason not to.

Leave a Comment

Whither Power Pivot for SharePoint? A Comparison With Power BI

Published December 2, 2013

Power BI is a hot topic within the Microsoft Business Intelligence community. Since it was announced last July at the Worldwide Partner Conference in Houston, it’s pretty well been the only thing that has gotten discussed with regard to Microsoft BI. There are good reasons for this, the addition of a mobile BI story, its ease of use, and it’s compelling new features (Power Q&A!) make it the shiny new toy. I’ve certainly been paying a great deal of attention to it, but what about the more traditional products, in particular Power Pivot for SharePoint?

One of the big questions around Power BI is whether or not there will be an on premises version of Power BI. Whenever asked, Microsoft responds with “Power BI will initially be available through Office 365”. This answer causes concern to those with requirements that can not or may not be met by a cloud based solution. Many worry that Microsoft’s move into devices and services are leaving on premises installations behind. I’ve been of the opinion that this is evidence of a “cloud first” release strategy, as opposed to a “cloud only” release strategy. Recent statements by Microsoft officials would tend to confirm this, but the question should be asked, does it even make sense to bring Power BI on premises?

A little explanation is in order. The collection of tools that is Power BI is centred around the x-Velocity data model that is part of Excel 2013, and available through Power Pivot in Excel 2010. All of the client based design tools can be used with Excel without the need for a Power BI license. With Power Pivot for SharePoint, it has been possible to interact with these data models through a browser for several years already, and with the 2013 updates to Office 365, it is even possible to interact with these models in the cloud. What has been missing from the Office 365 BI story has been an automatic way to keep on premises data refreshed, and the ability to work with large models.

While these two capabilities have been available on-prem for years through Power Pivot for SharePoint, they are only coming available to Office 365 now with Power BI. It doesn’t really make sense to bring these capabilities on-prem when they already exist. However, complicating this picture is the host of new capabilities that are being introduced by Power BI. In many ways, it’s a “leapfrog” product, filling in gaps in some areas, while moving forward in others. A comparison of the two products can be seen below.

image

Let’s walk through these features. Obviously both products work with the embedded x-Velocity data models. Power Pivot for SharePoint from SQL Server 2012 SP1 can render Power View in Excel, as can Power BI. Power View has some interesting variations however. Through the Power View that is available via Power Pivot gallery, live Power View reports can be exported to PowerPoint decks. This feature is not available through Excel Power View, or through Power BI. On the flip side, on-prem Power View reports (both types) use Silverlight for rendering, whereas Power BI will allow both Silverlight and HTML 5 renderings (confused yet?).

Both Power Pivot for SharePoint and Power BI are powered by an Analysis Services engine. The Power Pivot gallery available on-prem provides for the ability to connect to that engine through Excel with an Analysis Services connection. This makes the embedded model created in one workbook available to Excel clients as what appears to be a data cube. This is not available through Power BI, although the OData publishing features fills that gap somewhat.

The ability to refresh the data in the embedded model is critical and is to my mind, the most important feature in Power BI. However, at best, this brings it to parity with Power Pivot for SharePoint. For the moment it supports only SQL Server on-prem data sources where Power Pivot for SharePoint supports all Power Pivot data sources for refresh. As of this writing (December 2013) neither product supports the refresh of Power Query data sources, but this has been promised for Power BI “soon”. No announcement has yet been made as to the refresh of Power Query data sources on-prem.

The default maximum file size for SharePoint 2013 is 200 MB, and the default maximum workbook size for Excel Services is 10 MB. These values can be changed on prem, making the maximum possible size for a data model equal to the maximum possible file size in SharePoint – 2 GB. This equates to the maximum file size in Office 365 as well, but that 10 MB Excel Services limit can’t be changed in Office 365. Power BI supports model sizes up to 250 MB by removing the model portion from the workbook, and housing it in an Analysis Services instance, allowing the workbook to remain within the 10 MB limit. It’s wonderful to be able to move beyond the 10 MB limit that we’ve had, but it’s not without its limits.

Both products have a thumbnail gallery, but the one available through Power BI sites is arguably more sophisticated, and it doesn’t rely on Silverlight for rendering. The rest of the feature set outlined above is all in Power BI’s favour. Power Pivot SharePoint will optimize workbooks (move the data model into Analysis Services) on first interaction, but Power BI can do that ahead of time, minimizing user inconvenience. The rest of the feature set, OData feeds for on-prem data, Power Q&A, and mobile clients are only available with Power BI.

The fact that Power BI for SharePoint on premises has not been announced isn’t as disappointing as it may seem. Parts of it, the Data Management Gateway in particular, aren’t even necessary in an on-prem scenario. This is pure speculation on my part, but if I had to bet, I would expect to see the relevant features from Power BI (Power Q&A, OData publishing) put into Power Pivot for SharePoint in a future release of that product. It also wouldn’t surprise me to see it renamed to Power BI for SharePoint. As to when this could happen I have absolutely no idea, but we should keep in mind that this is a product that ships with SQL Server, not with SharePoint, and I haven’t heard of anything like this in the previews of SQL Server 2014.

3 Comments