Power BI Data Loading and Capacity Challenges, file over 1GB

[p_stathopoulos]

I am currently using a free Power BI license and rely on Power BI Desktop to connect to a MySQL database. I utilize Import Mode with custom SQL queries (e.g., SELECT column1, column2 FROM table) to load data.

I have identified four large tables, and while I've already optimized by selecting only necessary columns, I still require all rows.

As you can see, the core issue is:

• Each of the four tables, when limited to only the 2024-2025 data, results in a table size exceeding 400MB. Ideally, I need to load data starting from 2022. The 3 tables have over 6 million rows and the 4th over 12m!

• The total size of the resulting Power BI file uploaded to the Power BI Service (app.powerbi.com) exceeds the 1 GB per dataset limit for the free license, triggering an upgrade requirement error.

• The primary contributor to the large dataset size is a column named MYID, which is crucial as the main key connecting the four tables and exist in every one of the 4 tables.

• Despite various optimization attempts to reduce dataset size, the only current workaround is limiting the data to the last two years.

Key Questions and Considerations:

1. Data Loading Strategy: Import vs. DirectQuery

If I switch the data connectivity from Import Mode to DirectQuery, how will this impact the file size limit and report performance?

• File Size Limit: Will the 1 GB per dataset limit in the Power BI Service be bypassed or still apply when using DirectQuery?

• Report Performance: What is the expected trade-off in report speed and responsiveness when executing queries live against the source MySQL database via DirectQuery compared to the optimized in-memory processing of Import Mode?

2. Premium Capacity Upgrade and Scalability

Do i have to upgrade to a Power BI Premium Capacity (P-SKU), or to the Power BI Premium Per User (PPU) to accommodate current and future growth? What t is the best choise to provide a sustainable solution for several years of data expansion?

If I utilize Azure's Pay-As-You-Go model, how can I accurately estimate/calculate the daily cost associated with the dataset refresh frequency and user consumption (usage)? I have attempted to set up an Azure account but require guidance on identifying the appropriate metrics for exact cost calculation per day/usage.

[p_stathopoulos]

Thanks for your answer.

As you can see, the 1st column "myid" occupies about 86% of the table size, so there’s very little room for optimization. The Time column is necessary because we perform hourly analysis. I might remove the minutes, but that would only provide minimal improvement since time accounts for just 3.8% of the table.

Next year, with more data, and for my one user,  I’ll need to switch to PPU at €22.50 per month, and I only require one, correct?
https://www.microsoft.com/el-gr/power-platform/products/power-bi/pricing

Also, what will be the model/file size limit—3 GB or more?

[ibarrau]

With PPU you need one license per user. Viewers will also need one at a Workspace with PPU capacity. Yes you can create 3gb or more data models.

Regards

[GilbertQ]

Hi @p_stathopoulos 

What if you had to further optimize the model by removing the time component from your queries and then aggregating the data to see if that will reduce the amount of rows that you have?

[ibarrau · Accepted solution]

Hi. First of all I would say the best option will always be optimice the data model. You can find amazing practices at youtube or articles talking about autodate time, remove unnecessary columns/tables, or even analyze the vertipaq storage to understand if big columns could be changed. Of course oneof the most impactfull options is thinking the data model again, is that the best star schema possible?

After saying that, let's talk about the questions.

1. Direct query will reduce the 1gb to less than 10mb for sure. That's because it won't store data. It will run queries against the source. In order to make it work properly you really really need to optimize the model and make sure your source can respond fast. Otherwise the user experience will be very slow. If you want to try this, please make sure to check a guide for direct query like: https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance

2. Premium capacity can't be purchased any more. Depending on the amount of users you would pick PPU or Fabric capacity. PPU is better if you are a small number of users, it can handle more than 1gb model and has many great features. Fabric will help when sharing with more users, you pay for capacity for a workspace. Depending if it's less than F64 SKU you would pay for pro users to view or not. Fabric pricing can be  yearly reserved or pay as you go. You can find prices just searching at web "Fabric Pricing".

I hope that helps,