In this article, we are going to highlight some of the PowerBI Data Refresh Limitations and restrictions. However, you will have to understand that, not all “Data Refreshes” happen in the same way. It also depends on the data source that you are using for your data model.
PowerBI Data Refresh Limitations:
As a prerequisite, you will need to have a basic knowledge of PowerBI to have a proper understanding of this technology and related acronyms. So, lets being with the following table:
PowerBI Tiers | Shared Pro Capacity | Premium dedicated Capacity |
Total Data Model Size | 10 GB | 100 TB |
Max Dataset Size (Import Mode) | 1 GB per Dataset | 12 GB per Dataset |
Refresh Rates | 8 Times / Day | 48 Times / Day |
As you can see from the table above, when you are using the Import methods, you are only allowed to refresh your data a couple of times in a day. For most use cases, this should be sufficient. However, for real-time scenarios, you will ideally need to use “DirectQuery” as opposed to the Import method. See the screenshot below:
Here is a quick comparison of Import method vs. DirectQuery method:
Import (cached mode) | Direct query or Live connection | |
Refresh frequency | Scheduled Async Jobs – hourly or daily | (near) Real-time |
Performance | No noticeable delay since the data model is already cached (Point in time data) | Depends on how fast the network connectivity and data source is as queries are executed in real-time. Only metadata and schema structure is stored on the Data model |
Data storage in Power BI | Since it is a cached mode, data is stored in the PowerBI Service (cloud) | Data is not stored in the Power BI cloud service. Data resides on-premises |
Dataset size (Max) | Hard limit of 1 GB (Columnar data compression) per model | The on-premises database storage is the limit; no Power BI service limitation |
Security | Can create row-level security on the PBI dataset (import only) | Re-use on-prem row-level security for Analysis Services Tabular using DAX expressions |
In order to keep your data current, here are the types of data models which PowerBI supports:
- Excel Workbooks with embedded PowerPivot Data Models or PowerQuery
- PowerBI Desktop files (PBIX)
Check out the table below. It will help you to understand the differences between Import vs. DirectQuery vs. Live Data. As you can see from the table below, the behavior is different for different connection methods, types, and data sources.
Import | DirectQuery | Live Data Exploration | |
Overview | ETL (Power Query M Expressions)
Data download |
Select specific tables or write your Select Query
No data download Queries triggered directly from Report visuals |
Explore source objects from Report surface
No dataset download Queries triggered directly from Report visuals |
Supported Data Sources | All sources (>100 sources) | Typically, relational data sources:
MS SQL Server Azure SQL Database (SMP) Azure SQL Data Warehouse (MPP) SAP HANA (In-Memory) Oracle Databases Teradata |
SQL Server Analysis Services (Tabular & Multidimensional) |
Max No of data sources per report | No hard limits | Single | Single |
Data Transformations | All transformations (100’s) | Partial support (differs by data source) | N/A |
Mashup Capabilities | Merge (Joins)
Append (Union) Custom Parameterized queries |
Merge (Joins)
Append (Union) |
N/A |
Modeling Capabilities | Identify and create relationships between datasets from a variety of data sources
Calculated Columns & Tables (via. DAX) Measures (Aggregations) ** Measures will create new information from the existing data already present in your model Simple Hierarchies |
Calculated Columns
Measures Change Column Types |
N/A |
PowerBI Data Refresh Limitations – Datasets
As you can see from the above comparison tables, when you try to refresh your imported data, you can only do it eight times for Pro and 48 times for Premium accounts. When you exceed these numbers, your Data Refresh jobs will fail. Sometimes, if you delete and recreate the existing data refresh schedules, you can exceed the prescribed limited. We got mixed results with this and is not a viable long term solution.
One important thing to understand is one user can own one dataset at a time. See the screenshot below:
So, a single dataset cannot be taken over by multiple people in a single tenant and refresh it simultaneously. Thus, the data refresh is per dataset in a given tenant.
Conclusion
We hope this article helped understand the various hard limits and feature specifications. It is highly recommended to know these limitations before implementing your next BI project based out of PowerBI.
Comments or suggestions are welcome in the comment box below. Thanks for visiting.