Skip to content

Category: Uncategorised

Software Boundaries and Limits for Power BI

The Office team have published a  comprehensive resource outlining all of the current boundaries and limits for SharePoint Online generally. Power BI offers a host of new capability, and in the absence of anything official from Microsoft, I thought it might be a good idea to itemize my findings below. I’m clearly leaving things out, and if you have something to add, I’d love to here from you. This list is over and above the inherent boundaries of Office 365.

This is obviously not official, and I’ll try to modify it as these limits change, but for now, here are the current limits for Power BI, in its preview form.

Feature Description
Maximum workbook size (not enabled) 10 MB
Maximum size of non-model workbook data (enabled) 10 MB
Maximum size of data model 250 MB
Maximum workbook size (enabled) 260 MB (250 MB model + 10 MB workbook data)
Maximum size of workbook for editing 30 MB (This includes interactive refresh. Larger workbooks still support interaction)
Number of tables/views in a database visible to Power Query Navigator 2,000
Maximum number of thumbnail reports in Mobile application 6
Maximum OData query size with the Data Management Gateway 100 MB


Updated 2013-11-17 – Navigator limit changed to 2,000 and OData query limit removed with the Nov 2013 version of the gateway.

Leave a Comment

Update Your Power BI On Premises Data Management Gateway

I’ve mentioned before that in my opinion, one of the most compelling features of Power BI is the ability to automatically refresh data models in the cloud with data from on premises. Up until now, this couldn’t even be done manually – you needed to download the workbook, use the Excel client to do the refresh, and then re-upload. I’ve had the opportunity to work with the preview for a little while now, and while automatic refresh still isn’t there, it is at least possible to manually refresh the data in the browser, which forms the underpinnings of the upcoming automatic refresh feature.

On premises data refresh is restricted for the moment to SQL data sources (I’ll be writing more about this shortly), but until today, I had been unsuccessful in getting even those to refresh properly. Needless to say, I was pretty disappointed. After trying a number of scenarios, and spending some time in the forum (I highly recommend the forum), I got some help from the product team. It turns out that I was pretty quick off the draw, and the version of the gateway I had installed didn’t work. Since the release, it had already been updated.

So how do you know that you’re out of date? If you go to the Power BI Admin Center, and click on Gateways, you’ll see all of your registered Data Gateways. Under status, if everything is up to date, and running, you should see “online”.


However, in my case is said “expiring”. This means that a newer version is available. OK, so how do I get access to this wonderful new version? Just to the left of the status, there is an ellipsis. Click on it to open the Gateway Configuration dialog (that’s what I’m calling it).


There are a number of options available here, but at the bottom of it is a link – “Download gateway installation package here”. It’s pretty self explanatory. It may go without saying, but in case it doesn’t, you will need to be on the machine that is running the gateway to install it. It’s a simple install, and it will install over top of the existing gateway. If you have the gateway status window open, you will need to close it before the install completes.

One other thing that’s worth mentioning about this dialog. Clicking the machine name next to “Installed On” will open up a remote desktop session to the machine running the gateway. You will need to be on the same network for this to work – it’s simply launching Remote Desktop – it doesn’t have any tunnelling protocol a la Windows Live Mesh, but it’s handy nonetheless.

In my case, this wasn’t quite enough to get data refresh working. The final step was to delete my original data connection, and then create a new one. Once that completed, I was able to use a browser to update my cloud based data model from on premises data.

Leave a Comment

Power Pivot and Power View Demonstration – Profit Analysis for Great Plains

Power Pivot and Power View are tools that quickly allow power users to get answers from their data. In order to help demonstrate how easy it is to start working with these technologies, I published a blog on the MVP blog site. This article walks through the process of creating a simple Power Pivot data model from a Great Plains database, and then using that model to do a simple profit analysis and visualization using Power View. Finally, the model is shared out on Office 365.

You can read the article here.

Leave a Comment

How to display Specific Content to Anonymous or Authenticated Users in SharePoint

A long time ago I wrote about the usefulness of the SPSecurityTrimmedControl in selectively displaying content based on a user’s permission level. It supports a myriad of different permission options, and my friend Marc Anderson has an excellent post in which he outlines all of the possible permission levels that can be used with this control by manipulating the PermissionsString attribute.

What is less well known about this control is that it can also be used in an application that supports anonymous access to selectively display content based on the user’s logged in state. This might be to display a custom log in button or link that should only be displayed when a user has not logged in. The way that this is accomplished is through the  AuthenticationRestrictons attribute. There are three possible values:

  • AllUsers
  • AnonymousUsersOnly
  • AuthenticatedUsersOnly

I have no idea why the AllUsers value exists. It’s not much of a restriction. The other two values are pretty well named, so I won’t bother explaining them.

A pretty typical usage scenario might be to build a page layout that will display a content field for all users, another field exclusively for anonymous users, and another for logged in users. The exclusive fields would need to be created and added to a content type prior to the creation of the layout. A simple example of this might appear as follows:

<PublishingWebControls:RichHtmlField id="PageContent" FieldName="PublishingPageContent" DisableInputFieldLabel="true" runat="server"/>
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spAuthenticated" AuthenticationRestrictions="">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAuthenticated" runat="server" />
<SharePoint:SPSecurityTrimmedControl runat="server" ID="spUnAuthenticated" AuthenticationRestrictions="AnonymousUsersOnly">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />

In this example, the PublishingPageContent field is displayed on the page first, and depending on the logged in state, one of the remaining fields will be displayed. Of course, in order to use the control, the WebControls directive must first be added to the page:

<%@ Register Tagprefix="PublishingWebControls" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

The above example is for SharePoint 2010, but the namespace also exists in 2013.

This is simple enough, but there is a practical problem with this approach. How do we edit the content for anonymous users on the page? Although the control is there, editors are going to be authenticated users, and therefore the content will be completely hidden from them.

To do this, we can treat the Anonymous filed like other page metadata, and include it (also) in an edit mode panel, without the spSecurityTrimmedControl.

<PublishingWebControls:EditModePanel class="ewiki-margin" runat="server">
    <PublishingWebControls:RichHtmlField FieldName="PageContentAnonymous" runat="server" />

The contents of the edit mode panel are only displayed when the page is in edit mode, so authors will be able to edit anonymous content and authenticated content in one step.

This control isn’t limited to publishing scenarios, but does require the Publishing namespace, and therefore requires at least SharePoint Standard license.

1 Comment

Configure Your SharePoint Farm for Business Intelligence

It’s been far too long since I’ve posted any new content, but that is about to change. In the meantime, I wanted to share out the slide deck that I used for my presentation at the SharePoint Conference 2012, and more recently at SharePoint Saturday Ottawa 2013.

In addition, if you’re interested in watching a full video of the SPC12 presentation, you can do so below.

Leave a Comment