***********
This post is outdated and has been replaced with the following:
The Conceptual Data Model and Limitshttps://blogs.technet.com/b/powerbisupport/archive/2015/08/11/the-conceptual-data-model-and-limits.a...
***********
When using an Excel workbook, you will encounter some limitations with regards to the file size. I’ve had to explain this a few times to folks, so I wanted to get this out to make sure people understood what those limits are. Here are the different scenarios that you could encounter. I will go through each one.
|
|
Workbook Size |
Data Model Size |
Requires Power BI License |
Action Required* |
|
Scenario 1 |
< 10mb |
No Model |
NO |
NO |
|
Scenario 2 |
> 10mb |
N/A |
N/A |
N/A |
|
Scenario 3 |
< 10mb |
< 10mb |
NO |
NO |
|
Scenario 4 |
< 10mb |
> 10mb & < 30mb |
YES |
NO |
|
Scenario 5 |
< 10mb |
> 30mb & < 250mb |
YES |
YES |
|
Scenario 6 |
< 10mb |
> 250mb |
N/A |
N/A |
At the bottom of this post you will find how to determine the sizes within an Excel Workbook.
Scenario 1: Excel Online with workbook under 10mb and no Data Model
In this scenario, we are only talking about using Excel Online with a workbook that doesn’t have a data model. So, just the workbook. This will work with Excel Online.Scenario 2: Excel Online with workbook over 10mb
This scenario applies to the workbook itself being over 10mb when being used within Excel Online. In this scenario it doesn’t matter if you have a Data Model or not, as we hit the cap that is imposed by Excel Online for the workbook size. If you do have a Data Model, you can see the steps at the bottom of this post to see how to determine what the workbook size actually is.When we try to open this workbook within Excel Online, we will see the following message.
If you go into the old Power BI Sites and try to enable the report, you will see the following error.
Scenario 3: Excel Online with a Data Model under 10mb
Assuming both the Workbook size is under 10mb, and the Data Model is under 10mb, this should work within Excel Online without issue.Scenario 4: Excel Online with Data Model > 10mb and < 30mb
In this scenario, we still have the same limit as Scenario 2 for the workbook size, but we also will hit a limit on the Data Model. This scenario requires that you have a Power BI for Office 365 or Power BI Pro license assigned.If the Data Model itself, regardless of the Excel file size, is over 10MB, we require a Power BI License for that user. If you don’t have a Power BI License assigned, you will get an error similar to the following.
Scenario 5: Excel Online with Data Model > 30mb
In this scenario, we have a workbook that has a Data Model over 30mb. You will hit the same message as shown in Scenario 4 if you do not have a Power BI for Office 365 or Power BI Pro license assigned.The other action we need to take here is to either enable the workbook within the old Power BI Sites, or to pull in the Excel Workbook within the new Power BI Dashboard site (app.powerbi.com). Within the new site, the workbook needs to be hosted on OneDrive for Business to use the Excel Online App.
If you have not taken one of the above actions, you would see an error similar to what we saw in Scenario 4 within Excel Online.
Scenario 6: Excel Online or Power BI with a Data Model over 250mb
This could actually be with either an Excel Workbook, or a Power BI Desktop file (PBIX). In this, the data model is over 250mb. The assumption here is that you have a Power BI License assigned to your account. Because it is over 30mb, we need to either enable it within Power BI Sites for the old service, or pull it into the new service via Get Data and point to OneDrive for Business.Enable in Power BI Sites
When you try to enable the report within the old Power BI Sites, you will see the following error.
PowerBI.com via Get Data
When you click on Connect within the Get Data screen, you will see the following message.
How to Figure out the Sizes
Let’s see how we can determine the total file size, along with the Data Model size and Workbook size. We really want to figure out three items.- Total file size- Workbook Size- Data Model sizeTo accomplish this, we can get the total file size, by just going to properties of the file within Explorer. This will tell you the file size. Let’s look at my Large File to see what it’s size is.
Total File Size here is 140,083,398 bytes (133MB). Now, what we can do, to find the Data Model, is to rename the excel workbook to a .zip extension. After doing that, open the zip file, go into the XL Folder and then the Model folder. You will see an item.data file. This is the Data Model file. We want to get the size of that. The compressed and actual size will be the same for this item.
The Data Model size is 59,784,000 bytes (about 58MB). Next we do a little math. Take the total file size and subtract the Data Model size to get the workbook size.
[Total File Size] – [Data Model Size] = [Excel Workbook Size]So, our workbook alone is about 76.5MB which will stop everything. The Data Model itself is ok. In this case, when we pulled data into the workbook, we pulled it in to the Data Model, and added it to a worksheet. This bloats the workbook if your intent is only to use the Data Model and Power View Reports, or something like a Pivot Chart or Pivot Table. To avoid this, when you pull data in via the Excel Data Tab or Power Query, you can choose to only add it to the data model, and not to an excel sheet.140,083,398 (133MB) – 59,784,000 (~58MB) = 80,299,398 (~76.5MB)
Another File Size Example
Let’s have a look at another example. My Super Size workbook. We will do the same thing we did in the previous example.
[Total File Size] – [Data Model Size] = [Excel Workbook Size]
367,363,383 (350MB) – 358,704,000 (~342MB) = 8,659,383 (~8.25MB)In this example, our workbook size is ok, but our data model is well over the 250MB limit.
Adam W. Saxton | Microsoft Business Intelligence Support - Escalation Services@GuyInACube | Mixes | YouTube | Facebook.com\guyinacube