Category: Power BI

Problems Manually Refreshing Power BI Enabled Workbooks

Published February 5, 2014

Office 365 (without Power BI) has supported data refresh for Excel and PowerPivot for some time, and it works well provided that the data source is both in the cloud, and is one of the supported data sources. To refresh a workbook, simply open the workbook in the Excel Web App, open the data menu and select “Refresh All Connections”.

Up until just recently, this was how it worked with Power BI workbooks as well, with the additional ability of being able to refresh on premises data through the Data Management Gateway. However, with the latest refresh of the Power BI application, and its support of scheduled refresh, this has changed. Now, if you follow this procedure and attempt to manually refresh a Power BI enabled workbook from an on premises data source, you will receive an error.

The error is pretty self-explanatory, so I won’t try to explain it. Scheduled data refresh hasn’t just been added, it has replaced the old refresh method. That’s all well and good, but what about those use cases where we want to manually refresh data? The good news is that it hasn’t been lost, it’s just been moved. It is, however, well hidden.

To refresh the workbook on demand, you must first open the Power BI application, locate your workbook, and click on its ellipsis to open its context menu.

Next, you need to select “Schedule Data Refresh”. Now I know that we don’t want to schedule the refresh, but to update it on demand, so you’ll just need to trust me here. Selecting “Schedule Data Refresh” will open the scheduling interface into either the history tab (if the workbook has already been scheduled) or the settings tab (if it has not). In any event, you’ll need to be in the settings tab.

If the workbook has not already been enabled for scheduled refresh, it will need to be. Once it has, the “save and refresh report” button will be available. If it has already been saved, the button will read “refresh report now”. In either case, clicking on it will start the refresh process immediately.

On-demand refresh is still available, but I have to say that it’s well hidden. The fact that it has moved into the Power BI application means that a Power BI license will be required to refresh it on demand, which seems quite reasonable to me. However, some better visual cues would be a big help. For example, why not add “refresh now” to the context menu in the Power BI application?

In addition, given that the refresh is being initiated manually, some visual cues around the status of the refresh (started, in progress, completed) would help considerably. As it stands, the only status information is available after the refresh completes, on the history tab of “Schedule Data Refresh”.

There has also been another subtle change around how workbooks are displayed in Power BI. When a workbook is opened from the source Office 365 library, the standard Excel Web App interface is displayed, with options for opening in Excel, editing, etc. displayed.

However, if you first navigate to the Power BI application, and open the workbook by clicking on the thumbnail, it will open in the browser but without the Web App chrome.

~~I’m not sure what the reasoning is for this different behaviour, but it’s a change, and something that you should be aware of.~~ UPDATE 14/2/14 – It has been explained to me that the reason for this different behaviour is an effort to reduce screen clutter for those using the Power BI application. It’s a consumption mostly application, so this change makes sense in that context. It’s also possible to add the Excel Web app chrome back in by using a new “action bar” (my name). If you look to the bottom of the worksheet window, you’ll see it, and its three icons.

The three icons, from left, allow you to submit feedback to Microsoft, to get embed codes for the report (a new feature!) and finally, to restore the standard Web App chrome (for editing etc.)

2 Comments

Scheduled Data Refresh in Power BI

Published February 5, 2014

It’s finally here.

Quietly, sometime over the past few days, Microsoft updated the Power BI application in Office 365 along with the Data Management Gateway (get it here). Chief among the changes is the ability to schedule data refresh, which to my thinking, is the single most important feature for deploying Business Intelligence solution in the cloud.

Until now, it has been possible to refresh Excel worksheets with embedded data models on demand, In fact, if your data source was also in the cloud (and was one of the supported data sources), you don’t need Power BI to do it, it’s supported natively in Office 365. If your data source is on-premises (and either Oracle or SQL Server), you can do it through the Data Management Gateway. What has been missing is the ability to have the data model refreshed in the absence of interaction. No longer.

This capability can of course be found in PowerPivot for SharePoint on premises. It is configured on a per workbook basis in the PowerPivot Gallery, which is a PowerPivot focused view of a document library that contains workbooks. In works in much the same manner with Power BI, with the Power BI application taking the place of the Power Pivot gallery.

Configuring Scheduled Refresh

