Model with loading very big CSV files - best practices.

[jaryszek]

Hello,

I have model where I am loading 50 GB + of data in flat tables (csv). 
I am using just Folder.Files to get data. 

But the issue is that it is too big for some matrix visuals and getting error like: Query exceed available resources. But it is working in power bi service. 

How to create a proper model for it? 
Incremental load will not work because i have csv and query folding will not work. 
Loading sample data ...could work but it will not allow power bi developers to see overall data and make proper comparisons. 

We have also possibility to direct query but i would have to move my data in specific databases for that. 

What is the best approach? How you can model it?

Best,
Jacek

[v-pgoloju]

Hi @jaryszek,

Just following up to see if the responses provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @jaryszek,

Pre aggregating older data means keeping full detailed records for the recent period (like the last one or two years) but storing only monthly or yearly totals for older years. This makes your model much smaller and faster because old data rarely needs transaction-level detail. In Power BI, you can do this by splitting your fact table into recent and old data in Power Query, summarizing the old data by year/month and key fields, and then combining it back with the recent detailed data.

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @jaryszek,

Yes, normalizing the fact table and replacing text with integer surrogate keys will make it faster but the real gain comes from reducing cardinality, removing unused columns, and pre aggregating older data.

Thnaks & Regards,

Prasanna kumar

[jaryszek]

What do you mean by 

pre aggregating older data.?

It means that for older years you are using aggregated data and for the newest you are using granularity like days? How to implement it? 

[jaryszek]

Thanks,

this is star schema. How can i optimize fct table then? 
If i would normalized data and put only integers columns will be faster?

Best,
Jacek

[v-pgoloju]

Hi @jaryszek,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Since your Power BI model loads over 50 GB of data from CSV files, you're facing performance issues in Power BI Desktop, especially with large visuals like matrix tables. To fix this, the best approach is to restructure your data into a star schema by separating it into smaller, related tables (like Date, Product, Region) instead of using one large flat table. You can also create summary tables in Power Query to reduce the data volume used in visuals. During development, load only a sample of the data using parameters to speed things up, while keeping the full data in the Power BI Service for publishing. Additionally, avoid overloading visuals with too much detail use drillthrough or summary views instead. If possible, consider moving your data to a proper database like Azure SQL or Synapse to enable advanced features like incremental refresh and DirectQuery for better scalability.

Tnaks & Regards,

Prasanna Kumar