Note – this article was written during the preview bike of Ability BI, and sure behaviours and screens may change by final release.

Quick, what's the biggest file that you can upload into SharePoint? Equally with annihilation SharePoint, of form it depends. This article explains some of the file size boundaries in SharePoint and Office 365, how they impact Power BI, how to have advantage of the storage changes that Power BI provides, and some of the intricacies of using the xVelocity model with Power BI.

The maximum file upload size is 250 Mb by default with SharePoint 2013, and 50 with prior versions. This setting can be changed on an application past awarding basis within Central Administration, and once set, is a difficult limit. If your file exceeds it, you lot won't be able to upload it into SharePoint.

If you use Excel Services, you may also note that the default maximum size for an Excel workbook is x Mb. This as well tin be changed in Central Administration (information technology's a belongings of trusted file locations). If you upload a workbook that exceeds this limit, Excel Services won't exist able to return it, and yous'll need to use the Excel client. Depending on performance limitations, I often like to arrange this setting to match the maximum file upload size to avert end user confusion.

SharePoint Online in Office 365 works a niggling bit differently. Until recently, the default upload limit was unchangeable. Recently even so, SharePoint Online removed the upload limit altogether, so now the maximum file upload size is 2 Gb, which represents the maximum file size that SharePoint can store. For Excel Services Online in that location are settings that can be adjusted, merely the maximum workbook size isn't i of them. With Excel Services in Function 365, the maximum workbook size is 10 MB. Catamenia.

If yous use Ability Pivot to do assay, data is brought into the data model that is embedded within the workbook. That workbook is then stored in SharePoint, and on first use of the model (clicking a slicer, expanding a dimension, or anything requiring the retrieval of data from the model), an instance of the model is created in a backing Assay Services engine past Excel Services.

Given that all of this is wrapped upwards into a single workbook file (xlsx), 10 MB would seem to exist pretty constraining for data storage, and it is. The information model is very efficient, and information is highly compressed, just 10MB is simply likewise small for most involved analyses. This isn't a problem on premises, where additional resources can be allocated and the limit increased, but in Office 365, you lot're only stuck with the 10 MB limit.

Enter BI Sites, the recently announced Office 365 based Business Intelligence app that is part of the Power BI application suite. BI Sites is a SharePoint app that provides boosted capability to workbooks with embedded models stored in SharePoint Online libraries. BI sites allows for data models equally large as 250MB. The BI Sites app doesn't actually store content, it just renders it, and provides additional capability such equally automatic data refresh. BI Sites also relies on Excel Services to return the content, and then does a Ability BI subscription increase that workbook limit to 250 Mb? Nope – that limit is still firmly in place. So how does it become around this limit?

As mentioned higher up, when a model is accessed past a user by forcing a information retrieval from the model, Excel Services creates an instance of the model in the backing Assay Services instance, if it hasn't already been created. Subsequent calls just utilize the bankroll version. What Power BI does is information technology preloads this model, and and then drops the model from the workbook (information technology is reconstituted on download). Given that the model is the large part of the workbook this drops the file size considerably, allowing it to work within the limits imposed past Excel Services.

Find that the limit of 250 Mb higher up is specified for the model, Not for the workbook. The workbook limit is nonetheless 10 Mb, and this is quite visible if you do things in the wrong society, at least in the Power BI preview. To demonstrate we will work with a model that is like to the 1 that I created in this article, which is a rudimentary budget analysis with Great Plains data. There are three versions of the analysis file for sit-in purposes.

image

In the first version, the information was first imported into Excel worksheets using Power Query, and and then loaded into the model before the model was edited. It is obviously the largest of the iii, as information technology contains both the original data, and the more than optimized model. In the 2d file, the data was loaded direct into the model with Power Query. Later model edits, the analysis was created using a simple pivot table and pin chart. It is the smallest of the three, as the data is contained exclusively within the optimized model. In the last version of the file, the data was imported into Excel worksheets using Power Query. Take note of the fact that the file is 12 MB, 50% larger that the model only version, and all of which is counted when considering the Excel Services limit.

Subsequently uploading these three files to an Office 365 site, Only the EnbGPDataModelOnly file tin can be accessed via Excel Services direct. This makes sense because the other ii are larger than the ten MB limit, and Excel Services can't exercise annihilation with them at all, resulting in the fault beneath:

Sorry, we can't open your workbook in Excel Web App because it exceeds the file size limit. You'll need to open this in Excel.

If nosotros now navigate into the Power BI application, Nosotros will see a warning symbol on the tiles for our workbooks. This is because they have not yet been optimized for viewing on the web. What does that mean? Information technology means that the model has not yet been extracted from the workbook, and attached to the Analysis Services engine.

image

To do this, click the ellipsis on the tile, and then select Enable. After confirming, the model will exist extracted, and you receive a confirmation. In our instance, the merely one that will be successfully enabled is our EnbGPDataModelOnly file, only the reason is unlike for the other 2 files. In the case of EnbGPExcelOnly, the data model was never populated, and results in "This Workbook could non be enabled for spider web viewing considering information technology does non contain a data model".

image

This makes perfect sense, but it does mean that all of the features available through the BI Sites characteristic are unavailable to workbooks that don't use information models. There is 1 exception to this however. The Power BI app for Windows 8 and iOS tin can return workbooks without models, provided that they are inside the 10MB limit.

If we endeavour to enable the EnbGPDataModelandExcel file, which does contain a information model, we get the error "This workbook could non be enabled in Power BI for Function 365 considering the worksheet contents (the contents of the workbook that are not contained in the data model) exceed the allowed size.

image

If nosotros look at the file size with only the model, it's about 8.7 MB. The file without the model is 12 Mb, so even with the model extracted, the limit is exceeded, and the enablement process detects this.

On a side note, I call up that these mistake messages accept some interesting language. They make reference to "Power BI for Office 365". This implies, to my mind at least, that at that place may exist a version coming that isn't for Function 365. No on premises version of Ability BI has always been mentioned by Microsoft, but this may hint at it.

When consummate, the failed and successful enablements are easy to spot.

image

Clicking on the center tile volition successfully render the workbook.

Next, let's work with a file that can do good from these new features. I created a model that's identical to the "model only" version with the only divergence that I import all of the data from the 3 tables, not just the selected columns. The resultant file (named EnbGPBigModel.xlsx) is 54 MB on disk – well above the x MB Excel Services limit, simply below the 250 MB Ability BI limit. However, Once uploaded, clicking on information technology directly in the library gives the "file size exceeded" fault bulletin. What's up with that?

The reason is that Excel Services merely sees that it is too big, and gives up. In gild to extract the model, we must showtime enable it in Ability BI earlier nosotros can piece of work with it in Excel Services. To do that, nosotros simply echo the higher up procedure by navigating to the Power BI app, and enabling it.

image

Once this has been done, the workbook can be accessed past clicking on it inside the Power BI app here, in the Power BI mobile app, or by navigating dorsum to the source library and using information technology direct with Excel Services.

image

image

Therefore, when building models, information technology is vitally important to distinguish between the size of the model, and the size of the rest of the workbook. While the model tin can grow to 250 MB, the sum of the rest of the content cannot exceed 10 MB. Note to Microsoft – we could actually use a good term here, as opposed to "remainder of the workbook". Allow's call information technology the spreadsheets for our purposes right now.

So, how practice we know the size of the model vs the size of the spreadsheets? Well, an Excel file (xlsx) is actually just a zip file in disguise. In fact, if we rename the file to end in a .zip extension, we can fissure it open up with a ZIP viewer (or as a folder in Windows). If we practice so with our file that contains both the spreadsheets and the model, open information technology up, and then drill down to the xlmodel folder we'll see the file particular.data.

image

This file is your data model. in this case, information technology is 7.8 MB in size. Decrease that value from the size of the overall xlsx file, and yous have the size of your spreadsheets, which is the part that must fit within the x MB limit. When done, merely rename the extension xlsx.

If we continue to have a trouble, or as a matter of good practice, an excellent tool to apply is the Workbook Size Optimizer tool from Microsoft – bachelor here. This is (yet another) Excel add-in that will assist to farther optimize your model and to aid reduce the file size. Just open your workbook, run the add in, and follow the prompts.

Nosotros can see that although the 250 MB model size in Power BI helps to make Office 365 a viable BI platform, it does still require a sure sensation on the part of users. The most important lesson to learn from this is to endeavour to avert importing information straight into Excel. Whenever possible, bring the data straight into the model, bypassing whatever Excel storage. This is a expert idea in any event, only Power BI farther underscores the need for information technology. When using Power Pivot, information technology'southward fairly straightforward, simply the data acquisition interface available in Ability Query tends to adopt Data import. When using Power Query, intendance must exist taken to avoid Excel import, and I'll be posting another commodity on how to practice this shortly.