To turn on automatic refresh for a workbook, you need to access the workbook’s BI context menu. To do this, first, open the Power BI application, then locate the workbook that you wish to have refreshed automatically. Click on the ellipsis to access the menu.

A number of items have been added to the menu, and to the preview graphic itself. To the left of the ellipsis is information on when the model was last updated, and the context menu adds the ability to edit in Excel and to add to Q&A as well. However, the feature that we’re interested in is the scheduled data refresh, and selecting that option takes us to the scheduled refresh screen.

If refresh has not already been configured, it opens into the settings tab, otherwise, the history tab will be opened.

To turn on refresh, simply select the “on” slider. If your model has multiple data sources you can choose them to be included or not. As far as I have seen, you can only have one schedule per workbook, so if a data source isn’t included, it simply won’t be updated.

Next, select your refresh schedule, which will be either daily or weekly. By default, your schedule will have a shelf life of 90 days, and will turn off after that time. You can adjust this period by changing the value of the “Ends By” field. You can then select a time (or a day and time if appropriate) for the refresh to occur. Finally, any errors will be sent to the email address that you specify in the notification field.

That’s really all there is to it. Selecting “save settings” will save the schedule, and “save and refresh report” will save the schedule, and attempt to run an immediate refresh.

If the data source is cloud based, it will be queried directly by Power BI, and if it is on premises, it will contact the appropriate Data Management Gateway process and refresh through it. I would love for there to be a little more status information for refreshes in the administration portal, but for now, the refresh will either succeed or fail. However, If the data source is on premises, you can open the Resource Monitor on the gateway machine, and monitor the “diawp.exe” process.

Once the refresh kicks in you’ll notice it using a lot of send bandwidth.

Selecting the “history” tab will of course show the refresh history, and what the refresh schedule for the workbook is. At a glance you can see whether or not refreshes succeeded or failed, how long they took, and how they were initiated.

~~I should note here that I have been working with the Power BI preview for several months now, and in order to get scheduled refresh to work with on premises data, I did need to install the latest~~ ~~Data Management Gateway. I’m not sure if this was because scheduled refresh required it, or just because it had expired (it had), but I would recommend installing it in any case.~~ Update 10/02/14 – I have been informed that scheduled refresh does not require the latest data management gateway, but I would recommend getting it all the same – it’s the release version.

One interesting side note. After installing the latest DMG, accessing its configuration shows its version to be 1.0, where previous versions were all point releases (the latest being 0.11). I can’t help but assume that the General Availability of Power BI isn’t far away. UPDATE 10/02/14 – In fact, Power BI went GA today, and this is in fact the GA version of the Data Management Gateway.

Limitations

There are a number of behaviours and limitations that you should be aware of when using scheduled refresh in Power BI. The below items are by no means exhaustive, but simply things that I have either run into, or been made aware of.

Too much data

As I have outlined previously, the maximum size for an embedded workbook model in Power BI is 250 MB. If a user attempts to enable a larger model, they will receive an error message. However, scheduled refresh now allows for the possibility that the model could start small, and then grow to exceed this limit through refresh. What then happens when the limit is exceeded?

When the model is opened for refresh, its size is checked. If it’s OK, the refresh proceeds, and the model is updated. If the model now exceeds the limits, the next refresh will fail, as will any attempts to work with the file through a browser, until the size of the model is reduced.

Collisions

Refreshes can take a fair amount of time. During this period, the file is not checked out exclusively to the refresh process, and if it is edited by a user in that time an edit collision could occur. If this situation arises, scheduled refresh will simply discard its updates and fail.

Frequency

As mentioned above, the two options for schedule frequency are daily and weekly. I was really hoping to see hourly. Monthly and annually would be great too. As it stands, if your data needs to be more current than daily, then Power BI still won’t work for you (without heavy customization). Of course, the reality is that daily is frequent enough for most situations, and this at least puts data refresh on par with its counterpart in PowerPivot for SharePoint.

If someone from the product team is reading this, hourly updates would be my #2 feature ask, for both Power BI and Power Pivot for SharePoint. (for the #1 ask, read on).

Limited Data Sources

