The Conceptual Data Model and Limits
Note: This information is good as of 8/11/2015 and is subject to change!
I had discussed part of this topic as it related to Excel Workbooks with the old Power BI service, but I wanted to refresh that and talk about the concept of the Data Model and highlight where we are at today with the limits.
Conceptual Data Model
Power BI works with the Data Model. If the data isn’t in the Data Model, we can’t really do anything with a file (Excel Workbook or otherwise). You can have your data model in a couple of locations. Either in an Excel Workbook, a Power BI Desktop file or an on premises Analysis Services Tabular Instance.
What I really want to get across here is that when I talk about the Data Model, I don’t mean the Excel Workbook. The workbook is just a container that has the Data Model within it.
The Power BI Desktop file is similar. For either, you can rename the file to .zip and see the data model file within.
Excel File Example
\xl\model\item.data will be the Data Model file within an Excel Workbook.
Power BI Desktop Example
DataModel is the item within the Power BI Desktop file.
When you import a Workbook or Power BI Designer file, we extract the data model from the file and host it in our back end. We then work against that hosted Data Model, not the workbook or PBIX file where it came from.
Limits
Let’s look at what some of the limitations are. This was the scenario list I had put together for the old posting. It is still valid today, but I won’t be going through all of them as some relate to the old Power BI Service. I will be focusing on Excel Online and the new Power BI Service.
|
|
Workbook Size
|
Data Model Size
|
Requires Power BI License
|
Action Required*
|
|
Scenario 1
|
> 10mb
|
N/A
|
N/A
|
N/A
|
|
Scenario 2
|
< 10mb
|
No Model
|
NO
|
NO
|
|
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
|
* Action Required: You have to do one of two things. Either enable the workbook in the old Power BI Sites, or use the Get Data action within the new Power BI Dashboard site and get an Excel File that is hosted on OneDrive for Business which allows you to still use the Excel Online app as an option.
Scenario 1: 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 try to import an Excel Workbook into the Power BI service that is over the 10MB workbook limit, you will see the following.
Scenario 2: 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 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 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 if 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.
PowerBI.com via Get Data
When you click on Connect within the Get Data screen, you will see the following message.
Using Analysis Services Tabular Mode with the Analysis Services Connector
If you really need a data model larger than 250mb, you should use an on premises Tabular model and use the Analysis Services Connector to use it with Power BI. This will issue live queries from the Power BI service to the on premises Tabular Instance.
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 size
To 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]
140,083,398 (133MB) – 59,784,000 (~58MB) = 80,299,398 (~76.5MB)
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.
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 | YouTube | Facebook.com\guyinacube