At the moment, the refreshable data sources are those that are currently supported by Office 365 in the cloud (Azure SQL, SQL on Azure VMs, and OData feeds with simple or no authentication), and those supported by the Data Management Gateway (SQL Server 205 +, Oracle 10g +). A full list can be found in the official documentation here.

This is a great starting list, but it is limited. There are quite a number of other data sources that would be great to see on this list, multidimensional sources being right up there. However to my thinking, the most glaring omission on this list is Power Query.

The above data sources are supported if the data was imported into the model through Power Pivot’s import feature (or the native features in Excel 2013). However, if a user takes advantage of the many excellent features available in Power Query, their model will not be automatically refreshable. I have already seen in the preview forums that this difference confuses users, and given that Power Query is a highly touted integral component of Power BI, it needs to become a first class citizen, and soon. That’s my #1 ask – again, both for Power BI and Power Pivot for SharePoint.

However, for the moment, what you need to know is that if your model is built with Power Query, it can’t be refreshed automatically.

Limitations aside, it appears to me that Power BI is an absolutely compelling value proposition, and the inclusion of scheduled refresh completes the picture. I can’t wait for it to be released into the wild. Let the games begin!

11 Comments

Append Multiple Tables in Power Query

Published January 8, 2014

Power Query transformations can be very powerful, but they only work on one data source at a time. Sometimes data providers will only provide their data in discrete chunks, like one category per table, or data may come from different providers with the same schema. Ultimately, we want to show these different sources together with different attributes, so that it may all be analyzed simultaneously. Power Query supports this requirement through its “Append” function.

Consider the following scenario. We want to analyze alcohol consumption data. The World Health Organization provides extensive data on this, but it is reported separately for each type of alcohol.

(source: Global Health Observatory Data Repository)

There is a source for total, but it does not break the consumption down by type. What we need to do is to append the four (beer, wine, spirits, other) categories together. To start with, we need to query for each type separately. The data is provided by the WHO as a CSV data file, but it’s directly downloadable, so we will use the “From Web” data source (which makes refresh simple and removes a download step). First we open up Excel, click the Power Query tab then click on the “From Web” external data source. We then enter the URL of our first category (beer) and click OK. The query editor window will then be opened.

We don’t need to do much in the way of transformation, just turn the first data row into headers (by clicking on the upper left grid icon). Then, we give the query a name (Beer), and importantly, we deselect the “Load to worksheet” load setting.

By default, the “Load to worksheet” option is selected (I’ve griped about this elsewhere), but in this case, we don’t want to load the data into the model OR the worksheet. Why not? We’re going to be using this query as an append source with other queries into a final all encompassing appended query, so there’s no point in incurring the data load or storage overhead of the extra data.

Once complete, we repeat this procedure for the other categories. Each of these queries have the same schema, so no transformations need to be made, but keep in mind that there may be cases where we need to do extra work to make sure that the schemas match. Once all of the category queries have been defined, we are ready to perform the append.

From the Power Query tab, we click on the “Append” button which allows us to select two tables.

This will create a new query with the result of the append operation. But wait a minute, we have four tables to merge, and the UI only gives us an option for two. We could append our two other tables together, create another append destination, and then append the two append results together, but that’s very cumbersome, and it certainly doesn’t scale much beyond 4 input sources. The ideal scenario would be to append all four sources in one step. Fortunately, that’s possible with Power Query – it’s just not obvious.

From the query that results from the initial append operation, we can see a formula in the formula editor – Table.Combine({Beer,Wine}).

This formula uses Power Query’s “M” language, and the good news is that not only can it be easily edited, the Table.Combine function takes more than 2 arguments. It’s a simple matter to add in our other queries to the function to get a single append function.

It should be noted that if the queries have a space in their name, it is necessary to refer to them as #”query name” – i.e. #”beer consumption”, etc. At this point, we give the resultant query a name, and change the load options to load into the data model. Once loaded, we can import any other supporting data, enhance our model, and start analyzing.

This single append also demonstrates that whether or not a particular feature is supported through the user interface, It may be possible to accomplish the goal through some creative M language work. If you’re interested on some more things that can be done with M, I suggest you check out these examples on Chris Webb’s BI blog.

9 Comments

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”.

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

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.

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.

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.

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

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.

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